|
Subject:
|
Gridview/SqlDataSource won't show results
|
|
Posted By:
|
fizzerchris
|
Post Date:
|
9/25/2006 6:11:29 PM
|
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)
|
|
Reply By:
|
Imar
|
Reply Date:
|
9/26/2006 1:44:16 PM
|
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.
|
|
Reply By:
|
fizzerchris
|
Reply Date:
|
9/26/2006 10:14:09 PM
|
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.
|
|
Reply By:
|
Imar
|
Reply Date:
|
9/27/2006 12:46:45 AM
|
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.
|
|
Reply By:
|
fizzerchris
|
Reply Date:
|
9/27/2006 2:35:56 PM
|
The CancelSelectOnNullParameter is what I needed.
Thank you very much! (=
|
