Date Range Error with SQL Server DB

[DH]

I have 2 input text boxes for users to enter a start date and end date to query the SQL database.

Here is the code....

Else
Dim searchdatestart
Dim searchdateend
searchdatestart = Session("searchdatestartquote")
searchdateend = Session("searchdateendquote")
cmdSelect = New SqlCommand("Select ID, dpurpose, tcontactname, timestamp, submitdate From Quotes WHERE submitdate BETWEEN ' & '#' & searchdatestart & '#' & ' And ' & '#' & searchdateend & '#' & ' Order By '" & strSortField, SqlConnection1)
End If
SqlConnection1.Open()
DataGrid1.DataSource = cmdSelect.ExecuteReader()
DataGrid1.DataBind()
SqlConnection1.Close()

Here is the error:

Message: Exception of type System.Web.HttpUnhandledException was thrown.

Source: System.Web

TargetSite: HandleError

InnerException: Cast from string "9/1/2004" to type 'Long' is not valid.

Stack Trace: at System.Web.UI.Page.HandleError(Exception e)
at System.Web.UI.Page.ProcessRequestMain()
at System.Web.UI.Page.ProcessRequest()
at System.Web.UI.Page.ProcessRequest(HttpContext context)
at System.Web.CallHandlerExecutionStep.System.Web.HttpApplication+IExecutionStep.Execute()
at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)

Any suggestions on how to fix??

Thanks,

DH

 

[dragonwell]

Your variables must be Typed.

  Dim searchdatestart [b]As DateTime[/b]
  Dim searchdateend [b] As DateTime [/b]

And when you get objects out of session, you need to cast them back to what they are

  searchdatestart = DirectCast(Session("searchdatestartquote"), DateTime)
  searchdateend = DirectCast(Session("searchdateendquote"), DateTime)

Use Option Explicit On and Option Strict On to make yourself do this.

Also, if using SqlServer, you don't need to surround the dates with # - just a single quote is all that's needed.

 

[DH]

Thanks, I'll give it a try...

DH

 

[chiph]

Right - If you don't have Option Explcit and Option Strict turned on in your code modules, you'll run into more of the same type errors, as well as strange behavior (like when you mispell a variable name). There's probably a VS.NET option to include them every time when you create a new .cls .frm, or .bas module.

Chip H.


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

 

[DH]

I turned on Option Explcit and Option Strict

Ok, here is here is the updated code:

Sub BindDataGrid(ByVal strSortField As String)
Dim cmdSelect As SqlCommand
If Session("searchdatestartquote") Is Nothing Then 'Or Session("searchdateendquote")
cmdSelect = New SqlCommand("Select ID, dpurpose, tcontactname, timestamp From Quotes Order By " & strSortField, SqlConnection1)
Else
Dim searchdatestart As DateTime
Dim searchdateend As DateTime
searchdatestart = DirectCast(Session("searchdatestartquote"), DateTime)
searchdateend = DirectCast(Session("searchdateendquote"), DateTime)
cmdSelect = New SqlCommand("Select ID, dpurpose, tcontactname, timestamp, submitdate From Quotes WHERE submitdate BETWEEN ' & searchdatestart & ' And ' & searchdateend & ' Order By '" & strSortField, SqlConnection1)
End If
SqlConnection1.Open()
DataGrid1.DataSource = cmdSelect.ExecuteReader()
DataGrid1.DataBind()
SqlConnection1.Close()
End Sub

Now receiving the following error:

Message: Exception of type System.Web.HttpUnhandledException was thrown.

Source: System.Web

TargetSite: HandleError

InnerException: Specified cast is not valid.

Stack Trace: at System.Web.UI.Page.HandleError(Exception e)
at System.Web.UI.Page.ProcessRequestMain()
at System.Web.UI.Page.ProcessRequest()
at System.Web.UI.Page.ProcessRequest(HttpContext context)
at System.Web.CallHandlerExecutionStep.System.Web.HttpApplication+IExecutionStep.Execute()
at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)

Any suggestions? This is driving me nuts...

DH

 

[chiph]

What format is your datetime being passed from the ASP page/control? I think you mentioned 9/1/2004 in an earlier post. The datetime datatype may have a problem with that format, as it can't tell the difference between January 9th (European style dates) and September 1st (US style dates).

Are you using a date-time picker control on the web page? That should prevent this, as those controls use a DateTime as their Value property. If it's just a textbox, as has been mentioned here several times, you're in trouble, as users always seem to enter dates in the one style that you didn't plan on...

Chip H.


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

 

[DH]

I am using a 3rd party calendar control. Looks like the value property (posteddate) is a string datatype. The string seems to be causing the problem here.

Haven't had any success converting the string to a date or datetime datatype?? .Net doesn't allow or seem to like this conversion.

I'll keep working at it.

DH