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
– Bjoern, Commented Jan 7, 2015 at 18:52
@Bjoern, I declared Sql as String. it says, INSERT INTO dbase.tblgradesection ('grade1') VALUES ('section1'), thats the output. — Fvcundo
– Fvcundo, Commented Jan 7, 2015 at 18:57
Have a look at Marc Bs answer. Your field names should be quoted with backticks. — Bjoern
– Bjoern, Commented Jan 7, 2015 at 19:02

Marc B · Accepted Answer · 2015-01-07 18:53:44Z

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 7, 2015 at 18:53

Marc B

362k44 gold badges433 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Fvcundo Over a year ago

You're right. I'll keep that in mind. It worked. ThankU. Last question, how to parameterize it? Dont know what's that for.

user4202824 · Accepted Answer · 2015-01-07 18:58:28Z

0

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

answered Jan 7, 2015 at 18:58

user4202824

Collectives™ on Stack Overflow

Syntax Error in InsertStatement

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related