Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: DataTable DataRow
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]
Posted 3-Feb-11 10:22am
Edited 3-Feb-11 12:30pm
Henry Minute223.9K
v5
Comments
Henry Minute at 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 at 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 at 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
good
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[^].
  Permalink  

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

  Print Answers RSS
0 Peter Leow 255
1 Mika Wendelius 240
2 Zoltán Zörgő 208
3 CHill60 195
4 TheRealSteveJudge 180
0 Sergey Alexandrovich Kryukov 8,658
1 OriginalGriff 6,591
2 Peter Leow 3,982
3 Zoltán Zörgő 3,604
4 Richard MacCutchan 2,510


Advertise | Privacy | Mobile
Web04 | 2.8.150123.1 | Last Updated 3 Feb 2011
Copyright © CodeProject, 1999-2015
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