Gridview With Optional Parametric Query

I got gridview and populate it with Name,Departman,Location information BY choosing LocationID=@locationID and Departman=@departmanID but this time I wanna make those parameters optional so someone can search by just use departmanid and locationid information. How can I do that is there anyway to use that kind of queries by the way I am using strongly typed Datasets and writing queries in my DAL layer.
Thanks you in advance
Who is Participating?
garavindbabuConnect With a Mentor Commented:
Use a stored procedure and set the optional values or depending on the option values in stored procedure excecute respective query.
I've done something like this using a SqlDataSource.  First step was to set the DataSource's CancelSelectOnNullParameter="false".   Then use SQL ISNULL function in the SelectCommand like this:
SELECT * FROM myTable WHERE LocationID=ISNULL(@locationID, locationID) AND departmentID = ISNULL(@departmentID, departmentID)
In the ControlParameter I set ConvertEmptyStringToNull="True"  
You'll have to modify this a little, but you should be able to pass a null parameter to the DAL, and have it convert the null to a DBNull for the query.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.