Gridview filter on empty asp:textbox input

Posted on 2010-01-04
Last Modified: 2013-02-27
I have a SQL command that filters the output on a GridView.
SELECT * FROM [ProductIncluded] WHERE (ISNULL(@GFMerchantProductName2,'') = '' OR [GFMerchantProductName] LIKE '%' + @GFMerchantProductName2 + '%')

I want to show all the records in the gridview when the ASP:textbox (passing control variable @GFMerchantProductName2) is empty.

Therefor I have now put in the ISNULL statement. This does the trick for if I test the SQL statement alone, however if I test in the ASP form it returns no records. This implies (but may not be the case) that the empty textbox is returning some empty data rater than a null. I have ensured that the control parameter has ConvertEmptyStringToNull Set as True and DefaultValue=""

How can I get the gridview to show all records if the textbox is empty. (i.e set  @GFMerchantProductName2 or add isBlank(@GFMerchantProductName2 ) type statement to the SQL.
Question by:Simon Cripps

    Accepted Solution

    For some strange reason it now works with ConvertEmptyStringToNull="false", hey ho. Thought I leave the question here as it may be of use in the future
    LVL 6

    Expert Comment

    In the SQLDataSource,  if one of the parameters is null reference when you execute a Select command, no data will be returned and no exception will be thrown. You can change this behavior by setting the CancelSelectOnNullParameter property to false.  

    You have got around this by making sure the parameter does not become NULL, which has also worked.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
    Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    734 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

    21 Experts available now in Live!

    Get 1:1 Help Now