Wrox Programmer Forums
Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP.NET 2.0 > ASP.NET 2.0 Professional
| Search | Today's Posts | Mark Forums Read
ASP.NET 2.0 Professional If you are an experienced ASP.NET programmer, this is the forum for your 2.0 questions. Please also see the Visual Web Developer 2005 forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ASP.NET 2.0 Professional section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old September 25th, 2006, 06:11 PM
Authorized User
 
Join Date: Nov 2005
Location: , , .
Posts: 51
Thanks: 0
Thanked 0 Times in 0 Posts
Default Gridview/SqlDataSource won't show results

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)
  #2 (permalink)  
Old September 26th, 2006, 01:44 PM
Imar's Avatar
Wrox Author
Points: 70,322, Level: 100
Points: 70,322, Level: 100 Points: 70,322, Level: 100 Points: 70,322, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

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.
  #3 (permalink)  
Old September 26th, 2006, 10:14 PM
Authorized User
 
Join Date: Nov 2005
Location: , , .
Posts: 51
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
  #4 (permalink)  
Old September 27th, 2006, 12:46 AM
Imar's Avatar
Wrox Author
Points: 70,322, Level: 100
Points: 70,322, Level: 100 Points: 70,322, Level: 100 Points: 70,322, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

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.
  #5 (permalink)  
Old September 27th, 2006, 02:35 PM
Authorized User
 
Join Date: Nov 2005
Location: , , .
Posts: 51
Thanks: 0
Thanked 0 Times in 0 Posts
Default

The CancelSelectOnNullParameter is what I needed.

Thank you very much! (=


Similar Threads
Thread Thread Starter Forum Replies Last Post
conn from Gridview to SqldataSource veda Visual Studio 2005 2 July 9th, 2008 11:49 PM
Fill gridview from 2 tables using sqldatasource niktos ASP.NET 2.0 Basics 1 January 30th, 2007 10:45 AM
GridView and SqlDataSource Woes machone3720 ASP.NET 2.0 Basics 1 January 16th, 2007 02:11 PM
show results of SQL in txtbox Loralee Access 3 January 4th, 2007 11:40 AM
Gridview won't show results fizzerchris ASP.NET 2.0 Basics 0 September 22nd, 2006 06:20 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.