Syntax error in query

Question

With cmd
    .Connection = con
    .CommandTimeout = 0
    .CommandText = "INSERT INTO tableContacts (NameUser, Address, City, Phone, Fax, Note, Email) VALUES (@NameUser, @Address, @City, @Phone, @Fax, @Note, @Email)"
    With .Parameters
        .AddWithValue("@NameUser", txtName.Text)
        .AddWithValue("@Address", txtAddress.Text)
        .AddWithValue("@City", txtCity.Text)
        .AddWithValue("@Phone", txtPhone.Text)
        .AddWithValue("@Fax", txtFax)
        .AddWithValue("@Note", txtNote.Text)
        .AddWithValue("@Email", txtTo.Text)

    End With

    Try
        cmd.ExecuteNonQuery()
    Catch ex As Exception

    End Try
    .Dispose()
End With

Can't find where is the problem.

All the debugger says is:

{"Syntax error in INSERT INTO statement."}

To those who vote to close as a "typo question" and upvote corresponding comments: If it was about a missing/invalid parameter, the error would be different. — GSerg, Oct 09 '15 at 15:37
`Note` is a reserved word. You need to enclose it in square brackets `[Note]`. https://support.microsoft.com/en-us/kb/321266 — Bjørn-Roger Kringsjå, Oct 09 '15 at 15:39
@GSerg I voted to close it as a typo. We don't need a Q&A for each and every reserved word. If you know of a good canonical, please VTC as dupe. — Bjørn-Roger Kringsjå, Oct 09 '15 at 15:44
@Bjørn-RogerKringsjå Ok it makes sense if you VTC knowing it's about the keyword. The close vote appeared earlier than your comment so it looked like people are VTCing on the basis of the missing `@`. — GSerg, Oct 09 '15 at 15:46
GSerg your answer is helpful, Post it as answer so i can close this — Anel H., Oct 09 '15 at 15:58
@AnelH. That would be a wrong thing to do. Please click [the "That solved my problem!" button](http://meta.stackexchange.com/a/250930/147640). — GSerg, Oct 09 '15 at 16:00

score 0 · Answer 1 · answered Oct 09 '15 at 15:48

0

OleDbCommands don't support named parameters. You need to use question marks in your SQL statement and then just add the parameters in the correct order.

.CommandText = "INSERT INTO tableContacts (NameUser, Address, City, Phone, Fax, Note, Email) VALUES (?, ?, ?, ?, ?, ?, ?)"
With .Parameters
    .AddWithValue("?", txtName.Text)
    .AddWithValue("?", txtAddress.Text)
    .AddWithValue("?", txtCity.Text)
    .AddWithValue("?", txtPhone.Text)
    .AddWithValue("?", txtFax)
    .AddWithValue("?", txtNote.Text)
    .AddWithValue("?", txtTo.Text)
End With

answered Oct 09 '15 at 15:48

Martin Soles

534
3
8

1

While you are right that OleDb does not support named parameters, the OP's code would [work anyway](http://stackoverflow.com/q/1476770/11683). This is not the source of the problem. – GSerg Oct 09 '15 at 15:53
You do get a syntax error when your statement uses @ as a parameter prefix in the SQL statement. Using reserved words will also do that. – Martin Soles Oct 09 '15 at 15:58
That is only true if you actually manually send the `@` in the query text. When you have a command object that was constructed with `@`, the `@` will not necessarily reach the database. – GSerg Oct 09 '15 at 16:05

Syntax error in query

1 Answers1