CODEWITHSUNDEEP
vb.netms-accessoledb
Ronnel Yatco
Ronnel Yatco

Reputation: 51

OleDbCommandBuilder creates SQL statements that result in "syntax error"

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

Syntax error in INSERT INTO statement.

Here's the code.

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

Upvotes: 2

Views: 2675

Answers (3)

Gord Thompson
Gord Thompson

Reputation: 123449

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 = "]"

That will tell the OleDbCommandBuilder to generate SQL statements like this

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

...instead of

INSERT INTO TA_OFFICER (FirstName, ...

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

Upvotes: 2

Makita
Makita

Reputation: 746

Position is a reserved word for JET 4.0 - try to rename the column something else such as prog_position. Access will often let you create the column with a reserved name, but when you go to access it in code JET blocks it and gives you a generic Syntax error in INSERT INTO Statement error without being specific. Check out http://support.microsoft.com/kb/248738 for more information on reserved words. It is goods practice to always prefix your column names with something short so that you never run into conflicts or reserved word issues.

Upvotes: 2

Tata
Tata

Reputation: 809

This kind of error can happen when you have different data type in the code and in the DB. Checking the log can give some light on this.

Upvotes: 0

Related Questions