Click here to Skip to main content
15,896,201 members

Update Only One Row in a DataTable

Eddie Niebruegge asked:

Open original thread
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]
Tags: DataTable, DataRow

Plain Text
ASM
ASP
ASP.NET
BASIC
BAT
C#
C++
COBOL
CoffeeScript
CSS
Dart
dbase
F#
FORTRAN
HTML
Java
Javascript
Kotlin
Lua
MIDL
MSIL
ObjectiveC
Pascal
PERL
PHP
PowerShell
Python
Razor
Ruby
Scala
Shell
SLN
SQL
Swift
T4
Terminal
TypeScript
VB
VBScript
XML
YAML

Preview



When answering a question please:
  1. Read the question carefully.
  2. Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
  3. If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
  4. Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.
Let's work to help developers, not make them feel stupid.
Please note that all posts will be submitted under the http://www.codeproject.com/info/cpol10.aspx.



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