We help IT Professionals succeed at work.
Get Started

How to filter an ASP.Net GridView using a CheckBoxList

429 Views
Last Modified: 2016-09-20
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)) " 

Open in new window


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();
    }

Open in new window


Can that be fixed to account for the NULL values or is there an entirely different, better way to do it.  Thanks in advance.
Comment
Watch Question
SQL Server Consultant
Commented:
This problem has been solved!
Unlock 1 Answer and 7 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE