I'm writing an application to update a database with student records. I've used this particular code before, but with just a single column, but i added multiple columns to this one. when i call the sub to save the records it gives me this exception:

syntax error in INSERT INTO statement at line 41

Please any hints on how to correct my code?

Dim inc As Integer
    Dim MaxRows 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 frmVotersRegistration_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
       
        dbProvider = "PROVIDER=Microsoft.ACE.OLEDB.12.0;"
        dbSource = "Data Source=|DataDirectory|\Codes.accdb"

        con.ConnectionString = dbProvider & dbSource

        con.Open()

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

        
    End Sub

   Private Sub SaveRecords()
        Dim cb As New OleDb.OleDbCommandBuilder(da)
        Dim dsNewRow As DataRow
        dsNewRow = ds.Tables("Register").NewRow()

        dsNewRow.Item("Full Name") = txtFullname.Text
        dsNewRow.Item("Matriculation Number") = txtMatNo.Text
        dsNewRow.Item("Faculty") = cmbBoxFaculty.Text
        dsNewRow.Item("Level") = cmbBoxLevel.Text
        dsNewRow.Item("Department") = cmbBoxDept.Text
        If rdbtnFemale.Checked = True Then dsNewRow.Item("Sex") = "F"
        If rdbtnMale.Checked = True Then dsNewRow.Item("Sex") = "M"
        ds.Tables("Register").Rows.Add(dsNewRow)

        da.Update(ds, "Register")
        MsgBox("New Record added to the Database")
    End Sub

Recommended Answers

All 2 Replies

There is no insert, no update and no delete commands of the dataadapter to be used in the da.Update(ds, "Register") sentence.

Please provide the corresponding commands. (see here)

I'm old school. I prefer to do my insert queries using the connection object. An insert query looks like

insert into tablename (col1name,col2name,col3name) values (col1value,col2value,col3value)

In your case the connection object is named con so once you have composed your query in a string (let's call it query), you execute it by

con.Execute(query)

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.