OleDbCommandBuilder 创建导致“语法错误"的 SQL 语句 [英] OleDbCommandBuilder creates SQL statements that result in "syntax error"

问题描述

谁能解释一下为什么当我点击提交"按钮时出现错误

Can someone please explain why, when I click the "Commit" button, I get the error

INSERT INTO 语句中的语法错误.

Syntax error in INSERT INTO statement.

这是代码.

Public Class Form3
    Dim inc As Integer
    Dim MaxRows As Integer
    Dim con As New OleDb.OleDbConnection
    Dim ds As New DataSet
    Dim da As OleDb.OleDbDataAdapter

    Dim sql As String

    Private Sub Form3_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = D:TA_Officers.mdb"
        con.Open()
        sql = "SELECT * from TA_OFFICER"
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "TA_Officers")
        con.Close()

        MaxRows = ds.Tables("TA_Officers").Rows.Count
        inc = -1
    End Sub

    Private Sub NavigateRecords()
        txtFName.Text = ds.Tables("TA_Officers").Rows(inc).Item(1)
        txtMInitial.Text = ds.Tables("TA_Officers").Rows(inc).Item(2)
        txtLName.Text = ds.Tables("TA_Officers").Rows(inc).Item(3)
        txtContact.Text = ds.Tables("TA_Officers").Rows(inc).Item(4)
        txtEmail.Text = ds.Tables("TA_Officers").Rows(inc).Item(5)
        txtPosition.Text = ds.Tables("TA_Officers").Rows(inc).Item(6)
        txtCourse.Text = ds.Tables("TA_Officers").Rows(inc).Item(7)
        txtAddress.Text = ds.Tables("TA_Officers").Rows(inc).Item(8)
    End Sub

    Private Sub BtnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnAdd.Click
        BtnCommit.Enabled = True
        BtnAdd.Enabled = False
        BtnUpdate.Enabled = False
        BtnDel.Enabled = False

        txtPosition.Clear()
        txtLName.Clear()
        txtFName.Clear()
        txtMInitial.Clear()
        txtAddress.Clear()
        txtCourse.Clear()
        txtEmail.Clear()
        txtContact.Clear()
    End Sub

    Private Sub RdMember_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles RdMember.CheckedChanged
        Label2.Visible = False
        txtPosition.Visible = False
    End Sub

    Private Sub RdOfficer_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles RdOfficer.CheckedChanged
        Label2.Visible = True
        txtPosition.Visible = True
    End Sub

    Private Sub BtnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnUpdate.Click
        Dim cb As New OleDb.OleDbCommandBuilder(da)

        ds.Tables("TA_Officers").Rows(inc).Item(1) = txtFName.Text
        ds.Tables("TA_Officers").Rows(inc).Item(2) = txtMInitial.Text
        ds.Tables("TA_Officers").Rows(inc).Item(3) = txtLName.Text
        ds.Tables("TA_Officers").Rows(inc).Item(4) = txtContact.Text
        ds.Tables("TA_Officers").Rows(inc).Item(5) = txtEmail.Text
        ds.Tables("TA_Officers").Rows(inc).Item(6) = txtPosition.Text
        ds.Tables("TA_Officers").Rows(inc).Item(7) = txtCourse.Text
        ds.Tables("TA_Officers").Rows(inc).Item(8) = txtAddress.Text

        da.Update(ds, "TA_Officers")

        MsgBox("Data Updated!")
    End Sub

    Private Sub BtnDel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnDel.Click
        Dim cb As New OleDb.OleDbCommandBuilder(da)

        ds.Tables("TA_Officers").Rows(inc).Delete()
        MaxRows = MaxRows - 1

        inc = 0
        NavigateRecords()
        da.Update(ds, "TA_Officers")
    End Sub

    Private Sub BtnClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnClear.Click
        BtnCommit.Enabled = False
        BtnAdd.Enabled = True
        BtnUpdate.Enabled = True
        BtnDel.Enabled = True

        inc = 0
        NavigateRecords()
    End Sub

    Private Sub BtnCommit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnCommit.Click
        If inc <> -1 Then

            Dim cb As New OleDb.OleDbCommandBuilder(da)
            Dim dsNewRow As DataRow

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

            dsNewRow.Item("Firstname") = txtFName.Text
            dsNewRow.Item("Middleinitial") = txtMInitial.Text
            dsNewRow.Item("Lastname") = txtLName.Text
            dsNewRow.Item("Mobilenumber") = txtContact.Text
            dsNewRow.Item("Emailaddress") = txtEmail.Text
            dsNewRow.Item("Position") = TxtPosition.Text
            dsNewRow.Item("Course") = txtCourse.Text
            dsNewRow.Item("Address") = txtAddress.Text

            ds.Tables("TA_Officers").Rows.Add(dsNewRow)
            **da.Update(ds, "TA_Officers")**

            MsgBox("New Record added to the Database")
            BtnCommit.Enabled = False
            BtnAdd.Enabled = True
            BtnUpdate.Enabled = True
            BtnDel.Enabled = True
        End If
    End Sub

    Private Sub BtnBack_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnBack.Click
        Form2.Show()
        Me.Close()
    End Sub

    Private Sub TA_MEMBERSBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Me.Validate()
        Me.TA_MEMBERSBindingSource.EndEdit()
        Me.TableAdapterManager.UpdateAll(Me.TA_OfficersDataSet)

    End Sub
End Class

推荐答案

正如 Makita 所提到的，您的问题源于 Position 是 Jet/ACE SQL 中的保留字这一事实.解决方法是在创建OleDbCommandBuilder对象后添加以下两行代码:

As Makita mentions, your problem stems from the fact that Position is a reserved word in Jet/ACE SQL. The solution is to add the following two lines of code after you create the OleDbCommandBuilder object:

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

这将告诉 OleDbCommandBuilder 生成这样的 SQL 语句

That will tell the OleDbCommandBuilder to generate SQL statements like this

INSERT INTO [TA_OFFICER] ([FirstName], ...

...而不是

INSERT INTO TA_OFFICER (FirstName, ...

将 Position 列名包裹在这些方括号中将告诉 Jet 数据库引擎它是一个列名，而不是一个关键字.

Wrapping the Position column name in those square brackets will tell the Jet database engine that it is a column name, not a keyword.

这篇关于OleDbCommandBuilder 创建导致“语法错误"的 SQL 语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！