[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 711
  • Last Modified:

Gridview filter on empty asp:textbox input

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.
Simon Cripps
Simon Cripps
1 Solution
Simon CrippsAuthor Commented:
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
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now