Question update record using dataset or dataadapter

[rehan.azher]

Dear All ,

I am using below code to update a record in a table on SQL server 2000.

With frmMain
            .myConnection.Open()
            .comUserSelect = New SqlCommand("SELECT     customerID, company, cname, chp, cop, cemail, cname2, chp2, cop2, cemail2  FROM         tbl_Customer WHERE     (customerID = " & j & ")", .myConnection)
            .myAdapter = New SqlDataAdapter("SELECT     customerID, company, cname, chp, cop, cemail, cname2, chp2, cop2, cemail2  FROM         tbl_Customer WHERE     (customerID = " & j & ")", .myConnection)
            .myDataset = New DataSet()
            .mydataTable = New DataTable()

            .myAdapter.Fill(.myDataset, "customers")
            Dim cb As New SqlCommandBuilder(.myAdapter)
            .myDataset.Tables(0).PrimaryKey = New DataColumn() {.myDataset.Tables(0).Columns(0)}
            'Label3.Text = .myDataset.Tables(0).Rows(0)(0)
            .myDataset.Tables(0).Rows(0)(1) = TextBox4.Text
            .myDataset.Tables(0).Rows(0)(2) = TextBox5.Text
            .myDataset.Tables(0).Rows(0)(3) = TextBox6.Text
            .myDataset.Tables(0).Rows(0)(4) = TextBox7.Text
            .myDataset.Tables(0).Rows(0)(5) = TextBox8.Text
            .myDataset.Tables(0).Rows(0)(6) = TextBox12.Text
            .myDataset.Tables(0).Rows(0)(7) = TextBox11.Text
            .myDataset.Tables(0).Rows(0)(8) = TextBox10.Text
            .myDataset.Tables(0).Rows(0)(9) = TextBox9.Text
            .myAdapter.Update(.myDataset, "customers")
            .disposeAll()
            MsgBox("Details Updated")
            filldv()
        End With

It was running sucessfully on while SQL Server was on local machine, now my SQL Server is on network and I get following Exception.

"Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information."

can any one help me in this regard.

Thanks and best regards,

 

[jmcilhinney]

In order to generate an UpdateCommand, the SqlCommandBuilder must know which column(s) is the primary key. Your setting the PrimaryKey property of the DataTable is no good because there's no guarantee that it's the same as the PK on the database.

You need to set the MissingSchemaAction property of your SqlDataAdapter to AddWithKey. As a result you will no longer be violating this condition:

a SelectCommand that does not return any key column information

It also means that you won't need to set the PrimaryKey of your DataTable because it will be done automatically.

 

[rehan.azher]

thanks JM,

i did it using following code and it worked:

 With frmMain
            .myConnection.Open()
            '
            '
            .comUserSelect = New SqlCommand("UPDATE    tbl_Customer SET company = '" & TextBox4.Text & "', cname = '" & TextBox5.Text & "', chp = '" & TextBox6.Text & "', cop = '" & TextBox7.Text & "', cemail = '" & TextBox8.Text & "', cname2 = '" & TextBox12.Text & "', chp2 = '" & TextBox11.Text & "', cop2 = '" & TextBox10.Text & "', cemail2 = '" & TextBox9.Text & "' WHERE     (customerID = " & j & ")", .myConnection)
            .comUserSelect.ExecuteNonQuery()
            .comUserSelect.Dispose()
            .myConnection.Close()

            MsgBox("Details Updated")
            filldv()
        End With

 

[jmcilhinney]

I strongly suggest that you learn to use parameters instead of building SQL statements using string concatenation. You'll avoid all sorts of problems that way. See this for some examples.

 

[rehan.azher]

thanks JM,

this is what i was looking for ,i hope it will be great help for me in future.

best regards,

 

[cjard]

Something that would help you more for the future would be to read the DW2 link in my signature and follow the Creating a Simple Data App tutorial