I'm getting syntax error in insert into statement

Question

Could anyone solve the below code plz....

NOTE: front end vb8 & back end access 7

Private Sub SaveData()
    Dim InsertString As String
    InsertString = "Insert into STUDENT DETAILS(SREGNO,SFIRSTNAME,SMIDDLENAME,SLASTNAME,SYEAR,SCOURSE,SSEM,SCLASS,SLANGUAGE)" & "Values('" & Me.TXTSREGNO.Text & "','" & Me.TXTSFIRSTNAME.Text & "','" & Me.TXTSMIDDLENAME.Text & "','" & Me.TXTSLASTNAME.Text & "','" & Me.COMB1SYEAR.Text & "','" & Me.COMB2SCOURSE.Text & "','" & Me.COMB3SSEM.Text & "','" & Me.COMB4SCLASS.Text & "','" & Me.COMB5SLANGUAGE.Text & " ');"
    Dim InsertCommand As New OleDbCommand(InsertString, Con)
    InsertCommand.ExecuteNonQuery()
    MsgBox("New record added successfully.", MsgBoxStyle.Information, "Record Added")
End Sub

1. Print the resulting string. 2. Compare to expected string. 3. Fix algoritm. 4. Goto 1. — dutt, Aug 19 '13 at 14:57
Also, consider using parameters, building all queries like that may allow a user enter quotes in your text fields and [wipe your database](http://xkcd.com/327/). — Joachim Isaksson, Aug 19 '13 at 14:59

score 3 · Answer 1 · edited May 23 '17 at 10:25

The error is the name of the table that contains a space and so you need to enclose it in square brackets

INSERT INTO [Student Details] ........

But you really need to know how to use a parameterized query.
This code is really an invitation for Sql Injections

This is how you could rework your code to use a parameterized query

Dim InsertString As String
InsertString = "Insert into [STUDENT DETAILS] "  & _
"(SREGNO,SFIRSTNAME,SMIDDLENAME,SLASTNAME,SYEAR,SCOURSE,SSEM,SCLASS,SLANGUAGE)" & _
"Values(?,?,?,?,?,?,?,?,?)"
Dim InsertCommand As New OleDbCommand(InsertString, Con)
InsertCommand.Parameters.AddWithValue("@p1", Me.TXTSREGNO.Text )
InsertCommand.Parameters.AddWithValue("@p2", Me.TXTSFIRSTNAME.Text )
InsertCommand.Parameters.AddWithValue("@p3", Me.TXTSMIDDLENAME.Text )
InsertCommand.Parameters.AddWithValue("@p4", Me.TXTSLASTNAME.Text )
InsertCommand.Parameters.AddWithValue("@p5", Me.COMB1SYEAR.Text )
InsertCommand.Parameters.AddWithValue("@p6", Me.COMB2SCOURSE.Text) 
InsertCommand.Parameters.AddWithValue("@p7", Me.COMB3SSEM.Text)
InsertCommand.Parameters.AddWithValue("@p8", Me.COMB4SCLASS.Text )
InsertCommand.Parameters.AddWithValue("@p9", Me.COMB5SLANGUAGE.Text)
InsertCommand.ExecuteNonQuery()

Yes, you have to write more code, but it is safer for Sql Injection and you will never hit syntax errors when one or more of your textbox fields happen to contain a single quote.

I'm getting syntax error in insert into statement

1 Answers1