Hello good people,

This syntax error keeps coming up whenever i click the "commit button" to add a new record to my ms access database. The error is, "Syntax error in INSERT INTO statement."

I got help to write a code to accept alphanumeric (e.g SC0001, SC0002...) in my order id textbox and it works fine whenever i click the "add button", but when i click the "commit button" to save the new record into the database, it brings up that error and i have tried to figure it out but still doesn't work.

Please someone should help me out.

Thanks for your help n advance.

The codes i have written for both the "add and commit" buttons are here below:

Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click

        txtStudID.Clear()
        txtStudName.Clear()
        txtSex.Clear()
        txtFaculty.Clear()
        txtStudID.Focus()

        With txtStudID
            If Not .Text = "" Then
                Dim myIDalpha As String = ""
                For i As Integer = 0 To .Text.Length - 1
                    If Not IsNumeric(.Text(i)) Then myIDalpha &= .Text(i) Else Exit For
                Next
                .Text = myIDalpha & (CInt(.Text.Substring(myIDalpha.Length)) + 1).ToString("0000")
            End If
        End With

    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(daTest)
            Dim dsNewRow As DataRow

            dsNewRow = dsTest.Tables("SouthCity").NewRow()

            dsNewRow.Item(0) = txtStudID.Text
            dsNewRow.Item(1) = txtStudName.Text
            dsNewRow.Item(2) = txtSex.Text
            dsNewRow.Item(3) = txtFaculty.Text

            dsTest.Tables("SouthCity").Rows.Add(dsNewRow)

            daTest.Update(dsTest, "SouthCity")

        End If
    End Sub

Do people really help on this forum anyway?? Been hoping for an answer for more than 5 hours already...gosh!

Not sure if your second post was sarcasm or not but...
Have you set the InsertCommand property of your CommandBuilder? I can't see it here.

daTest.InsertCommand = cb.GetInsertCommand()

Not sure if your second post was sarcasm or not but...
Have you set the InsertCommand property of your CommandBuilder? I can't see it here.

daTest.InsertCommand = cb.GetInsertCommand()

Thanks for the reply Hericles.

I didnt add this before but i added it now in the click event of the "Commit" button and its still showing me the same error. :(
Do i need to add something to it or add this code somewhere else?

Thanks for your help again

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.