CODEWITHSUNDEEP
.netsqlsql-servervb.netdatetime
AymAn AbuOmar
AymAn AbuOmar

Reputation: 423

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 '#'

Upvotes: 1

Views: 1631

Answers (2)

nestavazquez
nestavazquez

Reputation: 43

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

Upvotes: 1

Jon Skeet
Jon Skeet

Reputation: 1499860

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

Upvotes: 4

Related Questions