I have a six check box lists to select products (two are shown below). All of the data resides in a table, so I iterate through all of the checked boxes to construct a SELECT statement which in turn lists my results regardless of which items are checked across the various check box lists. This works perfectly to fetch the results I want. Now I want to constrain the results in each of those check box lists based on selections in other check box lists.

For example – if you go to Zappos, and select Adidas from the “Brand” options, then the “Style”, “Color”, and “Materials” lists will all only show options available from Adidas. This is what I want to accomplish, but not in a hierarchy – so you don’t have to start with brand, you could start with any of the check box lists and the others will automatically restrict.

I feel like the best way to do that for me is to create a SELECT statement for each CBL, but I am completely stuck. Here is what I have so far:

<h3 id="brandheader" class="NavHeader">Brand</h3><!--end NavHeader-->
<div id="brandoptionsCBL" class="subnav">
    <asp:CheckBoxList id="CheckBoxListBrand" Cssclass="CBL" runat="server" AutoPostBack="True" DataSourceID="SqlMHIPlumbingVendor" DataTextField="Vendor" DataValueField="Vendor" RepeatColumns="1" OnSelectedIndexChanged="CheckBoxListBrand_SelectedIndexChanged"></asp:CheckBoxList>
    <asp:SqlDataSource runat="server" id="SqlMHIPlumbingVendor" ConnectionString="<%$ ConnectionStrings:DefaultConnection %>" SelectCommand="SELECT DISTINCT [Vendor] FROM [MHIplumbing] WHERE ([Vendor] <> @Vendor) ORDER BY [Vendor]">
    <SelectParameters>
        <asp:Parameter DefaultValue="0" Name="Vendor" Type="String" />
    </SelectParameters>        
    </asp:SqlDataSource>
</div><!--end brandoptionsCBL-->

<h3 id="collectionheader" class="NavHeader">Collection</h3><!--end NavHeader-->
<div id="collectionoptionsCBL" class="subnav">
    <asp:CheckBoxList id="CheckBoxListCollection" Cssclass="CBL" runat="server" AutoPostBack="True" DataSourceID="SqlMHIPlumbingCollection" DataTextField="Collection" DataValueField="Collection" RepeatColumns="1" OnSelectedIndexChanged="CheckBoxListCollection_SelectedIndexChanged"></asp:CheckBoxList>
<asp:SqlDataSource ID="SqlMHIPlumbingCollection" runat="server" ConnectionString="<%$ ConnectionStrings:DefaultConnection %>" OnSelected="SqlMHIPlumbingCollection_Selected" ></asp:SqlDataSource>
    

.

public partial class bath_plumbing : System.Web.UI.Page
{
    String selectit = "SELECT SKU, Collection, Vendor, Description, Note, DISC, Category, Product, SubCategory, Finish, Stock FROM MHIplumbing WHERE " ;
    String selectCBLCollection = "SELECT DISTINCT Collection FROM MHIplumbing WHERE ";
    protected void Page_Load(object sender, EventArgs e)
    {
        if (IsPostBack)
        {
            LabelReferrer.Text += " Is PostBack";
            String controlname = Page.Request.Params["__EVENTTARGET"];
            if (String.IsNullOrEmpty(controlname))
            {
                ProcessQueryString();
            }
        }
        if (!IsPostBack)
        {

        }
    }

    protected void Page_LoadComplete(object sender, EventArgs e)
    {

    }

    protected void CheckBoxListBrand_SelectedIndexChanged(object sender, EventArgs e)
    {
        LabBrand.Text = "";
        LabBrandCBL.Text = "";
        String whereis = " (MHIplumbing.Vendor = '";
        String changeCBLCollection = " (Vendor = '";

        CheckBoxList ckl = (CheckBoxList)sender;
        Boolean first = true;
        Boolean somechecked = false;
        selectionbrand.Text = "";

        foreach (ListItem li in ckl.Items)
        {
            if (li.Selected == true)
            {
                if (first)
                {
                    whereis += li.Value;
                    changeCBLCollection += li.Value;
                    first = false;
                }
                else
                {
                    whereis += "') OR (MHIplumbing.Vendor = '" + li.Value;
                    changeCBLCollection += "') OR (Vendor = '" + li.Value;
                }
                selectionbrand.Text += " " + li.Text;
                somechecked = true;
            }
        }
        whereis += "')";
        changeCBLCollection += "')";
        if (somechecked)
        {
            LabBrand.Text += whereis;
            LabBrandCBL.Text += changeCBLCollection;
        }

        Updatepage();
    }
protected void CheckBoxListBrand_Set()
{
    LabBrand.Text = "";
    LabBrandCBL.Text = "";
    String whereis = " (MHIplumbing.Vendor = '";
    String changeCBLCollection = " (Vendor = '";
    CheckBoxList ckl = CheckBoxListBrand;
    Boolean first = true;
    Boolean somechecked = false;
    foreach (ListItem li in ckl.Items)
    {
        if (li.Selected == true)
        {
            if (first)
            {
                whereis += li.Value;
                changeCBLCollection += li.Value;
                first = false;
            }
            else
            {
                whereis += "') OR (MHIplumbing.Vendor = '" + li.Value;
                changeCBLCollection += "') OR (Vendor = '" + li.Value;
            }
            somechecked = true;
        }
    }
    whereis += "')";
    changeCBLCollection += "')";
    if (somechecked)
    {
        LabBrand.Text += whereis;
        LabBrandCBL.Text += changeCBLCollection;
    }
    //Updatepage();
}

protected void CheckBoxListCollection_SelectedIndexChanged(object sender, EventArgs e)
{
    LabCollection.Text = "";
    String whereis = " (MHIplumbing.Collection = '";
    CheckBoxList ckl = (CheckBoxList)sender;
    Boolean first = true;
    Boolean somechecked = false;
    selectioncollection.Text = "";
    foreach (ListItem li in ckl.Items)
    {
        if (li.Selected == true)
        {
            if (first)
            {
                whereis += li.Value;
                first = false;
            }
            else
            {
                whereis += "') OR (MHIplumbing.Collection = '" + li.Value;
            }
            selectioncollection.Text += " " + li.Text;
            somechecked = true;
        }
    }
    whereis += "')";
    if (somechecked)
    {
        LabCollection.Text += whereis;
    }
    Updatepage();
}
protected void CheckBoxListCollection_Set()
{
    LabCollection.Text = "";
    String whereis = " (MHIplumbing.Collection = '";
    CheckBoxList ckl = CheckBoxListCollection;
    Boolean first = true;
    Boolean somechecked = false;
    foreach (ListItem li in ckl.Items)
    {
        if (li.Selected == true)
        {
            if (first)
            {
                whereis += li.Value;
                first = false;
            }
            else
            {
                whereis += "') OR (MHIplumbing.Collection = '" + li.Value;
            }
            somechecked = true;
        }
    }
    whereis += "')";
    if (somechecked)
    {
        LabCollection.Text += whereis;
    }
    //Updatepage();
}

protected void ListViewResults_ItemCreated(object sender, ListViewItemEventArgs e)
{

}
protected void SqlResults_Selected(object sender, SqlDataSourceStatusEventArgs e)
{
    LabelSelected.Text = e.AffectedRows.ToString();
}
protected void SqlMHIPlumbingCollection_Selected(object sender, SqlDataSourceStatusEventArgs e)
{

}


protected void Updatepage()
{
    plumbingsearchlabel.Visible = true;
    Boolean first = true;
    if (LabBrand.Text != "" || LabBrandCBL.Text != "" || LabCollection.Text != "")
    {
        SqlResults.SelectCommand = selectit;
        SqlMHIPlumbingCollection.SelectCommand = selectCBLCollection;
        if (LabBrand.Text != "")
        {
            if (first)
            {
                SqlResults.SelectCommand += LabBrand.Text;
                first = false;
            }
            else
            {
                SqlResults.SelectCommand += " AND " + LabBrand.Text;
            }
        }
        if (LabBrandCBL.Text != "")
        {
            if (first)
            {
                SqlMHIPlumbingCollection.SelectCommand += LabBrandCBL.Text;
                first = false;
            }
            else
            {
                SqlMHIPlumbingCollection.SelectCommand += " AND " + LabBrandCBL.Text;
            }
        }
        if (LabCollection.Text != "")
        {
            if (first)
            {
                SqlResults.SelectCommand += LabCollection.Text;
                first = false;
            }
            else
            {
                SqlResults.SelectCommand += " AND " + LabCollection.Text;
            }
        }

        SqlResults.SelectCommand += " ORDER BY MHIplumbing.SKU ";
        SqlMHIPlumbingCollection.SelectCommand += " ORDER BY Collection ";
    }
    else
    {
        SqlResults.SelectCommand = "";
        SqlMHIPlumbingCollection.SelectCommand = "";
    }
    LabSelect.Text = SqlResults.SelectCommand;
    LabCBLCollection.Text = SqlMHIPlumbingCollection.SelectCommand;
    ListViewResults.DataBind();
}

I’m getting “Incorrect syntax near the keyword ‘AND'” that I think is provoked here…

    if (LabBrandCBL.Text != "")
    {
        if (first)
        {
            SqlMHIPlumbingCollection.SelectCommand += LabBrandCBL.Text;
            first = false;
        }
        else
        {
            SqlMHIPlumbingCollection.SelectCommand += " AND " + LabBrandCBL.Text;
        }
    }

I “built” the SELECT statement as I scrolled through the code, and it looked correct, but obviously I’m missing something.

I appreciate any help with this method or if you could point me in the direction of a tutorial for a better method that would work with what I’ve done so far, I’m open to all options.

Thank you!