INSERT / UPDATE error updating access database through VB.net

Question

I am trying to update / save a new record in an access database file through VB.

When I run the application and press the save or update button I am receiving the 2 errors:

Additional information: Syntax error in INSERT INTO statement.

Additional information: Syntax error in UPDATE statement.

Can anyone see the problem with my syntax?

I will attach the code and a screenshot of GUI

Imports System.Data.OleDb

Public Class Form1

    Dim dbconn As New OleDbConnection
    Dim adt As New OleDbDataAdapter
    Dim ds As New DataSet

    Dim datatable As New DataTable
    Dim cmd As New OleDbCommand


    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        dbconn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; data source = CUBSDatabase.accdb"
        showData()
    End Sub

    Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
        adt = New OleDbDataAdapter("insert into Student (FName, SName, Attendance, CA1, CA2, FinalExam) values ( '" & txtFName.Text & "','" & txtSName.Text & "',  '" & txtAttendance.Text & "', '" & txtCA1.Text & "', '" & txtCA2.Text & "', '" & txtFinalExam.Text & "', )", dbconn)

        adt.Fill(ds)
        ds = New DataSet
        showData()
        MsgBox("Saved")
    End Sub

    Private Sub showData()
        Dim dbcommand As String
        dbcommand = "SELECT * FROM Student"
        adt = New OleDbDataAdapter(dbcommand, dbconn)
        datatable = New DataTable
        adt.Fill(datatable)
        DataGridView1.DataSource = datatable
    End Sub

    Private Sub btnFind_Click(sender As Object, e As EventArgs) Handles btnFind.Click
        Dim sql = "select * from Student where ID =" & txtID.Text & " "
        adt = New OleDbDataAdapter(sql, dbconn)
        cmd = New OleDbCommand(sql)
        adt.Fill(ds, "Student")

        txtFName.Text = ds.Tables("Student").Rows(0)(1).ToString
        txtSName.Text = ds.Tables("Student").Rows(0)(2).ToString
        txtAttendance.Text = ds.Tables("Student").Rows(0)(3).ToString
        txtCA1.Text = ds.Tables("Student").Rows(0)(4).ToString
        txtCA2.Text = ds.Tables("Student").Rows(0)(5).ToString
        txtFinalExam.Text = ds.Tables("Student").Rows(0)(6).ToString
        ds = New DataSet
    End Sub

    Private Sub TabPage1_Click(sender As Object, e As EventArgs) Handles TabPage1.Click

    End Sub

    Private Sub btnUpdate_Click(sender As Object, e As EventArgs) Handles btnUpdate.Click
        adt = New OleDbDataAdapter("update Student set FName='" & txtFName.Text & "', SName='" & txtSName.Text & "', Attendance='" & txtAttendance.Text & "', CA1'" & txtCA1.Text & "', CA2'" & txtCA2.Text & "', FinalExam'" & txtFinalExam.Text & "'where ID=" & txtID.Text & "", dbconn)

        adt.Fill(ds)
        ds = New DataSet
        showData() ' refresh data in datagridview
        MsgBox("Updated")
    End Sub
End Class

This is my GUI

you have an extra comma at the end of your list of values "', ) remove this '" & txtFinalExam.Text & "', ) should be '" & txtFinalExam.Text & "') — Brad, Apr 13 '18 at 12:45
You’re missing lots of equal signs in the update command also — Sami Kuhmonen, Apr 13 '18 at 12:50
Always use parameters to avoid sql injection and formatting errors. — LarsTech, Apr 13 '18 at 14:01

James Lingham · Answer 1 · 2018-04-13T12:49:43.310

0

As far as i can tell, you are missing your closing ' in the following line

adt = New OleDbDataAdapter("update Student set FName='" & txtFName.Text & "', SName='" & txtSName.Text & "', Attendance='" & txtAttendance.Text & "', CA1'" & txtCA1.Text & "', CA2'" & txtCA2.Text & "', FinalExam'" & txtFinalExam.Text & "'where ID=" & txtID.Text & "", dbconn)

should be

adt = New OleDbDataAdapter("update Student set FName='" & txtFName.Text & "', SName='" & txtSName.Text & "', Attendance='" & txtAttendance.Text & "', CA1'" & txtCA1.Text & "', CA2'" & txtCA2.Text & "', FinalExam'" & txtFinalExam.Text & "'where ID=" & txtID.Text & "'", dbconn)

edited Apr 13 '18 at 12:49

answered Apr 13 '18 at 12:48

James Lingham

419
1
3
17

No, that’s not it. There’s other errors but there shouldn’t be one there – Sami Kuhmonen Apr 13 '18 at 12:49
why shouldn't there be a closing ' there? they open it when defining the WHERE? – James Lingham Apr 13 '18 at 12:50
They weren’t. They were closing one before the WHERE clause. The where clause must not be in quotes. – Sami Kuhmonen Apr 13 '18 at 12:52

score 0 · Accepted Answer · answered Apr 13 '18 at 13:11

James was almost there, but I think this should do it for you...

                adt = New OleDbDataAdapter("update Student set FName='" & txtFName.Text _
                                           & "', SName='" & txtSName.Text _
                                           & "', Attendance='" & txtAttendance.Text _
                                           & "', CA1='" & txtCA1.Text _
                                           & "', CA2='" & txtCA2.Text _
                                           & "', FinalExam='" & txtFinalExam.Text _
                                           & "' where ID='" & txtID.Text & "'", dbconn)

I have blocked it for easier reading, the full string is below too if you prefer that (Both do same thing).

adt = New OleDbDataAdapter("update Student set FName='" & txtFName.Text & "', SName='" & txtSName.Text & "', Attendance='" & txtAttendance.Text & "', CA1='" & txtCA1.Text & "', CA2='" & txtCA2.Text & "', FinalExam='" & txtFinalExam.Text & "' where ID='" & txtID.Text & "'", dbconn)

Side note - Be aware of the texts you are passing too - for example if txtSName contains a ' within the string, it is going to throw errors. Have a check of This Stackoverflow Article or Microsoft SQL Parameters They both can help you with this type of coding.

INSERT / UPDATE error updating access database through VB.net

2 Answers2