DateTimePicker to SQL Query Problems 1

[mushin]

If I execute the following query in the SQL-2000 Analyzer:

Select myDate from myTable where myDate = '11/03/2004'

I get appropriate results.


BUT,If I grab the date from a DateTimePicker on my vb.net form and execute the exact same query, I get Nothing....

The db column is a smalldatetime, (have tried datetime as well).

I have tried various combinations of output for the date,
and my .net query is identical to the one in the SQL Analyzer, visually.

I am fairly new to these products and do not see what the
big deal is here.

I have also tried concatenating the strings for time
"00:00:00" and "23:59:59" to the date with the between operator as suggested in another post.

Works in the Query tool but not in .Net !


What's the secret ? Should I just store the date as an
Integer converted from the date value and forget it ?

Thanks folks .........

 

[hayt]

can you post your code??? it's dificult to see where an error may lie. it cold be in the execution of the sql, or the string itself.

 

[mushin]

Here are the sections of relevant code that I am using:
Doesn't format too weel for this space, sorry...


This is the trigger logic for the DTP......

Private Sub DateTimePicker1_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DateTimePicker1.ValueChanged

Dim myCultureInfo As CultureInfo = New CultureInfo("en-US")
Dim myString As String = DateTimePicker1.Text
Dim myDateTime As DateTime = DateTime.Parse myString,myCultureInfo)
SelectedDate = myDateTime
'selectedDate is DateTime var
PopulateDay()
End Sub


This is the routine creating the query from the DTP....
If I hardcode the date like '11/04/2004', it is fine...


Private Sub PopulateDay()
Dim cnSQL As SqlConnection
Dim cmSQL As SqlCommand
Dim drSQL As SqlDataReader
Dim strSQL As String

Try
strSQL = "Select Description " & _
" from appointments where apptdate = " & SelectedDate.ToShortDateString

cnSQL = New SqlConnection(ConnectionString)
cnSQL.Open()
cmSQL = New SqlCommand(strSQL, cnSQL)
drSQL = cmSQL.ExecuteReader()

Do While drSQL.Read()
iStart = drSQL.Item("Start")
iEnd = drSQL.Item("EndTime")
cKeyId = Str(iEnd - iStart)
cText = drSQL.Item("Description").ToString()
load_appt()
Loop

drSQL.Close()
cnSQL.Close()
cmSQL.Dispose()
cnSQL.Dispose()

Catch e As SqlException
MsgBox(e.Message, MsgBoxStyle.Critical, "SQL Error")

Catch e As SqlException
MsgBox(e.Message, MsgBoxStyle.Critical, "General Error")
End Try

End Sub

 

[hayt]

first, the parse needs a parenthesis (i'm sure you forgot it when you typed it in here).

when u use sql, you have to add single qoutes around dates.

strSQL = "Select Description " & _
" from appointments where apptdate = '" & SelectedDate.ToShortDateString & "'"


try that, let me know if it works

 

[mushin]

Hi

Thank you

I had forgot to enclose the date in the query with ''...
That did the trick

K

 

[chiph]

I would use the Value property of the datetimepicker, rather than the text property. It will return you a DateTime object which ADO.NET Parameter objects know about natively. You also won't have any worries about mistaking January 2nd for February 1st.

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

 

[hayt]

glad to help