I have textbox1, textbox2 and textbox3. I have a GridView using a SqlDataSource with the following SQL statement:
SELECT * FROM TABLE
WHERE column1 = @textbox1
OR column2 = @textbox2
OR column3 = @textbox3

Putting values into all three textboxes returns the results as expected. If I leave one or two of the text boxes blank, the gridview shows no results...

How can I show results without requiring values in all textboxes? (When I do a Test Query in VisualStudio it returns values without requiring values for all fields)

I think this should work as well:

SELECT * FROM TABLE
WHERE column1 = @textbox1 OR @textbox1 IS NULL
WHERE column2 = @textbox2 OR @textbox2 IS NULL
WHERE column3 = @textbox3 OR @textbox3 IS NULL

This way, you compare the value of the @textbox3 parameter with null. This works in stored procedures; I am not sure about in-line SQL statements though...

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.

The values in the database table are not null. The SQL statement you offer says WHERE...OR...WHERE...OR. I get an error "near WHERE" when I do that. I changed the extra WHERE's to OR's and I still have the same problem.

Please understand if any of the textboxes on the aspx page are blank, no results are returned.

Hi there,

Sorry about the WHERE clause. Copy and paste issue.

It doesn't matter wether your columns contain null or not. The fix I suggested doesn't look at the columns, it sees if the actual parameters are null, which is the case when the text boxes are empty. Notice the syntax compares @textbox1 with null, not column1.

However, I left one thing out: you need to add CancelSelectOnNullParameter="False" to the SqlDataSource.

Otherwise, the SqlDataSource won't fire its query when one of the boxes is empty.

HtH,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.

The CancelSelectOnNullParameter is what I needed.

Thank you very much! (=