Dataadapter produces error: Syntax error in INSERT INTO statement

  • Thread starter Thread starter William LaMartin
  • Start date Start date
W

William LaMartin

The code below loads an Access table with one row. It then adds a row and
displays all this in a datagridview. So far so good.

But when I use the dataadapter's update command to update the database
table, I receive the following error:
Syntax error in INSERT INTO statement

The Insert statement as displayed by the debug,write line is:
INSERT INTO Main (Id, Interval, Tutor1, Tutor2) VALUES (?, ?, ?, ?), which
looks fine to me.

Where is the problem? The Commandbuilder should have no problems with this
simple setup.

The code:

Dim ds2 As New DataSet
Dim da2 As New Data.OleDb.OleDbDataAdapter
Dim dc As Data.OleDb.OleDbConnection = New OleDb.OleDbConnection
dc.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=Test2.mdb"
dc.Open()
Dim StrSQL As String = "SELECT * from Main"
Dim objCommand As New Data.OleDb.OleDbCommand(StrSQL, dc)
da2.SelectCommand = objCommand
Dim CB2 As Data.OleDb.OleDbCommandBuilder = New
OleDb.OleDbCommandBuilder(da2)
Debug.Write("CB: " & CB2.GetInsertCommand.CommandText) 'What it
writes looks fine to me

da2.Fill(ds2, "Main")

Dim NewRow As DataRow = ds2.Tables(0).NewRow
NewRow.Item("ID") = 254
NewRow.Item("Interval") = 22
NewRow.Item("Tutor1") = "Joe"
NewRow.Item("Tutor2") = "Mary"
ds2.Tables(0).Rows.Add(NewRow)
Me.DataGridView1.DataSource = ds2.Tables(0)

MsgBox("Got this far")

da2.Update(ds2, "Main") 'Here is where the error occurs.
 
Problem solved.

The database had a keyword used in a field name. Once that was corrected,
then the update worked.
 
Back
Top