null parameter in asp or ado command object

Question

I have a form in asp with two text boxes. If user enters a value in it and submit, it displays data from MS access query for the selected dates by user. If users just leave both text boxes blanks, I want to display complete output from the database.
This is my sample query in access :

select column_date, field1, field2, sum(field3) from table1
where field1 like '*xyz' and 
column_date between [@startdate] and [@enddate]
group by column_date, field1, field2

My asp codes are similar to below :

objCmd.CommandText = "Query"
objCmd.CommandType = adCmdStoredProc
Set objParam = objCmd.CreateParameter("@startdate" , adInteger, adParamInput, 0, 0)
objCmd.Parameters.Append objParam
Set objParam = objCmd.CreateParameter("@enddate" , adInteger, adParamInput, 0, 0)
objCmd.Parameters.Append objParam
if request.form ("startdate") = "" Then
objCmd.Parameters ("@startdate") = 1
Else
objCmd.Parameters("@startdate") = request.form("startdate")
objCmd.Parameters("@enddate") = request.form("enddate")
End if
if request.form ("enddate") = "" Then
objCmd.Parameters ("@enddate") = 31
Else
objCmd.Parameters("@startdate") = request.form("startdate")
objCmd.Parameters("@enddate") = request.form("enddate")
End if
................

Please note my startdate and enddates are text datatype with just numbers e.g. 1, 2, 3, 4, 5 ( 1 means 1st July 2012, 2 means 2nd July 2012)

I have two text boxes name "startdate" and "enddate". When user enters dates in the boxes, it returns data between the two dates from the query. If user leaves blank, it shows error.

But I want to make sure if user leave both text boxes blank, it returns all values from the query. If user input a single value in any of the two text boxes, it should return data only for that date.

I'm not sure how can I achieve it.

David W David W · Accepted Answer · 2012-07-13T20:31:25

One problem you have is that you are calling CreateParameter for both @startdate and @enddate but specifying adInteger as the input type, when I believe it should be adDate.

For your value problem, one approach is to check the values for startdate and enddate on your form before you submit the query. If startdate is blank, you could default it to a "magic" date such as "01/01/1900" (or whatever might represent the earliest date likely to be represented in this field of your application), with a similar "magic" default date for enddate representing the latest likely date to represent. That way, if the user enters nothing for either value, you'll still return some results. You might also add some validation to ensure the textboxes used to capture the date values hold either blank values or a valid date.

An example of how you would implement a default date might look like this:

if request.form("enddate")="" then
    objCmd.Parameters("@enddate")="12/31/2099"
else
    objCmd.Parameters("@enddate")=request.form("enddate")
end if

There are other ways you could overcome this problem; this is merely one (untested) example, intended as a push in the right direction.

Hope that helps.

null parameter in asp or ado command object

2 Answers