Syntax error in INSERT INTO statement generated by OleDbCommandBuilder

Question

Why does this keep telling me

Syntax error in INSERT INTO statement

I searched for more details but it keeps telling me this.

This is the code :

Imports System.Data
Imports System.Data.OleDb
Public Class f9
    Dim ds As New DataSet
    Dim da As OleDb.OleDbDataAdapter

    Dim con As New OleDb.OleDbConnection
    Dim dbProvider As String
    Dim dbSource As String

    Dim sql As String


    Private Sub f9_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        dbProvider = "Provider=Microsoft.Jet.OLEDB.4.0;"
        dbSource = "Data Source = E:\21.mdb"

        con.ConnectionString = dbProvider & dbSource
        con.Open()

        sql = "SELECT * FROM snack"
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "snack")

        da = New OleDb.OleDbDataAdapter(sql, con)

    End Sub
    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles b1.Click
        Me.Close()
        x = x + (5 * 1)
        If d.tc.Text = f7.b1.Text Then
            Dim cb As New OleDb.OleDbCommandBuilder(da)
            Dim dsNewRow As DataRow

            dsNewRow = ds.Tables("snack").NewRow()

            dsNewRow.Item("Date") = f1.d1.Text
            dsNewRow.Item("Order") = d.tc.Text
            dsNewRow.Item("Number Of Items") = b1.Text
            dsNewRow.Item("Price") = " 5 "
            dsNewRow.Item("Total") = x
            ds.Tables("snack").Rows.Add(dsNewRow)
            da.Update(ds, "snack")
            con.Close()
        End If




    End Sub


End Class

Can you point to the line of code that generates this error? — parakmiakos
– parakmiakos, Commented Feb 19, 2014 at 12:33

Andrew Morton · Accepted Answer · 2021-12-26 18:07:55Z

4

Some of your field names are reserved words in Access SQL (Date, Order) and you also have a field name with spaces in it. The default configuration of the CommandBuilder will not produce valid SQL statements in cases like this.

To fix this issue, immediately after the line...

Dim cb As New OleDb.OleDbCommandBuilder(da)

...add the following two lines:

cb.QuotePrefix = "["
cb.QuoteSuffix = "]"

That will tell the command builder to enclose table and field names in square brackets ([]) so instead of generating a statement like

INSERT INTO snack (Date, Order, Number Of Items) VALUES ...

it will generate a statement like

INSERT INTO [snack] ([Date], [Order], [Number Of Items]) VALUES ...

Those square brackets are required for the SQL statement to be syntactically correct.

edited Dec 26, 2021 at 18:07

Andrew Morton

25.3k9 gold badges66 silver badges96 bronze badges

answered Feb 19, 2014 at 13:21

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Syntax error in INSERT INTO statement generated by OleDbCommandBuilder

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related