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

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

"J. Babe" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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

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.