SQL query error :Incorrect Syntax Near "#"?

Question

When I try to execute this SQL query:

savInto.CommandText = "update onCommands set warnDate =#" & movDate.Value.ToString("MM/dd/yyyy") 
& "#,updateDate =#" & Date.Now.ToShortDateString 
& "#,transportCompany ='" & Trim(Company.Text) 
& "' where ID =" & moveID

I get this error:

Incorrect syntax near '#'

Can we see the actual sql? (after all the parsing to strings) — Yosi Dahari
– Yosi Dahari, Commented Nov 29, 2013 at 13:37
Use sql-parameters instead of string concatenation if you build a sql query. Otherwise you are open for sql-injection and localization issues. — Tim Schmelter
– Tim Schmelter, Commented Nov 29, 2013 at 13:37
Print out your entire string to a log so that you can see exactly what is being executed. This may give you a clue as to the syntax error — Mike
– Mike, Commented Nov 29, 2013 at 13:37
Thank you a lot, my problem was solved by the first answer :) — AymAn AbuOmar
– AymAn AbuOmar, Commented Nov 29, 2013 at 13:48

Jon Skeet · Accepted Answer · 2013-11-29 13:36:59Z

4

Well the main problem is that you're trying to provide the parameter as part of the SQL itself. While there are ways of doing that (use an apostrophe rather than #), it's generally a bad idea:

It invites SQL injection attacks when used with arbitrary strings
It makes it harder to read the code
It introduces unnecessary string conversions

Instead, you should use parameterized SQL and specify the value for the parameter. Something like:

savInto.CommandText = "update onCommands set warnDate = @warnDate" & 
    ", updateDate = @updateDate, transportCompany = @transportCompany" &
    " where ID=@moveID"
savInto.Parameters.Add("@warnDate", SqlDbType.DateTime).Value = movDate
savInto.Parameters.Add("@updateDate", SqlDbType.DateTime).Value = Date.Now
savInto.Parameters.Add("@transportCompany", SqlDbType.NVarChar).Value = Trim(Company.Text)
savInto.Parameters.Add("@moveID", SqlDbType.NVarChar).Value = moveID

answered Nov 29, 2013 at 13:36

Jon Skeet

1.5m893 gold badges9.3k silver badges9.3k bronze badges

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

7 Comments

Lasse V. Karlsen Over a year ago

Additionally, the #date# syntax, isn't that Access / JET?

user247702 Over a year ago

@LasseV.Karlsen I believe that's from Access indeed.

Jon Skeet Over a year ago

@LasseV.Karlsen: Possibly. I'm not going to dwell on that, given that it's a fundamentally bad approach :)

Jon Skeet Over a year ago

@TimSchmelter: Well it's not a literal when it's getting the value from a variable...

Jon Skeet Over a year ago

@AymAnAbuOmar: Maybe the SQL date literal is expected to be in a different format, e.g. yyyy-MM-dd. I don't know, and you shouldn't need to care, because you're not really trying to perform a string conversion: you're trying to provide a date to the database. That's the beauty of using parameterized SQL.

|

nestavazquez · Accepted Answer · 2013-11-29 13:38:39Z

1

Why are you using # character? What database are you using (sql server, oracle, mysql...)

Try this:

savInto.CommandText = "update onCommands set warnDate ='" & movDate.Value.ToString("MM/dd/yyyy") & "',updateDate ='" & Date.Now.ToShortDateString & "',transportCompany ='" & Trim(Company.Text) & "' where ID =" & moveID

answered Nov 29, 2013 at 13:38

nestavazquez

435 bronze badges

1 Comment

user247702 Over a year ago

While your answer is technically correct, it's not a good approach in general.

Collectives™ on Stack Overflow

SQL query error :Incorrect Syntax Near "#"?

2 Answers 2

7 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related