ASP.NET DataAdapter/CommandBuilder Syntax Error

[J. Babe]

I am updating an Access 2000 database using ASP.NET. I pass a
datatable to a function in a DLL that updates the database with a
DataAdapter. When I run the program it crashes when updating and gives
the error: "Syntax Error in INSERT INTO Statement". I'm wondering what
I can do to fix it.

Private Sub SaveWork()
'Create an instance of a class to perform DB operations....
Dim objWork as new Work()
Dim dr As DataRow
Dim dtUpdate As New DataTable()
'return template for blank row
dtUpdate = objWork.GetAllWork(True)
dr = dtUpdate.NewRow
With dr 'set info in the datatable with values in textbox
fields
.Item("PersonID") = CInt(Session("PersonID"))
.Item("ProjectID") = intProjectID
.Item("Position") = txtPosition.Text.Trim
.Item("Sector") = txtSector.Text.Trim
.Item("Hours") = dblHours
.Item("Description") = txtDescription.Text
.Item("Date") = strDate
.Item("Temp") = True
End With
dtUpdate.Rows.Add(dr) 'Adding the new record to the datatable
objWork.SaveWork(dtUpdate, intProjectID)
End Sub


The function in the Work.vb class....

Public Function SaveWork(ByVal dtUpdate As DataTable, ByVal
ProjectID As Integer) As Boolean
OpenDB()
Dim da As New OleDbDataAdapter("SELECT * FROM WorkLog", con)
Dim cb As New OleDbCommandBuilder(da)
da.InsertCommand = cb.GetInsertCommand
da.Update(dtUpdate) '!!!!!!!!!!!!!!Crashes on this
line!!!!!!!!!!!!!!
CloseDB()
Return True
End Function

 

[Marina]

Looks like you are using some reserved words as column names. Try setting
the QuotePrefix and QuoteSuffix properties of the commandbuilder to "[" and
"]" respectively.

 

[David Sceppa]

This error often occurs if you're using a reserved word for
at least one of your column names. In such scenarios, you need
to delimit the column names. Try adding the following code:

...
Dim cb As New OleDbCommandBuilder(da)
cb.QuotePrefix = "["
cb.QuoteSuffix = "]"

I hope this information proves helpful.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2003 Microsoft Corporation. All rights reserved.