Click here to Skip to main content
15,886,689 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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:
C#
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]
Posted
Updated 3-Feb-11 11:30am
v5
Comments
Henry Minute 3-Feb-11 17:33pm    
Rather than add an answer, you should either amend your original question or add a comment to one of the answers, if you want to deal with just one answerer. That way the answerer will get an Email notification just as you are getting here.

I have moved your answer into the original question and deleted your answer. :)
Henry Minute 3-Feb-11 17:38pm    
The ? denote that it is a parameterised Update statement. They 'should' get replaced by the data during the .Update() call. Does the UpdateCommand printed by Debug have a WHERE clause? Does that give any clue at all?

I've just noticed that the printed SQL doesn't reference DetailID. If that is the unique identifier for the table that could be part of the problem.
Eddie Niebruegge 3-Feb-11 18:26pm    
This is the entire CommandText...

UPDATE tbl_PhysCME SET PhysicianID = ?, Start Date = ?, End Date = ?, Subject = ?, Hours = ?, Category = ?, Sponsor = ? WHERE ((DetailId = ?) AND ((? = 1 AND PhysicianID IS NULL) OR (PhysicianID = ?)) AND ((? = 1 AND Start Date IS NULL) OR (Start Date = ?)) AND ((? = 1 AND End Date IS NULL) OR (End Date = ?)) AND ((? = 1 AND Subject IS NULL) OR (Subject = ?)) AND ((? = 1 AND Hours IS NULL) OR (Hours = ?)) AND ((? = 1 AND Category IS NULL) OR (Category = ?)) AND ((? = 1 AND Sponsor IS NULL) OR (Sponsor = ?)))

1 solution

If DetailID is an Identity or a Primary Key, that might be the cause of the problem.

See the Rules for Automatically Generated Commands section on This MSDN Page[^].
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900