Query syntax error on MySqlCommand

Question

I'm using MySqlCommand for perform query insert in my vb.net application, now I've this query:

UPDATE primo_appointments SET 
  book_datetime = @parameter1, 
  start_datetime = @parameter2, 
  end_datetime = @parameter3, 
  notes = @parameter4, 
  hash = @parameter5, 
  is_unavailable = @parameter6 
WHERE hash = xqA5jdsFBLPTrvy5kKHfgXBZdv9Hs2Ld 
  AND lastUpdated = 12-01-2016 15:53:47.3978486

when I do: .ExecuteNonQuery() this error appear:

you have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '15:53:47.3978486' at line 1.

What is wrong?

Matt Wilko · Accepted Answer · 2016-01-13 15:29:32Z

1

Your lastUpdated and hash is not enclosed in single quotes which is the cause of your error.

The fix for this is not to enclose it in quotes, but to use a parameter for these values as well as the others:

UPDATE primo_appointments SET 
  book_datetime = @parameter1, 
  start_datetime = @parameter2, 
  end_datetime = @parameter3, 
  notes = @parameter4, 
  hash = @parameter5, 
  is_unavailable = @parameter6 
WHERE hash = @oldHashString
  AND lastUpdated = @lastUpdatedDate

When you use a parameterised list, you don't have to remember whether a field needs to be surrounded by quotes or not - this is handled for you. It also protects you from SQL injection attacks.

edited Jan 13, 2016 at 15:29

answered Jan 13, 2016 at 15:20

Matt Wilko

27.4k10 gold badges99 silver badges146 bronze badges

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

2 Comments

Ňɏssa Pøngjǣrdenlarp Over a year ago

It is strange how many apparently think a parameter wont work on the WHERE clause

Sandokan Over a year ago

The same parameter if inserted as @param work, but if I execute the query without put Where parameter into the parametizer value won't work.

Gouda Elalfy · Accepted Answer · 2016-01-13 15:20:18Z

0

your varchar should between 2 ', you query should be:

UPDATE primo_appointments SET book_datetime = @parameter1, 
start_datetime = @parameter2, end_datetime = @parameter3, 
notes = @parameter4, hash = @parameter5, is_unavailable = @parameter6 
WHERE hash = 'xqA5jdsFBLPTrvy5kKHfgXBZdv9Hs2Ld' 
AND lastUpdated = '12-01-2016 15:53:47.3978486'

answered Jan 13, 2016 at 15:20

Gouda Elalfy

7,0833 gold badges28 silver badges38 bronze badges

Collectives™ on Stack Overflow

Query syntax error on MySqlCommand

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related