Syntax error with Date in sql query

Question

In my program I'm saving date from DateTimePicker into the global variable with My.Settings.date = dtpDate_do.Value.Date. I'm using this date to compare date from my database but I'm always getting syntax error, no matter what I'm changing. This is my query:

cmd.CommandText = "SELECT ID, order_date FROM orders WHERE order_date = " & My.Settings.date & " ORDER BY ID DESC"

Dates in my database are stored in EU format with dots - 17.2.2014. Can anyone provide me some help.

Syntax error is likely because there are no quotes around the date. — Anthony Chu, Feb 17 '14 at 06:28
what do you get in My.Settings.date ? As in the actual value at runtime when you debug? — Rohan, Feb 17 '14 at 06:29

score 1 · Accepted Answer · answered Feb 17 '14 at 06:33

1

Never ever create your query like that. Always and without any exception use parameters. This avoids both SQL-injection attacts and ensures proper formatting of your parameters.

Sorry for not knowing VB.NET, but it should be similar to this:

cmd.CommandText = "SELECT ID, order_date FROM orders WHERE order_date = @Date ORDER BY ID DESC" 
cmd.Parameters.AddWithValue("@Date", My.Settings.data)

Explanation: Create your query using @ParamName as a placeholder for your parameters. Then substitute your parameters with values. Make sure to either apply a concrete typed value (i.e. not an object) or/and supply the data type otherwise.

answered Feb 17 '14 at 06:33

alzaimar

4,572
1
16
30

Ty alzaimar. Your approach worked as planned. But now I'm getting another error on program startup. _COM object that has been separated from its underlying RCW cannot be used_. Do u have any idea what why is that happening?. – Dave Feb 17 '14 at 07:22
I've tried with local variables but there is still this error. Maybe is it caused somewhere in code ahead. Dunno. – Dave Feb 17 '14 at 08:06
Check this: http://stackoverflow.com/questions/2260990/com-object-that-has-been-separated-from-its-underlying-rcw-cannot-be-used – alzaimar Feb 17 '14 at 09:10

score 0 · Answer 2 · edited Feb 17 '14 at 06:34

0

Try to use a parameter in the query like this:

cmd.CommandText = "SELECT ID, order_date FROM orders WHERE order_date = @date ORDER BY ID DESC";
cmd.Parameters.Add(new SqlParameter("@date", dateTimePicker.Value.Date));

edited Feb 17 '14 at 06:34

alzaimar

4,572
1
16
30

answered Feb 17 '14 at 06:31

Vasile

134
2

Syntax error with Date in sql query

2 Answers2