troubleshooting Question

How to filter an ASP.Net GridView using a CheckBoxList

Avatar of David Megnin
David MegninFlag for United States of America asked on
ASP.NETC#Microsoft SQL Server 2008
7 Comments1 Solution430 ViewsLast Modified:
I have a GridView where one field comes from a single SQL column that may contain NULL or any combination of the letters; "D", "I", "S", or "C."  Example values may be; NULL, "D", "DI", "ID", "CS", "SI", "DC", "CSI", "ICS", or even "DISC"

Using, say, a CheckBoxList where each check is one of the letters, I need to filter the GridView so that it displays all of the records that contain any checked value.

If only "D" is checked, the GridView should display all records that contain "D" or any other value except NULL.  "D", "DI", "ID", "CD", "DS" should all show.  "ICS" would not show.

If more than one letter is checked, the GridView should show all records that contain all of the selected values or more.  So, if "D" and "S" are checked, the GridView should show "DS", "SD", "DIS", "DCS" and "DISC."

If all four; "D", "I", "S", and "C" are selected then only "DISC" would be shown.

If none are selected then all records should show.  This should be the default view with no check boxes checked.

Some of my markup for reference (the CheckBoxList is external to the GridView):
<asp:Label ID="lblDISC" runat="server" Text="_"></asp:Label>

        High Scores: 
        <asp:CheckBoxList ID="cbDISC" runat="server" RepeatDirection="Horizontal" RepeatLayout="Flow" AutoPostBack="True" OnSelectedIndexChanged="cbDISC_SelectedIndexChanged">
            <asp:ListItem Text="All" Value="%" />
            <asp:ListItem>D</asp:ListItem>
            <asp:ListItem>I</asp:ListItem>
            <asp:ListItem>S</asp:ListItem>
            <asp:ListItem>C</asp:ListItem>
        </asp:CheckBoxList>

<asp:BoundField DataField="DISCHighScores" HeaderText="DISC Scores" SortExpression="DISCHighScores" />

SelectCommand="SELECT * FROM [Applicants] WHERE ([DISCHighScores] LIKE '%' + (@DISC) + '%' OR [DISCHighScores] IN (@DISC)) " 

This kind of worked when there were no NULL values in the database.  Live data had NULL scores and broke it on this line with the error: "Length can not be less than zero."
"Line 29:             selected = string.Format("{0}", selected.Substring(0, selected.Length -3));"

    protected void cbDISC_SelectedIndexChanged(object sender, EventArgs e)
    {
        string selected = "";
        foreach (ListItem item in cbDISC.Items)
        {
            //selected += item.Selected ? string.Format(" [STATUS] = '{0}' OR ", item.Value) : "";
            //selected += item.Selected ? string.Format("'{0}', ", item.Value) : "";
            //selected += item.Selected ? string.Format("'{0}' , ", item.Value) : "";
              selected += item.Selected ? string.Format("{0}", item.Value) : "";
        }
        if (!string.IsNullOrEmpty(selected))
        {
            selected = string.Format("{0}", selected.Substring(0, selected.Length -3));
            //selected = string.Format("{0}", selected.Substring(0, selected.Length - 0));
        }

        lblDISC.Text = selected.ToString();

        Label2.Text = selected.Length.ToString();
        Label3.Text = cbDISC.SelectedValue.ToString();
    }

Can that be fixed to account for the NULL values or is there an entirely different, better way to do it.  Thanks in advance.
ASKER CERTIFIED SOLUTION
Megan Brooks
SQL Server Consultant

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros