CODEWITHSUNDEEP
sqlvb.net
Dave
Dave

Reputation: 87

Syntax error with Date in sql query

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.

Upvotes: 1

Views: 1661

Answers (2)

Vasile
Vasile

Reputation: 134

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));

Upvotes: 0

alzaimar
alzaimar

Reputation: 4622

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.

Upvotes: 1

Related Questions