Problem with using UpdateCommand property of the DataAdapter 1

[raabbasi]

The following procedure does not Update the record(s).
'---------------------------------------------------------
Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
Dim dadapter As OleDbDataAdapter = New OleDbDataAdapter
Dim CmdBuilder As OleDbCommandBuilder = New OleDbCommandBuilder(dadapter)
Dim strSelect = "SELECT CustomerID, CompanyName FROM Customers"
Dim cmdSelect As OleDbCommand = New OleDbCommand(strSelect, Conxn)

Dim DS As DataSet = New DataSet
dadapter.SelectCommand = cmdSelect
dadapter.Fill(DS, "Customers")

Dim strUpdate = "UPDATE " & strCustomers & " " & _
"SET CustomerID = 'COMID', " & _
"CompanyName = 'Cactus Comidas'" & _
"WHERE CustomerID = 'CACTU'"

Dim cmdUpdate As OleDbCommand = New OleDbCommand(strUpdate, Conxn)
dadapter.UpdateCommand = cmdUpdate

Try
Dim NumRows As Long = dadapter.Update(DS, "Customers")
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try
End Sub
'---------------------------------------------------
I need a solution based on the Update method of the DataAdapter (Not ExecuteNonQuery of the Command object). Please suggest a solution.

Regards.

R. A. Abbasi

 

[sin1965]

Assign the SelectCommand to the DataAdapter before assigning the OleDbCommandBuilder. Using the OleDbCommandBuilder should mean that you don't need to specify an update command. (Leave that section of code out) It generates action queires based on the select query metadata. Just amend the DataRows returned from the resulting select query and then update the dataset.
Find out about using your own update logic. CommandBuilder only works with single tables.
Hope this helps
Steve

 

[raabbasi]

OleDbCommandBuilder was not supposed to be included.
Please elaborate more on the suggestion "amend the DataRows returned from the resulting select query and then update the dataset".
I am precisely interested in using the Update method of the DataAdapter object.
Regards.

 

[ca8msm]

http://msdn.microsoft.com/library/d...stemdatacommondataadapterclassupdatetopic.asp

____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[sin1965]

if you are not using the command builder then you must provide all update logic yourself.
Once you have filled the DataSet with "Customers" assign it to a DataTable thus:
Dim tblX as DataTable=DS.Tables(0)

Declare a DataRow array to hold the DataTable.Select results
Dim rowX() as DataRow

Get the data you want to change
rowX = tblX.Select("CustomerID = 'CACTU'")

Assume that only one row is returned (zero based array)
Change the values you want to
With rowX(0)
.Item("CustomerID") = "COMID"
.Item("CompanyName") = "Cactus Comidas"
End With

Build sql string for update
Dim sqlX As String
sqlX="UPDATE Customers SET CustomerID = ?,CustomerName= ? WHERE CustomerID = ?"

The ?'s are place holders that the DataAdapter fills in when it updates the database. But to use these placeholders we must add parameters to the OleDbCommand

Dim cmdUpdate As OleDbCommand = New OleDbCommand(sqlX, Conxn)

With cmdUpdate.Parameters
.Add("CustID_New", OleDb.OleDbType.BSTR, 0, "CustomerID")
.Add("CustName_New", OleDb.OleDbType.BSTR, 0, "CustomerName")
.Add("CustID_Orig", OleDb.OleDbType.BSTR, 0, "CustomerID")
.Item("CustID_Orig").SourceVersion = DataRowVersion.Original
End With

assign DataAdapter.UpdateCommand
dadapter.UpdateCommand = cmdUpdate

update
dadapter.Update(DS,"Customers")

Hope this helps
Regards
steve

 

[raabbasi]

Great help from Steve. Thanks and best regards.