Ok, so this is a difficult question. It is rather easy to filter, and have a cumulative filter.

So, say we have this screen:

And lots more rows.

So, I can say lets filter by a city.

So this:

Note how we do allow multiple city in the multi-select drop down.

So, I now have this:

Now, lets select those ONLY with a description.

So this:

And then say only active ones. So, this:

So, above is quite easy to setup. Note how any option NOT selected is left out of the critera.

but, a BIG problem exists in the above.

What happens if I want Active from say B.C. but NOT active from Alberta???

I can’t do that, and hence your problem.

What we could do however is add a button to above to SAVE the resulting filter, and put the "list" of filters say into a list box or collection.

we then have a search button to search on our collection of filters.

Let me see if this can work – I’ll add to above a "box" or collection of each filter.

I would think a union query with distinct row for each filter would do the trick.

So, above example is not too hard – a "cumulative" filter. In fact, the code patter for 2 or 15 filters is quite easy to do here.

However, adding up separate filter requests and combine them? That is somewhat difficult to do.

Edit: Multiple filters

so, while in above, I could say filter by city and get all active, but THEN I want to filter by another city, and get all NON active!!!

That’s the problem here.

So, we would have to add code to SAVE the filter. And the HUGE problem with that is how then do we save each filter to "add up" each filter set we want?

We could try and save the raw SQL, but such SQL would be subject to sql injection, and we want to always use parameters.

So, we can and could adopt a design in which we SAVE the resulting SqlCommand object. And then merge the results.

So, now our UI becomes like this:

Lets grab and filter all those from city Edmonton, but Active,
so, this:

We now hit save filter and this:

And now we filter by say City = Banff, but don’t care about active or not.

So we have this:

We then save that filter – and now we have this:

I now hit the filter button below the list of filters, and we get this:

So, how does this code work?

Well, I simple saved the Sqlcommand object to a collection (list), and thus combine the results.

So, first our markup at the top for the filter stuff.

        <h4>Filters</h4>
        <div style="float:left">
            <asp:Label ID="Label1" runat="server" Text="Search Hotel"></asp:Label>
            <br />
             <asp:TextBox ID="txtHotel" runat="server"></asp:TextBox>
        </div>

        <div style="float:left;margin-left:20px">
            <asp:Label ID="Label2" runat="server" Text="Search City"></asp:Label>
            <br />
            <asp:TextBox ID="txtCity" runat="server"></asp:TextBox>
        </div>

        <div style="float:left;margin-left:20px">
            <asp:Label ID="Label3" runat="server" Text="Must Have Description"></asp:Label>
            <br />
            <asp:CheckBox ID="chkDescripiton" runat="server"  />
        </div>

        <div style="float:left;margin-left:20px">
            <asp:Label ID="Label4" runat="server" Text="Show only Active Hotels"></asp:Label>
            <br />
            <asp:CheckBox ID="chkActiveOnly" runat="server"  />
        </div>

        <div style="float:left;margin-left:20px">
            <asp:Button ID="cmdSearch" runat="server" Text="Search" CssClass="btn" OnClick="cmdSearch_Click"/>
        </div>

        <div style="float:left;margin-left:20px">
            <asp:Button ID="cmdClear" runat="server" Text="Clear Fitler" CssClass="btn" OnClick="cmdClear_Click"/>
        </div>

        <div style="float:left;margin-left:20px">
            <asp:Button ID="cmdTest" runat="server" Text="Save Filter" 
                CssClass="btn" OnClick="cmdTest_Click"
                OnClientClick="return myfilterprompt()"
                />
            <asp:HiddenField ID="HFilterName" runat="server" ClientIDMode="Static"/>
            <script>
                function myfilterprompt() {
                    sFilter = ""
                    sFilter = prompt('Enter name for filter ')
                    if ( (sFilter === null) || (sFilter === "") ){
                        return false
                    }
                    $('#HFilterName').val(sFilter)
                    return true
                }

            </script>
        </div>

        <div style="float:left;margin-left:30px;width:190px">
            <asp:ListBox ID="lstFilters" runat="server" Width="100%" Height="100px"
                DataTextField="sFilterName" >
            </asp:ListBox>
            <asp:Button ID="cmdMultiFilter" runat="server" Text="Filter" 
                CssClass="btn" OnClick="cmdMultiFilter_Click" style="float:left" />
            <asp:Button ID="cmdMultiClear" runat="server" Text="Clear" 
                CssClass="btn" OnClick="cmdMultiClear_Click" style="float:right"/>

        </div>

then below above is our grid:

        <asp:GridView ID="GridView1" runat="server" 
            AutoGenerateColumns="False" DataKeyNames="ID" 
            CssClass="table" Width="60%" ShowHeaderWhenEmpty="true">
            <Columns>
                <asp:BoundField DataField="FirstName" HeaderText="FirstName" />
                <asp:BoundField DataField="LastName" HeaderText="LastName"  />
                <asp:BoundField DataField="HotelName" HeaderText="HotelName" />
                <asp:BoundField DataField="City" HeaderText="City" />
                <asp:BoundField DataField="Province" HeaderText="Province" />
                <asp:BoundField DataField="Description" HeaderText="Description"  />
                <asp:BoundField DataField="Active" HeaderText="Active"  />
            </Columns>
        </asp:GridView>

So, code to load:

    List<MyFilter> MyFilters = new List<MyFilter>();
    protected void Page_Load(object sender, EventArgs e)
    {
        if (IsPostBack == false)
        {
            SqlCommand cmdSQL = new
                SqlCommand("SELECT * FROM tblHotels WHERE ID = 0");

            LoadGrid(cmdSQL);
            Session["MyFilters"] = MyFilters;

        }
        else
            MyFilters = (List<MyFilter>)Session["MyFilters"];
    }

    public void LoadGrid(SqlCommand cmdSQL)
    {
        DataTable rstData = MyRstP(cmdSQL);
        GridView1.DataSource = rstData;
        GridView1.DataBind();
    }

And now our search button:

    protected void cmdSearch_Click(object sender, EventArgs e)
    {
        SqlCommand cmdSQL = GetMyCommand();
        LoadGrid(cmdSQL);
    }

    SqlCommand GetMyCommand()
    {
        string strSQL = "SELECT * FROM tblHotels ";
        string strORDER = " ORDER BY HotelName";
        string strFilter = "";

        SqlCommand cmdSQL = new SqlCommand();

        if (txtHotel.Text != "")
        {
            strFilter = "(HotelName like @HotelName + '%')";
            cmdSQL.Parameters.Add("@HotelName", SqlDbType.NVarChar).Value = txtHotel.Text;
        }

        if (txtCity.Text != "")
        {
            if (strFilter != "") strFilter += " AND ";
            strFilter += "(City Like @City + '%') ";
            cmdSQL.Parameters.Add("@City", SqlDbType.NVarChar).Value = txtCity.Text;
        }

        if (chkActiveOnly.Checked)
        {
            if (strFilter != "") strFilter += " AND ";
            strFilter += "(Active = 1)";
        }
        if (chkDescripiton.Checked)
        {
            if (strFilter != "") strFilter += " AND ";
            strFilter += "(Description is not null)";
        }

        if (strFilter != "") strSQL += " WHERE " + strFilter;

        strSQL += strORDER;
        cmdSQL.CommandText = strSQL;

        return cmdSQL;
    }

And now our save the filter button code:

    protected void cmdTest_Click(object sender, EventArgs e)
    {
        MyFilter OneFilter = new MyFilter();

        OneFilter.sFilterName = HFilterName.Value;
        OneFilter.cmdSQL = GetMyCommand();

        MyFilters.Add(OneFilter);
        lstFilters.DataSource = MyFilters;
        lstFilters.DataBind();
    }

    public class MyFilter
    {
        public string sFilterName { get; set; }
        public SqlCommand cmdSQL = new SqlCommand();
    }

And our multi-filter code button.

Now, for large data sets – not a great idea, but a start:

    protected void cmdMultiFilter_Click(object sender, EventArgs e)
    {
        List<DataTable> MyTables = new List<DataTable>();
        foreach (MyFilter OneFilter in MyFilters)
        {
            DataTable rstDT = MyRstP(OneFilter.cmdSQL);
            MyTables.Add(rstDT);
        }

        DataTable rstData = MyTables[0];
        for (int i = 1;i < MyTables.Count;i++)
        {
            rstData.Merge(MyTables[i]);
        }

        GridView1.DataSource = rstData;
        GridView1.DataBind();
    }

so, you can build list up of "filters" and display them in a listbox and then have a filter button that merges all of the filtering.

And one more helper routine I used:

    public DataTable MyRstP(SqlCommand cmdSQL)
    {
        DataTable rstData = new DataTable();
        using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.TEST4))
        {
            using (cmdSQL)
            {
                cmdSQL.Connection = conn;
                conn.Open();
                rstData.Load(cmdSQL.ExecuteReader());
            }
        }
        return rstData;
    }

These systems can be really nice. Often a group of managers will say, lets grab all customers from west coast. Yuk – too many. Ok, only those with purchases in last 2 months – ah, that’s nice.

then they say, lets get all customers who never purchased anything, but from the south – and add those to the list – but only active on our mailing list.

So, this type of slice and dice – get some of those, and then get some of these, and then combine them?

This type of business query system and being able to combine these, and those, and them, and then toss in a few more? Often they will keep going say until such time they get say 10,000 results (which happens to be how many catalogs they have left they would like to send out).