Passing date variables in SELECT statement

[FreeriderUK]

I've been struggling with this for a few days, and now have to ask for help.

I am trying to pass a date variable (taken from a DateTimePicker) in a SELECT statement.

Dim FromDate As Date = FromDateDTP.Value
Dim command1 As New SqlCommand
command1.Connection = sqlConn
command1.CommandText =  "SELECT * From DateTable WHERE CompletedDate >'"  & FromDate & "'"

The result shows ALL rows instead of > FromDate
Any advice on how to achieve this?

 

[giadich]

troubleshooting tips:

1. hardcode the date in your query
2. exam the data in the table to make sure you have the right data (to be included, to be excluded...)
3. exam the value in the variable
4. run query manually to verify that you can get proper result to eliminate possible error on the backend
5. check the format/datatype of CompleteDate

I don't see any problem with the query except the data in FromDate and the data/datatype in CompleteDate

 

[FreeriderUK]

Thanks for the suggestions, giadich.

I hard coded the date to make sure I was getting the correct result - this works OK.

I'm sure this isn't the correct way to do it, but using:

FromDate = FromDateDTP.Value.ToString("yyyyMMdd")

does work...

 

[JohnH]

You have to use parameters:

command1.CommandText = "SELECT * From DateTable WHERE CompletedDate > @fromdate"
command1.Parameters.AddWithValue("@fromdate", FromDateDTP.Value)

 

[FreeriderUK]

Brilliant!

Thanks JohnH, thats how I *wanted* to do it, but when I tried, I got a message saying I needed to declare the scalar variable - so I tried something else.

I didn't understand how to about the "command1.Parameters.AddWithValue..."
Now it makes sense.

 

[cjard]

I think AddWithValue looks at the type of the variable you supply and guesses.. FOr some situations it may not work (blobs/clobs?) but you can specify it directly for any type of variable:

cmd.Parameters.Add("name here", SqlDbType.Blob)
cmd.Parameters("name here").Direction = Input
cmd.Parameters("name here").Value = 'whatever value..

cmd.Parameters.Add("name here", SqlDbType.DateTime)
cmd.Parameters("name here").Direction = Input
cmd.Parameters("name here").Value = myDatePicker.Value


Quite often people forget to set the value of their parameter vefore they run the query