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
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
You simply drop it into a project, set the connection string