SQL statment into a Dataset

M

MTSkull

Well-known member
Joined
Mar 25, 2003
Messages
135
Location
Boulder, Colorado
How do I execute an SQL statment against a dataset object in memory.

I have a dataset that I recieved from the SQL server. I need to set a primary key for the table in the dataset (the SQL Server table doesnt have a PK and I am only allowed read only access) I want to use the sql command
Code: 
        strSQL = "Alter Table " & PROF_TABLE_NAME
        strSQL &= " modify column (professional_id int(6) not null primary key)"
to force the professional_id field to be the primary key. This field is unique. Then I can put the row I want in to a Datarow object to be passed to anouther part of the program.

Thanks
Brian
 
try with execute nonQuery!!

example-->


Code: 
Try
myConn.Open()
Catch errobj As Exception
MessageBox.Show(errobj.Message)
End Try

 myAdaper.SelectCommand = New OdbcCommand("alter .....")
 myAdaper.SelectCommand.Connection = myConnection
 myAdaper.SelectCommand.ExecuteNonQuery()
 
If you want to change a Column in the DataSet thats in memory, use the DataSet.Columns property to access the column you want to modify, and set it as follows for primary key;

C#: 
DataColumn.AutoIncrement = true;
DataColumn.AutoIncrementSeed = 1;
DataColumn.AutoIncrementStep = 1;
DataColumn.Unique = true;
DataColumn.ReadOnly = true;
 
Code: 
        2. Set the primary Key
        dtData = dsDataSet.Tables(PROF_TABLE_NAME)

        dtData.Columns.Add("TempPrimeKey", System.Type.GetType("System.Int32"))

        dtData.Columns("TempPrimeKey").AutoIncrement = True
        dtData.Columns("TempPrimeKey").AutoIncrementSeed = 1
        dtData.Columns("TempPrimeKey").AutoIncrementStep = 1
        dtData.Columns("TempPrimeKey").Unique = True
        dtData.Columns("TempPrimeKey").ReadOnly = True

Okay I am getting an error on the unique = true line that says
"Column TempPrimeKey contains nonunique values"
Any thoughts?

Thanks for all the Help so far.
Brian

PS
System.Type.GetType("System.Int32")
by the way getType requires a case sensitve string.
 
It will initially be set to null unless you assign it a beginning value.


edit: ok, missed the seed, disregard

Jon
 
Last edited by a moderator:
MT,

The PrimaryKey property is an array of columns that function as the primary key of the table.

If you need to designate the professional_id as a primary key of the table, give this a try.

Code: 
Dim dc() as DataColumn = {dsDataSet.Tables("PROF_TABLE_NAME").Columns("professional_id")}
dsDataSet.Tables("PROF_TABLE_NAME").PrimaryKey = dc

I cant do a run time test on it.

Jon
 
Hm.. PrimaryKey property, I must of missed it when I was looking up the info on MSDN.

I assumed your primary key was an auto increment field, which aparently it doesnt seem to be. My mistake for giving bad info, follow jfacklers advice.

Also, just thought Id throw out a warning that when getting the schema info from tables in a database through the DataAdapter, it doesnt copy of over Identity columns. You have to programmaticaly set it (as I showed above w/ AutoIncrement). Figured it would be worth mentioning since its relevant to this topic.
 
Code: 
        On Error GoTo ErrorHandler
        Dim iCurrentRow As Int32
        Dim dtData As DataTable
        Dim myColumn(1) As DataColumn

        1. Get the current Row from the datagrid
        iCurrentRow = grdResult.CurrentRowIndex()

        2. Set the primary Key
        dtData = dsDataSet.Tables(PROF_TABLE_NAME)

           A. set mycolumn = to Prof ID
        myColumn(0) = New DataColumn
        myColumn(0) = dtData.Columns("Professional_ID")

           B. set prof id with prime key attributes
        With myColumn(0)
            .Unique = True
            .ReadOnly = True
        End With

           C. set dtData primary key = to myColumn
        dtData.PrimaryKey = myColumn

        4. Get the current row from the table as a row object
        Not sure which of these two techniques I will use yet
        gdrDataRow = dtData.Rows.Find(CInt(iCurrentRow))

        giSelectedDoctor = CInt(gdrDataRow("Professional_ID").ToString)

        Me.Hide()

        Exit Sub

Here is what works after digging in to the help and all that. You can use PrimaryKey to set the PK to multiple columns, IE firstname lastname.

Thanks for Pointing me in the right direction.
Brian
 
Back
Top