Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > Using a SQL statement with variable parameters and variable criteria

Reply
Thread Tools

Using a SQL statement with variable parameters and variable criteria

 
 
Froefel
Guest
Posts: n/a
 
      07-04-2007
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

 
Reply With Quote
 
 
 
 
Mark Rae [MVP]
Guest
Posts: n/a
 
      07-04-2007
"Froefel" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed) 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

 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Passing variable to SQL statement when using psycopg2 Julia Jacobson Python 2 08-30-2010 08:22 AM
ASP Syntax using Variable for DropDownList1 as criteria in Pervasive SQL query bryant@4dnssupport.com ASP .Net 7 05-06-2007 02:35 PM
Hard coded vs. variable assignment using in SQL statement. Jack Perl Misc 10 12-29-2004 06:25 AM
Error in dynamic sql: Data type mismatch in criteria Jack ASP General 7 12-28-2004 04:09 AM
asp and sql statement in sql server db weiwei ASP General 3 09-22-2004 04:12 PM



Advertisments