Member Avatar for man924

the code is below.
the system said me that the insert into sql syntax error
any solutions?

Dim dbProvider As String
    Dim dbSource As String
    Dim constr As New OleDb.OleDbConnection
    Dim sql As String
    Dim ds As New DataSet
    Dim da As OleDb.OleDbDataAdapter



form load code:
        dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
        dbSource = "Data Source = pj.mdb"
        constr.ConnectionString = dbProvider & dbSource
        sql = "select * from information"
        da = New OleDb.OleDbDataAdapter(sql, constr)
        da.Fill(ds, "bking")


button click event :
        Dim cb As New OleDb.OleDbCommandBuilder(da)
        Dim dsNewRow As DataRow
        dsNewRow = ds.Tables("bking").NewRow()
        dsNewRow.Item(0) = "1231231"
        ds.Tables("bking").Rows.Add(dsNewRow)
        da.Update(ds, "bking")
        MsgBox("New Record added to the Database")
Edited by Nick Evan because: Added [CODE] tags
  • 2 Contributors
  • 1 Reply
  • 298 Views
  • 6 Hours Discussion Span
  • Latest Post Latest Post by IrshadIkhlas

Dani AI

Generated

Quick checklist and concrete fixes for the “Syntax error in INSERT INTO statement” when using a DataSet/DataAdapter+CommandBuilder (as shown by ). Common root causes are reserved table/column names, trying to set an AutoNumber column manually, or the CommandBuilder not having full schema (so it generates bad SQL). ’s manual INSERT will work, but prefer a parameterized command rather than string concatenation to avoid type and quoting problems.

Debug step — inspect the actual SQL the CommandBuilder will use (do this immediately after Fill and before Update). This reveals the exact column list and any unescaped names:

da.MissingSchemaAction = MissingSchemaAction.AddWithKey
Dim cb As New OleDb.OleDbCommandBuilder(da)
Dim insertCmd As OleDb.OleDbCommand = CType(cb.GetInsertCommand(), OleDb.OleDbCommand)
Debug.WriteLine(insertCmd.CommandText)
For Each p As OleDb.OleDbParameter In insertCmd.Parameters
    Debug.WriteLine(p.ParameterName & " - " & p.OleDbType.ToString())
Next

If the generated SQL shows unbracketed names, spaces, or reserved words, either rename those columns in the database or ensure they are escaped with square brackets (e.g. [My Column]). If the table has an AutoNumber primary key, do not set that column value on the NewRow — leave it unset so Access can generate it.

If the CommandBuilder still produces invalid SQL, set a parameterized InsertCommand on the DataAdapter (example pattern):

Dim cmd As New OleDb.OleDbCommand("INSERT INTO [information] ([FieldA],[FieldB]) VALUES (?, ?)", constr)
cmd.Parameters.Add("FieldA", OleDbType.VarWChar, 100, "FieldA")
cmd.Parameters.Add("FieldB", OleDbType.Integer, 0, "FieldB")
da.InsertCommand = cmd
da.Update(ds, "bking")

Additional notes: do not call AcceptChanges() before Update (that clears pending changes), and prefer parameterized commands to avoid quoting/type issues. These steps make it clear whether the problem is generated SQL, reserved names, or a schema mismatch.

Member Avatar for IrshadIkhlas

Hi,

Add Oledbcommand and Oledbconnection in Your project and create connection and in below coding do changes as requied and it will work.

Dim ROW As DataRow
        Dim Cmd As New OleDb.OleDbCommand
        OleDbConnection1.Open()

                '
        With Cmd
            .CommandText = "INSERT INTO [TableName] (VERSION, SMASK, EQP, FCAP, JCAP, YCAP)  VALUES  ('" & VERSION.Text & "', '" & SMASK.Text & "', '" & EQP.Text & "','" & FCAP.Text & "', '" & JCAP.Text & "', '" & YCAP.Text & "')"
            .CommandType = CommandType.Text
            .Connection = Me.OleDbConnection1
            .ExecuteNonQuery()
        End With

        OleDbConnection1.Close()
Edited by Nick Evan because: Added [CODE] tags. Learn to use them yourself please.
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.