Hi everybody
On page i have text box txtDate. User writes date in dd.mm.yyyy format.

When the button btnSearch is clicked i have to query sqlserver 2000 database. Here is a code:

Private Sub btnSearch_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSearch.Click

    ...
    If Trim(txtDate.Text) <> "" Then
        Dim dCreatedFrom As Date
        Try
            dCreatedFrom = CDate(Trim(txtDate.Text))
        Catch ex As Exception
            Response.Redirect("GlobalError.aspx")
        End Try

        strSQL =" Select * from Orders Where OrderDate >= '" & Format(dCreatedFrom, "YYYY.MM.DD") & "'"
    End If
End Sub

When i return dataset with this strSQL i always get the error:
 Syntax error converting datetime from character string
How can i solve this
Thanks
Alex

This isn't the answer but I think this is more correct with your If Statement.

If Not Trim(txtDate.Text) Is Nothing Then
...
...

Or you can use a requiredfieldvalidator to make sure it contains a value.

Have your tried using the FormatDateTime Function?

Like this:

FormatDateTime(dCreatedFrom, DateFormat.ShortDate)

J

SQLServer 2000 looks for a date to be passed in as a string. You define the parameter as a datetime in the stored procedure but in your VB code you need to pass it as a string.


strSQL =" Select * from Orders Where OrderDate >= " + "' mm/dd/yyyy + '"

you can specify the date in any acceptable format yyyy/dd/mm ... etc .. . but it must be a string.