Customized Update Command For DataAdapter
Hi, I'm hoping someone can tell me what is wrong. I'm getting an exception "Syntax Error in UPDATE Statement" when I try to update the database with changes from a datatable on the client (Via a datagrid). I have a feeling it may have to do with the parameters and stuff.
I'm using VS2003
Private dt As DataTable
Private da As OdbcDataAdapter
Private cn As OdbcConnection
Private cmdUpdate As OdbcCommand
Private DisplayGrid As New DataGrid()
cn = New OdbcConnection("DSN=CIRISSDB;Driver={Microsoft Access Driver (*.mdb)}")
cmdUpdate = New OdbcCommand("Update sa_adminvalues set Value = ? , modon = now(),modby = 1 where Variable = ?", cn)'now() is an access function for current date/time
'the previous line of code is all in one line, not two as the forum will display
With cmdUpdate.Parameters.Add("@p1", OdbcType.VarChar)
.SourceColumn = "Value"
.SourceVersion = DataRowVersion.Current
End With
With cmdUpdate.Parameters.Add("@p2", OdbcType.VarChar)
.SourceColumn = "Variable"
.SourceVersion = DataRowVersion.Original
End With
cn.Open()
da = New OdbcDataAdapter("Select * From sa_adminvariables", cn)
dt = New DataTable
da.Fill(dt)
DisplayGrid.DataSource = dt
DisplayGrid.CaptionText = Title
da.UpdateCommand = cmdUpdate
cn.close()
'pause & wait for user to modify data
'User chooses to save updates
cn.open()
Dim rowsUpdated As Long
rowsUpdated = da.Update(dt) 'This is where exception is thrown
cn.close()
Thanks Muchly!!!!!