SQL query error :Incorrect Syntax Near "#"?

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 '#'
Jon Skeet
people
quotationmark

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

people

See more on this question at Stackoverflow