How to return all records when date filter parameter is missing

I'm using an objectDataSource connected to a strongly typed dataset to populate a GridView. I want to be able to show all the records, or let the user to select only those records that expire in a certain month. The expire field is of type date

I'm used to all records being returned when a parameter is missing. If I have Select * from table where last=@.last, only the records where the last name is 'Smith' will be returned if @.last = 'Smith', but all records are returned is @.last = "". But that's not how it's working with the date.

I'm passing an integer from 1 to 12 in a querystring. I have the equivalent of

select * from table where (MONTH([AD ENDS]) = @.month)

MONTH(datefield) always returns an integer from 1 to 12. If @.month is empty, I want all the records to be displayed, but nothing is. If @.month is an int form 1 to 12, it works fine. How can I get all the records if no month is selected? Can I have two objectdatasources and programmatically select which one populates the gridview depending on if I want to filter the data or not?

Diane

HI Diane,

It think there are two options that might be better than having two objectdatasources.

1:change the select statement programmatically
strSQL = "select * from table ";
if (strMyMonth == string.empty)
{ strSQL+= "where (MONTH([AD ENDS]) = @.month)"}
else
{srSQL += "where (MONTH([AD ENDS]) > Date('01/01/0001')}

2:use the databases own If Then Else capabilities (this is dependent on your DB.. I know oracle, sqlserver and even access allows this)

Access Example: If [targetgoal] <> 0 then ([Total Of $FeeBilled]/6)*12)/[TargetGoal] else [targetgoal]= 0

|||I don't know how you can get all records without send anything in with Select * from table wherelast=@.last

But I would try to use Select * from table where last=ISNULL(@.last,last).

For the month query,please try something like this:

WHEREMONTH(tDate)=ISNULL(@.month,MONTH(tDate))

|||

Yup, those are much better options. I forgot about changing the select statement programmatically. Been at this too many hours I guess. I like the idea of using the database's capabilities though. If my current select statement is select * from table where (MONTH([AD ENDS]) = @.month) and I'm using SQL Server 2005, what would the new select statement be?

select * from table where ( If @.month <> 0 then (MONTH([AD ENDS]) = @.month) )?

or

select * from table ( If @.month <> 0 then (whereMONTH([AD ENDS]) = @.month) )?

Diane

|||SELECT * FROM Table WHERE MONTH([AD ENDS]) = ISNULL(@.month, MONTH([AD ENDS])|||

Thank you Dinakar! That worked!

Linmo, in a strongly typed dataset (which are pretty much all I know in .NET so far), if an empty parameter is returned, the filter for that parameter isn't set. This behavior is a life saver IMHO. It kust had me stumped this time around.

Diane