Using a SQL statement with variable parameters and variable criteria

Discussion in 'ASP .Net' started by Froefel, Jul 4, 2007.

  1. Froefel

    Froefel Guest

    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
    Froefel, Jul 4, 2007
    #1
    1. Advertising

  2. "Froefel" <> wrote in message
    news:...

    > 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"


    --
    Mark Rae
    ASP.NET MVP
    http://www.markrae.net
    Mark Rae [MVP], Jul 4, 2007
    #2
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Replies:
    7
    Views:
    403
  2. LuckyBoy
    Replies:
    0
    Views:
    595
    LuckyBoy
    Aug 25, 2007
  3. LuckyBoy
    Replies:
    0
    Views:
    384
    LuckyBoy
    Sep 4, 2007
  4. jm
    Replies:
    3
    Views:
    250
  5. Jack
    Replies:
    7
    Views:
    211
Loading...

Share This Page