Syntax error in INSERT INTO statement VB using Access

Question

I've been struggling with this for a while now and just don't understand what is wrong. I'm doing this for my college coursework and have little to no experience in coding. All I am trying to do here is insert Values from my windows form into my Access database.

It keeps coming up with the same error which is

"Syntax error in INSERT INTO statement"

Dim sqlconn As New OleDb.OleDbConnection
    Dim sqlquery As New OleDb.OleDbCommand
    Dim connString As String
    connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\Computer science\Coursework\Database.accdb"
    sqlconn.ConnectionString = connString
    sqlquery.Connection = sqlconn
    sqlconn.Open()
    Dim Query As String = "INSERT INTO Client_form (MemberNumber, Forename, Surname, Age, SkillLevel, Session) VALUES (" & NewMem.ToString & ", '" & TextBox1.Text & "', '" & TextBox2.Text & "', " & TextBox3.Text & ", '" & TextBox5.Text & "', '" & TextBox4.Text & "');"
    'sqlquery.CommandText = "INSERT INTO Client form(MemberNumber, Firstname, Surname, Age, Skill level, Session)VALUES(@MemberNumber, @Firstname, @Surname, @Age, @Skill level, @Session)"
    'sqlquery.Parameters.AddWithValue("@MemberNumber", NewMem)
    'sqlquery.Parameters.AddWithValue("@Firstname", TextBox1.Text)
    'sqlquery.Parameters.AddWithValue("@Surname", TextBox2.Text)
    'sqlquery.Parameters.AddWithValue("@Age", Convert.ToInt16(TextBox3.Text))
    'sqlquery.Parameters.AddWithValue("@Skill level", TextBox5.Text)
    'sqlquery.Parameters.AddWithValue("@Session", TextBox4.Text)
    'sqlquery.CommandText = "INSERT INTO Client_form (MemberNumber, Firstname, Surname, Age, Skill level, Session) VALUES (345, t, e, 5, 7, am);"
    sqlquery.CommandText = Query
    MsgBox(sqlquery.CommandText.ToString)
    sqlquery.ExecuteNonQuery()
    sqlconn.Close()

Any kind of guidance will be greatly appreciated, Thanks!

Some of your VALUES have single quotes around them and some dont, see the first 2: (" & NewMem.ToString & ", '" & TextBox1.Text & "', The first one does not have the single quotes (you need them) the second one does, so all of them should look similar to th esecond one. — Brad, Apr 19 '18 at 17:03
Why did you comment out the parameterized query and used a string concatenation created command? — Steve, Apr 19 '18 at 17:04
I would not use the first version where you insert _user input directly into your query_. You surely know that this is open to [SQL Injection](http://www.bobby-tables.com). And it is hard to read and check if all quotation marks and commas are correct. In the second (parameterized) version, I'm not sure if it is legal to use "Skill level" (containing a white space) as parameter name. — René Vogt, Apr 19 '18 at 17:05
The problem is the SESSION field. Session is a reserved keyword in Access. You should write it as _[Session]_ in your query. Or change the name to a different text. After fixing it remove the string concatenation and return ASAP to use parameters — Steve, Apr 19 '18 at 17:05
Because of your SQL injection vulnerability, you *do not control* the syntax of the SQL query you're executing. You also *aren't showing us* the actual runtime query you're executing. Clearly that query, whatever it is, has a syntax error. Examine the query at runtime to see what that error is. Or (mostly) prevent this problem from happening by using parameterized queries *like your code already tried to do*. — David, Apr 19 '18 at 17:05

score 0 · Answer 1 · answered Apr 20 '18 at 01:22

try this ..

Dim sqlconn As New OleDb.OleDbConnection
Dim sqlquery As New OleDb.OleDbCommand
Dim connString As String
connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\Computer science\Coursework\Database.accdb"
sqlconn.ConnectionString = connString
sqlquery.Connection = sqlconn
sqlconn.Open()
Dim Query As String = "INSERT INTO Client_form([MemberNumber], [Firstname], [Surname], [Age], [Skilllevel], [Session])VALUES(@MemberNumber, @Firstname, @Surname, @Age, @Skill level, @Session)"
'sqlquery.CommandText = "INSERT INTO Client form([MemberNumber], [Firstname], [Surname], [Age], [Skilllevel], [Session])VALUES(@MemberNumber, @Firstname, @Surname, @Age, @Skill level, @Session)"
sqlquery.Parameters.AddWithValue("@MemberNumber", NewMem.Text)
sqlquery.Parameters.AddWithValue("@Firstname", TextBox1.Text)
sqlquery.Parameters.AddWithValue("@Surname", TextBox2.Text)
sqlquery.Parameters.AddWithValue("@Age",TextBox3.Text)
sqlquery.Parameters.AddWithValue("@Skilllevel", TextBox5.Text)
sqlquery.Parameters.AddWithValue("@Session", TextBox4.Text)
sqlquery.CommandText = Query
MsgBox(sqlquery.CommandText.ToString)
sqlquery.ExecuteNonQuery()
sqlconn.Close()

Also remove the space between Skill(space)level. Change also the datatype of age into shortext. I hope it will works.

score 0 · Answer 2 · answered Apr 20 '18 at 07:04

I removed redundant lines, also note to parse the values with their proper type. you might want to try this

    Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\Computer science\Coursework\Database.accdb"
    Dim cmd As New OleDbCommand("INSERT INTO Client form(MemberNumber, Firstname, Surname, Age, SkillLevel, Session)VALUES(@MemberNumber, @Firstname, @Surname, @Age, @SkillLevel, @Session)")

    cmd.Parameters.AddWithValue("@MemberNumber", NewMem.ToString)
    cmd.Parameters.AddWithValue("@Firstname", TextBox1.Text)
    cmd.Parameters.AddWithValue("@Surname", TextBox2.Text)
    cmd.Parameters.AddWithValue("@Age", TextBox3.Text)
    cmd.Parameters.AddWithValue("@SkillLevel", TextBox5.Text)
    cmd.Parameters.AddWithValue("@Session", TextBox4.Text)

    Using con As New OleDbConnection(connString)
        cmd.Connection = con
        con.Open()
        cmd.ExecuteNonQuery()
        con.Close()
    End Using

Syntax error in INSERT INTO statement VB using Access

2 Answers2