Syntax error in INSERT INTO statement, OledbException was unhandled

[cutie0407]

im trying to add a new records to database access but when i click the button submit

it say's Syntax error in INSERT INTO statement, OledbException was unhandled

da.Update(ds, "MSHS") = syntax error INSERT INTO

here's my code

Public Class StudentRegistrationForm

    Dim inc As Integer
    Dim con As New OleDb.OleDbConnection
    Dim dbprovider As String
    Dim dbsource As String
    Dim ds As New DataSet
    Dim da As OleDb.OleDbDataAdapter
    Dim sql As String


    Private Sub StudentRegistrationForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        dbprovider = "Provider=Microsoft.ACE.OLEDB.12.0;"
        dbsource = "Data Source=C:\MSHS.accdb"
        con.ConnectionString = dbprovider & dbsource

        con.Open()

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

     
    End Sub
    Private Sub EXitBT_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles EXitBT.Click
        Application.Exit()
    End Sub

    Private Sub SRSubmitBT_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SRSubmitBT.Click

        Dim cb As New OleDb.OleDbCommandBuilder(da)
        Dim dsNewRow As DataRow
        dsNewRow = ds.Tables("MSHS").NewRow()
        dsNewRow.Item("Student Number") = SRStudNumTB.Text
        dsNewRow.Item("First Name") = SRFirstNameTB.Text
        dsNewRow.Item("Middle Name") = SRMiddleNameTB.Text
        dsNewRow.Item("Last Name") = SRLastNameTB.Text
        ds.Tables("MSHS").Rows.Add(dsNewRow)
        da.Update(ds, "MSHS")
        MsgBox("New Record added to the Database")

    End Sub

End Class

[apegram]

It has been a while since I have worked with the CommandBuilder line of objects, but perhaps you can use the .GetInsertCommand().CommandText method & property to retrieve what the builder is creating for you and display it on the screen so you can determine what issue there may be with your method.

A better approach, however, may be to simply forego the builder and control your own inserts and updates. Construct your own SQL statements and use a OleDbCommand object and the .ExecuteNonQuery() method to perform your database updates.

[cutie0407]

It has been a while since I have worked with the CommandBuilder line of objects, but perhaps you can use the .GetInsertCommand().CommandText method & property to retrieve what the builder is creating for you and display it on the screen so you can determine what issue there may be with your method.

A better approach, however, may be to simply forego the builder and control your own inserts and updates. Construct your own SQL statements and use a OleDbCommand object and the .ExecuteNonQuery() method to perform your database updates.

I did what u said so i changed my codes to like this

Public Class StudentRegistrationForm

    Private Sub SRSubmitBT_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SRSubmitBT.Click

        Dim conn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\Data Sources\MSHS Enrollment System Database.accdb")
        Dim newrecord As New System.Data.OleDb.OleDbCommand

        newrecord.CommandType = System.Data.CommandType.Text
        newrecord.CommandText = "INSERT INTO Students (StudentNumber, FirstName, MiddleName, LastName) VALUES (3, 'aa', 'bb', 'cc')"
        newrecord.Connection = conn

        conn.Open()
        newrecord.ExecuteNonQuery()
        conn.Close()
        MsgBox("Successful")

    End Sub

    Private Sub EXitBT_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles EXitBT.Click
        Application.Exit()
    End Sub
End Class

it has no errors but it does not add a record to the database...

how is that?? please help

[apegram]

Interesting. I'm afraid nothing jumps out as being incorrect in your code sample. ExecuteNonReader() returns an integer indicating the number of records affected, which should be 1 in this case. If it returns 1 and you see your message box, you should be confident that the record is, in fact, being inserted.

Shot in the dark, you don't have the database open on your desktop along with the table, do you? You're not going to visibly see the new record without closing and reopening the table.