VB: Syntax error when trying to insert to database with SQL

Question

I'm trying to insert a new record into a database using an SQL command but each time I run the program and try to add a new record I get an error telling me that there's a syntax error with my "INSERT INTO" statement. The data that I'm inserting is stored in an array + structure:

    Structure Question
        Dim QuestionName As String
        Dim Question As String
        Dim Ans1 As String
        Dim Ans2 As String
        Dim Ans3 As String
        Dim Ans4 As String
        Dim Difficulty As Integer
        Dim CorrectAns As String
    End Structure

    Dim arrQuestion as Question

and this is the sub im using to insert the record into the database:

    Try

        Dim InsertComm As New OleDb.OleDbCommand
        Dim dbAdap As New OleDb.OleDbDataAdapter

        ConnectToDB()

        Dim sqlInsert As String = "INSERT INTO questionDatabase(QuestionName, Question, 
                                   Answer 1, Answer 2, Answer 3, Answer 4, Correct answer,
                                   Difficulty ID) VALUES(" & Chr(39) & arrquestion.questionname 
                                   & Chr(39) & ", " & Chr(39) & arrquestion.question & Chr(39) &
                                   ", " & Chr(39) & arrquestion.ans1 & Chr(39) & ", " & Chr(39) 
                                   & arrquestion.ans2 & Chr(39) & ", " & Chr(39) & 
                                   arrquestion.ans3 & Chr(39) & ", " & Chr(39) & 
                                   arrquestion.ans4 & Chr(39) & ", " & Chr(39) & 
                                   arrquestion.correctans & Chr(39) & ", " & Chr(39) & 
                                   arrquestion.difficulty & Chr(39) & ");"

        InsertComm = New OleDb.OleDbCommand(sqlInsert, dbConn)

        InsertComm.ExecuteNonQuery()

        dbConn.Close()

    Catch ex As Exception
        MsgBox(Err.Description)
    Finally
        dbConn.Close()
    End Try

I've written and re-written this a bunch of times, googled the error it gives me and tried to copy the solutions that people posted there but I just couldn't get my head around how they wrote the code. Any help would really be appreciated.

you have spaces in the columnnames of your Insert Statement `(QuestionName, Question, Answer 1, Answer 2, Answer 3, Answer 4, Correct answer, Difficulty ID)` that's not valid. these should be the real column names as created in the database — Federico Berasategui, Jan 29 '13 at 19:58
Well, first off column names can't contain spaces (unless you put them in [brackets]) — Mike Christensen, Jan 29 '13 at 19:58
Parameterize and chances are this problem will go away. As this query stands, it's wide open to SQL injection: https://www.owasp.org/index.php/SQL_Injection — Tim M., Jan 29 '13 at 20:00
@MikeChristensen mentioned the brackets which is a necessity, I also think since you're passing it as a string you need to put single apostrophes on either side of each (N)Varchar datatype in your table. So instead of ...(" & Chr(39) & arrquestion.questionname & Chr(39) & ",... it should be ...('" & Chr(39) & arrquestion.questionname & Chr(39) & "',... — Eric J. Price, Jan 29 '13 at 20:11
ah! It's working now! thank you very much. Also, @TimMedora Thanks for the link, I'll look into that. — Edd B, Jan 29 '13 at 20:16

score 4 · Accepted Answer · edited May 23 '17 at 12:27

The core of your statement should be written in this way

Dim sqlInsert As String = "INSERT INTO questionDatabase(QuestionName, Question, " +
      "[Answer 1], [Answer 2], [Answer 3], [Answer 4], [Correct answer], " +
      "[Difficulty ID]) VALUES(?, ?, ?, ?, ?, ?, ?, ?)"
InsertComm = New OleDb.OleDbCommand(sqlInsert, dbConn)
InsertComm.Parameters.AddWithValue("@p1", arrquestion.questionname)
InsertComm.Parameters.AddWithValue("@p2", arrquestion.question )
InsertComm.Parameters.AddWithValue("@p3", arrquestion.ans1)
InsertComm.Parameters.AddWithValue("@p4", arrquestion.ans2)
InsertComm.Parameters.AddWithValue("@p5", arrquestion.ans3)
InsertComm.Parameters.AddWithValue("@p6", arrquestion.ans4)
InsertComm.Parameters.AddWithValue("@p7", arrquestion.correctans)
InsertComm.Parameters.AddWithValue("@p8", arrquestion.difficulty)
InsertComm.ExecuteNonQuery()

As you can see, the first thing is to encapsulate every field name with square brackets to resolve the problem of spaces in field names. The second point is to use parametrized query to avoid parsing problems (quotes in strings, date, decimals etc..) and most important Sql Injection

Also note that the parameters in an OleDb environment should be added to the ParametersCollection in the same order in which their respective placeholders (?) appear in the sql text

Thank you very much! I'm assuming "@p1" would be replaced with QuestionName and so on? — Edd B, Jan 29 '13 at 20:29
Yes, as I have said the placeholders (the ? in the string) will be replaced by the value of the parameters added to the collection. Because OleDb doesn't support 'named parameters' like Sql it is important the adding order not the name of the parameter. — Steve, Jan 29 '13 at 20:44

VB: Syntax error when trying to insert to database with SQL

1 Answers1