Click here to Skip to main content
13,256,446 members (52,387 online)
Rate this:
Please Sign up or sign in to vote.
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
    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
    'Update the database
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:

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?

Posted 3-Feb-11 10:22am
Updated 3-Feb-11 12:30pm
Henry Minute225.9K
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

Rate this: bad
Please Sign up or sign in to vote.

Solution 1

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[^].

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

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy |
Web03 | 2.8.171114.1 | Last Updated 3 Feb 2011
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100