help!! insert syntax error when using OleDbDataAdapter.Update

[Guest]

Try the following code.

The datasouce is MS access file, and the table abc has only two columns:
1. ID (integer) key
2. section (integer) non-unique

And try the following code would cause an exception saying that "syntax
error in insert statement" during the myad.Update(mydtb) (I'm sure that it's
not the key problem, there's nothing at all in the table abc in the MDB file)

if i changed the column 2 name ("section“) to another one in the datasouce
(MDB file), let's say "num", the following code would work properly.

So I'm very confused!! Any help woul d be appreciated.


VB. NET code

Dim connStr As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=C:\abc.mdb; Jet OLEDBatabase Password="

Private Sub initdb(ByRef conn As OleDbConnection)
conn = New OleDbConnection(connStr)
conn.Open()
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim conn As OleDbConnection
Dim mydtb As New DataTable
initdb(conn)
Dim mycommand As OleDbCommand = New OleDbCommand("select * from
abc", conn)

Dim myad As OleDbDataAdapter = New OleDbDataAdapter(mycommand)
Try
myad.Fill(mydtb)
Catch ex As Exception
MsgBox(ex.Message)
End Try
Dim drow As DataRow = mydtb.NewRow()
drow("id") = 1234
drow("section") = 1
mydtb.Rows.Add(drow)
Dim mycb As New OleDbCommandBuilder(myad)
AddHandler myad.RowUpdating, New
OleDbRowUpdatingEventHandler(AddressOf OnRowUpdating)
Try
myad.Update(mydtb)
Catch ex As Exception
MsgBox(ex.Message)
End Try

DataGrid1.DataSource = mydtb

conn.Close()
End Sub


Private Sub OnRowUpdating(ByVal sender As Object, ByVal args As
OleDbRowUpdatingEventArgs)
If args.StatementType = StatementType.Insert Then
Dim p As OleDbCommand = args.Command
Stop
'p.ExecuteNonQuery()
End If
End Sub

 

[Paul Clement]

¤ Try the following code.
¤
¤ The datasouce is MS access file, and the table abc has only two columns:
¤ 1. ID (integer) key
¤ 2. section (integer) non-unique
¤
¤ And try the following code would cause an exception saying that "syntax
¤ error in insert statement" during the myad.Update(mydtb) (I'm sure that it's
¤ not the key problem, there's nothing at all in the table abc in the MDB file)
¤
¤ if i changed the column 2 name ("section“) to another one in the datasouce
¤ (MDB file), let's say "num", the following code would work properly.
¤
¤ So I'm very confused!! Any help woul d be appreciated.
¤
¤
¤ VB. NET code
¤
¤ Dim connStr As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data
¤ Source=C:\abc.mdb; Jet OLEDBatabase Password="
¤
¤ Private Sub initdb(ByRef conn As OleDbConnection)
¤ conn = New OleDbConnection(connStr)
¤ conn.Open()
¤ End Sub
¤
¤ Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
¤ System.EventArgs) Handles Button1.Click
¤ Dim conn As OleDbConnection
¤ Dim mydtb As New DataTable
¤ initdb(conn)
¤ Dim mycommand As OleDbCommand = New OleDbCommand("select * from
¤ abc", conn)
¤
¤ Dim myad As OleDbDataAdapter = New OleDbDataAdapter(mycommand)
¤ Try
¤ myad.Fill(mydtb)
¤ Catch ex As Exception
¤ MsgBox(ex.Message)
¤ End Try
¤ Dim drow As DataRow = mydtb.NewRow()
¤ drow("id") = 1234
¤ drow("section") = 1


The field name you are using "section" is a Microsoft Jet reserved word. If must be enclosed within
brackets when referenced in a SQL statement.

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/default.aspx?scid=kb;en-us;321266


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)