468,133 Members | 1,184 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,133 developers. It's quick & easy.

Using a SQL statement with variable parameters and variable criteria

I'm trying to populate a gridview control based on a user's search
criteria, but what seemed like a trivial task turns out to be a
nighmare for this newbie... maybe someone can help.

Here's the (simplified) business requirement:
1. Allow the user to specify search criteria (country, action, status)
2. Display results of the search in a gridview, after the using clicks
the search button.
3. Allow paging and sorting on the gridview
4. Some of the search criteria can have multiple values (e.g. country
IN (8, 12, 54, 7)
5. Each search criteria is optional by using a "catch-all" value.

For the criteria I created DropDownList and Listbox controls. The
ListBox controls are set with MiltiSelect=True. The items in these
controls could be something like "[All countries]; Afghanistan;
Bangladesh; Belgium; Croatia", with corresponding values of "-1; 1; 3;
8; 15".
Note that I created the data sources for all of these controls such
that the "catch-all" always has a value of -1.

I tried creating the GridView control using a SqlDataSource, but I'm
not having much luck with that approach because 1) I don't know how to
handle the multiple values from ListBoxes and 2) I don't know how to
use a dynamically generated SelectCommand that only includes criteria
that don't have the "catch-all" value selected.
At the end of this post I've included a few sample statements.

I ventured in the direction of using an ObjectDataSource, but it seems
overkill to create an object model for this simple application,
especially since it only needs Select functionality (no updates or
deletes).

If anyone can shed some light on the direction I should be going, or
point me to some good examples, I would greatly appreciate it.

------------
Sample SQL statements of what I would expect to achieve, based on the
criteria provided by the user:
- criteria: country = [All countries], action=1, status=1
--"SELECT customer FROM customers WHERE action=1 AND status=1"

- criteria: country = 5;8;12, action=[Any action], status=[Any status]
--"SELECT customer FROM customers WHERE country IN(5,8,12)"

-- Hans

Jul 4 '07 #1
1 1712
"Froefel" <ha************@gmail.comwrote in message
news:11**********************@q75g2000hsh.googlegr oups.com...
If anyone can shed some light on the direction I should be going, or
point me to some good examples, I would greatly appreciate it.
1) Forget the DataSource controls - I never use them precisely for their
limitations that you are currently experiencing

2) Use a DAL (Data Access Layer) - a good idea would be to base yours on the
Microsoft DAAB:
http://www.microsoft.com/downloads/d...displaylang=en
http://aspnet.4guysfromrolla.com/articles/070203-1.aspx

Once you have a DAL in place, you really will wonder how you ever managed
without it :-) You simply drop it into a project, set the connection string
(probably in web.config) and you're good to go.

3) Use a paremterised query or a stored procedure to fetch your data -
dynamic SQL in the way that you are doing it is very dangerous, especially
in a web-based application, because of SQL injection:
http://www.google.co.uk/search?sourc...L+Injection%22
--
Mark Rae
ASP.NET MVP
http://www.markrae.net

Jul 4 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Colleyville Alan | last post: by
1 post views Thread by owengoodhew | last post: by
1 post views Thread by gcdp | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.