Syntax Error in InsertStatement

Question

Im inserting a data in MySql database and when I click the save button, it says,

You have an error in sql syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near "grade1") VALUES('section1')' at line 1"

I copied it from other insert statement that works fine, but in the form that contains combobox and a textbox only, it gives me that kind of error. They also want me to learn how to paramerized it so Im free from SQL injection they say. Any help is appreciated. Here's the sample codes:

conn = New MySqlConnection
conn.ConnectionString = "server=localhost; userid=root; password=root; database=dbase"

Try

    conn.Open()
    Sql = "INSERT INTO dbase.tblgrade_section ('" & cbGradeLvl.SelectedItem & "') VALUES('" & txtNewSec.Text & "') "
    cmd = New MySqlCommand(Sql, conn)
    dr = cmd.ExecuteReader
    MsgBox("Data saved", vbInformation, "Saving Data")
    conn.Close()

Catch ex As Exception
    MsgBox(ex.Message)
Finally
    conn.Close()
End Try
rem End Sub

Please output your variable `Sql` and show us how its content looks like. — Bjoern, Jan 07 '15 at 18:52
@Bjoern, I declared Sql as String. it says, INSERT INTO dbase.tblgradesection ('grade1') VALUES ('section1'), thats the output. — Fvcundo, Jan 07 '15 at 18:57
Have a look at Marc Bs answer. Your field names should be quoted with backticks. — Bjoern, Jan 07 '15 at 19:02

score 2 · Accepted Answer · answered Jan 07 '15 at 18:53

2

Basic MySQL 101: Fields/Table names cannot be quoted with anything other than backticks. You've used ', which turns your field name into a string literal. ONce it's a string literal, it's no longer a table/field name:

Sql = "INSERT INTO dbase.tblgrade_section (`" & cbGradeLvl.Selected ... "`...."
                                           ^-----------------------------^---

note the indicated changes.

answered Jan 07 '15 at 18:53

Marc B

356,200
43
426
500

You're right. I'll keep that in mind. It worked. ThankU. Last question, how to parameterize it? Dont know what's that for. – Fvcundo Jan 07 '15 at 19:04

score 0 · Answer 2 · answered Jan 07 '15 at 18:58

0

You can't put quotes around your column names. Use a prepared statement to "guard" against sql injections.

answered Jan 07 '15 at 18:58

Donald Winston

57
1
6

Syntax Error in InsertStatement

2 Answers2