INSERT INTO and UPDATE SQL using visual basic into access database

Question

I'm working on my A Level coursework using VB forms as my front end and an Access database as the back end. I've tried loads of different ways but I can't get the program to update or insert data into the database. I know for a fact the connection is fine because I've had no problem retrieving data from access into the program.

This the code for one of the forms: (the database connection is in a separate form)

Access.ExecQuery("SELECT * FROM Exam;")
        Dim user As String = TxtStudent.Text
        Dim board As String = CmbBoard.Text
        Dim instrument As String = CmbInstrument.Text
        Dim grade As String = CmbGrade.Text
        Dim result As String = CmbResult.Text


        Access.ExecQuery("INSERT INTO Grade (Username, Instrument, Exam Board, Grade, Result) VALUES ('" & user & "', '" & board & "', '" & instrument & ", " & grade & ", " & result & "');")
        If Not String.IsNullOrEmpty(Access.Exception) Then MsgBox(Access.Exception) : Exit Sub

The error message says there is a syntax error on INSERT INTO statement. Am i just being really stupid?

Well, string concatenation for SQL statement often causing this "syntax error" issue. I suggest you use [parameterized query](https://stackoverflow.com/questions/1851341/how-to-create-parameterized-queries-in-vb-net) instead. — Tetsuya Yamamoto, Apr 16 '18 at 09:09

score 0 · Answer 1 · answered Apr 16 '18 at 09:07

0

you are missing closing "'" for instrument '" & instrument & "', " . and also, just confirm the values for fields without single quotes(grade ) are numeric otherwise add single quotes

answered Apr 16 '18 at 09:07

Aswani Madhavan

816
6
19

Thank you for pointing that out but after fixing that syntax I still have the same problem – JayPeg Apr 16 '18 at 09:35
@JayPeg Post modified code here and ensure you have values for all variables – Aswani Madhavan Apr 16 '18 at 09:37
Access.ExecQuery("SELECT * FROM Exam;") Dim user As String = TxtStudent.Text Dim board As String = CmbBoard.Text Dim instrument As String = CmbInstrument.Text Dim grade As String = CmbGrade.Text Dim result As String = CmbResult.Text Access.ExecQuery("INSERT INTO Grade (Username, Instrument, Exam Board, Grade, Result) VALUES ('" & user & "', '" & board & "', '" & instrument & "', " & grade & ", '" & result & "');") If Not String.IsNullOrEmpty(Access.Exception) Then MsgBox(Access.Exception) : Exit Sub – JayPeg Apr 16 '18 at 09:53

score 0 · Answer 2 · answered Apr 16 '18 at 10:10

Your single and double parenthesis are a bit of a mess. This alone is a good reason to use parameters but it also protects you from malicious input by users. The important thing with Access is that you must add the parameters in the same order that the command uses them.

        Dim cn As New OleDbConnection("Your Access connection string")
        Dim s As String = "INSERT INTO Grade (Username, Instrument, Exam Board, Grade, Result) VALUES (@User, @Instrument, @Board, @Grade, @Result);"
        Dim cmd As New OleDbCommand(s, cn)
        cmd.Parameters.AddWithValue("@User", TxtStudent.Text)
        cmd.Parameters.AddWithValue("@Instrument", CmbInstrument.Text)
        cmd.Parameters.AddWithValue("@Board", cmdBoard.Text)
        cmd.Parameters.AddWithValue("@Grade", CmdGrade.Text)
        cmd.Parameters.AddWithValue("@Result", CmdResult.Text)
        cn.Open()
        cmd.ExecuteNonQuery()
        cn.Close()

Double check the data types of the fields and adjust the code if they are not all strings.

score 0 · Answer 3 · answered Apr 16 '18 at 12:29

In SQL Queries and statements , '(single quote) is used to pass a value of type string to any given parameter(or anything).You mistake was that you forgot to add ' in all the places.

   "INSERT INTO Grade (Username, Instrument, Exam Board, Grade, Result) VALUES ('" & user & "', '" & board & "', '" & instrument & ", "'" & grade & "'", "'" & result & "'")"

This will solve it :)

However, one advice, don't give direct values in the statement itself,you are welcoming SQL-Injection.Rather,create parameters and values to them later :

 Dim cmd as New SqlCommand("INSERT INTO Grade (Username)Values(@uname)",con)
 cmd.Parameter.Add("@uname",SqlDbType.Vachar) = "abc"

Hope this helps to enrich your knowledge :)

score 0 · Answer 4 · answered Apr 18 '18 at 01:42

You must try this!

    Dim con As New OleDbConnection("Your Access connection string here")
    Dim s As String = "INSERT INTO Grade ([Username], [Instrument], [Exam Board], [Grade], [Result]) VALUES (@User, @Instrument, @Board, @Grade, @Result)"
    Dim cmd As New OleDbCommand(s, con)
    con.Open()
    cmd.Parameters.AddWithValue("@User", TxtStudent.Text)
    cmd.Parameters.AddWithValue("@Instrument", CmbInstrument.Text)
    cmd.Parameters.AddWithValue("@Board", cmdBoard.Text)
    cmd.Parameters.AddWithValue("@Grade", CmdGrade.Text)
    cmd.Parameters.AddWithValue("@Result", CmdResult.Text)
    cmd.ExecuteNonQuery()
    con.Close()

I hope it will works! :)

score -2 · Answer 5 · edited Mar 11 '22 at 13:07

Dim con As New OleDbConnection("Your Access connection string here")
Dim s As String = "INSERT INTO Grade ([Username], [Instrument], [Exam Board], [Grade], [Result]) VALUES (@User, @Instrument, @Board, @Grade, @Result)"
Dim cmd As New OleDbCommand(s, con)
con.Open()
cmd.Parameters.AddWithValue("@User", TxtStudent.Text)
cmd.Parameters.AddWithValue("@Instrument", CmbInstrument.Text)
cmd.Parameters.AddWithValue("@Board", cmdBoard.Text)
cmd.Parameters.AddWithValue("@Grade", CmdGrade.Text)
cmd.Parameters.AddWithValue("@Result", CmdResult.Text)
cmd.ExecuteNonQuery()
con.Close()

INSERT INTO and UPDATE SQL using visual basic into access database

5 Answers5