I have a form with a datagridview control where an operator may or may not change the value in the cells. If any data is changed, I want to update the database with the updated row. In the UpdateRow function I'm using below, the argument received, intDetailID, is a unique key in the table to be updated. I'm using the following code to try to do this. Although I'm not getting any errors, the database is not being updated. What am I missing?
Private Sub UpdateRow(ByVal intDetailID As Integer)
'Define data adapter for only one row data table
Dim daPhysCME_Update As New OleDb.OleDbDataAdapter
Dim daPhysCMEUpdateSql As String
daPhysCMEUpdateSql = "Select * from tbl_PhysCME WHERE DetailID=" & intDetailID
daPhysCME_Update = New OleDb.OleDbDataAdapter(daPhysCMEUpdateSql, Cnxn)
'Define and fill data table with only one row to be updated
Dim dtPhysCME_Update As New DataTable
daPhysCME_Update.Fill(dtPhysCME_Update)
cbCommandBuilder = New OleDb.OleDbCommandBuilder(daPhysCME_Update)
'Populate the table with new values
dtPhysCME_Update.Rows(0).Item("DetailID") = aftDetailID
dtPhysCME_Update.Rows(0).Item("PhysicianID") = aftPhysicianID
dtPhysCME_Update.Rows(0).Item("Start Date") = aftStartDate
dtPhysCME_Update.Rows(0).Item("End Date") = aftEndDate
dtPhysCME_Update.Rows(0).Item("Subject") = aftSubject
dtPhysCME_Update.Rows(0).Item("Hours") = aftHours
dtPhysCME_Update.Rows(0).Item("Category") = aftCategory
dtPhysCME_Update.Rows(0).Item("Sponsor") = aftSponsor
dtPhysCME_Update.AcceptChanges()
'Update the database
daPhysCME_Update.Update(dtPhysCME_Update)
End Sub
[Edit - further information from OP - Henry]
I have some more information to help assist in solving this problem. I added the following code just before the ".Update" statement:
Debug.Print(cbCommandBuilder.GetUpdateCommand.CommandText)
This is the first part of what I got:
UPDATE tbl_PhysCME SET PhysicianID = ?, Start Date = ?, End Date = ?, Subject = ?, Hours = ?, Category = ? ...
Is the fact that all the column values are question marks significant?
Any other idea?
[/Edit]