"Syntax error in INSERT INTO statement" error - What is the error?

Question

I'm not sure where this error can be coming from.

I'm using VB.NET to insert data into MS Access 2016. I'm able to execute other SQL queries no problem, but this one returns the Syntax error in INSERT INTO statement error:

sql = "INSERT INTO Sense_Translation (SenseFK, LanguageCode, Translation) VALUES (?, ?, ?)"

cmd = New OleDbCommand(sql, myConnection)
cmd.Parameters.Add(New OleDbParameter("SenseFK", senseID))
cmd.Parameters.Add(New OleDbParameter("LanguageCode", lcodeID))
cmd.Parameters.Add(New OleDbParameter("Translation", tr.Translation))

Try
    cmd.ExecuteNonQuery()
    cmd.Dispose()
Catch ex As Exception
    MsgBox(ex.Message)
End Try

I've checked and double checked that the table name in Access and VB.NET match, that the parameters match, and that the parameters are in the correct order corresponding to the Access table, both in the SQL statement, and in the Parameters.Add statements (I'm not even sure if the latter matters, I just do it just in case.)

I've also checked that the types being passed to the parameters match - senseID is a number, lcodeID is a number, and tr.Translation is a string, which matches the table. Although if it was a type mismatch I expect the error would be different anyway.

Just for comparison, when I executed the code below, this did not return an error:

sql = "INSERT INTO Sense (EntryFK, SenseInformation) VALUES (?, ?)"
cmd = New OleDbCommand(sql, myConnection)

cmd.Parameters.Add(New OleDbParameter("EntryFK", entryID))
cmd.Parameters.Add(New OleDbParameter("SenseInformation", ss.SenseInformation))

Try
    cmd.ExecuteNonQuery()
    cmd.Dispose()
Catch ex As Exception
    MsgBox(ex.Message)
End Try

And as far as I can tell there is no difference between the two SQL statements except for the number of parameters, which shouldn't matter.

Is there something kickself-obvious that I've missed here, or is the problem likely to be more insidious?

Did u try to run these statements manually? Do one thing copy the same statements and run both with manual values in Sql first — Himanshu
– Himanshu, Commented Dec 3, 2019 at 12:31

Erik A · Accepted Answer · 2019-12-03 12:31:34Z

5

Translation is an SQL reserved word, as found here. Thus, you need to bracket it:

sql = "INSERT INTO Sense_Translation (SenseFK, LanguageCode, [Translation]) VALUES (?, ?, ?)"

It's good practice to always bracket all names, to avoid accidentally using a reserved word

sql = "INSERT INTO [Sense_Translation] ([SenseFK], [LanguageCode], [Translation]) VALUES (?, ?, ?)"

answered Dec 3, 2019 at 12:31

Erik A

32.8k13 gold badges48 silver badges74 bronze badges

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

Comments

Himanshu · Accepted Answer · 2019-12-03 12:36:37Z

1

Run the both statements in sql console. Syntax error just cant show you whole sql error but sometimes could be due to sql itself not the way it is written in high level language. Always check the query first in sql console then check at high language level in case of sql.

As @ErikA has already posted Translation is a Sql reserved keyword hence, running the same sql in sql console should have given you the exact reserved keyword error. Rename this column or enclose with [Translation]

answered Dec 3, 2019 at 12:36

Himanshu

3,9802 gold badges16 silver badges34 bronze badges

1 Comment

Lou Over a year ago

Thanks for this! @Erik A's answer solved my main problem, but I will take this step in future if the source of an error is unclear :)

Collectives™ on Stack Overflow

"Syntax error in INSERT INTO statement" error - What is the error?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related