Gridview With Optional Parametric Query

Posted on 2008-11-18
Last Modified: 2012-05-05
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
Question by:mindcool
    LVL 2

    Accepted Solution

    Use a stored procedure and set the optional values or depending on the option values in stored procedure excecute respective query.
    LVL 1

    Expert Comment

    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.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    Lots of people ask this question on how to extend the “MembershipProvider” to make use of custom authentication like using existing database or make use of some other way of authentication. Many blogs show you how to extend the membership provider c…
    Sometimes in DotNetNuke module development you want to swap controls within the same module definition.  In doing this DNN (somewhat annoyingly) swaps the Skin and Container definitions to the default admin selections.  To get around this you need t…
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now