Using a SQL statement with variable parameters and variable criteria

F

Froefel

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
 
M

Mark Rae [MVP]

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/...0A-9877-4A7B-88EC-0426B48DF275&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?sour...rlz=1T4GGIH_en-GBGB220GB220&q="SQL+Injection"
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,733
Messages
2,569,440
Members
44,830
Latest member
ZADIva7383

Latest Threads

Top