Solved

How to filter an ASP.Net GridView using a CheckBoxList

Posted on 2016-09-15
7
46 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.
0
Comment
Question by:megnin
  • 5
  • 2
7 Comments
 
LVL 13

Expert Comment

by:Megan Brooks
ID: 41801059
It's been years since I have worked in ASP.Net and not everything above is making sense to me at the moment, but even as I was reading I wondered about the NULL, and whether it couldn't just be represented by an empty string.

I see a query string in the first code block that doesn't look like ASPX markup. I assume that that is the source query, and that it is used somewhere that is not shown. It does a "select *", which made me cringe a little since that is a well-known way to load a result set down with columns that aren't used, but in this case it also stands in the way of returning the DISC column as ISNULL(<columnName>, '') AS <columnName>.

If you spell out the columns and specify the one causing trouble (DISCHighScores?) using that expression then there won't be a NULL field value to cause trouble. Would that solve the problem?
0
 
LVL 1

Author Comment

by:megnin
ID: 41804642
Thanks for the reply Megan.  We decided that the simplest solution was to use four fields to store the score letters.  So, instead of trying to parse one string where the letters could be in any order or combination (D,I,S, or C).  Thinking of it now, I see that will present it's own problem, since the order of the letters is relevant.  DI means something different from ID.  Basically, the higher score of the group is listed first.  Any score lower than a threshold is not listed.

Back to the original problem.  The only relevant column is "DISCHighScores."  It's currently a textbox that may contain any combination, in any order of the four letters, D,I,S, and C.  They represent scores.  If D and S are both above, say "50" then D and S are entered in the TextBox. The higher of the two is listed first.  So it could be DS or SD.  That only makes it so any list of possible combinations is larger than it would be if they were always in the same order.  Otherwise it doesn't affect any filter.
The filter should just list any records that "contain" the letters selected with the CheckBoxList.  "Contain", not equal to and not "IN."  Any letter(s) selected with the CheckBoxList merely needs to exist somewhere in the TextBox.

Here is the datasource query rewritten so it doesn't have "select *";  SelectCommand="SELECT Name, DISCHighScores FROM [Applicants] WHERE ([DISCHighScores] LIKE '%' + (@DISC) + '%' OR [DISCHighScores] IN (@DISC)) "

That worked fine when the data had no NULLs.  It worked as long as every record had at least one letter.   After testing, when the live data had NULL values, we found that it returned the error: "Length can not be less than zero."
"Line 29:             selected = string.Format("{0}", selected.Substring(0, selected.Length -3));"
0
 
LVL 1

Author Comment

by:megnin
ID: 41804672
Oh, disregard the above information about the order being relevant.  It is, for the purpose of scoring or whatever, but I only need to list records that contain the selected letters.  I don't care what order they are in.  At least that's not an issue.
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 13

Accepted Solution

by:
Megan Brooks earned 500 total points
ID: 41804889
I haven't had breakfast yet (yes, it makes a difference -- breakfast before SQL is highly recommended), but if I remember correctly (it's been a little while since I last looked at this), you might be able to change the query to something like

SELECT Name, DISCHighScores FROM [Applicants] WHERE ([DISCHighScores] LIKE '%' + ISNULL(@DISC, '') + '%' OR [DISCHighScores] IN (ISNULL(@DISC, ''))) 

Open in new window


Also, my mental "SQL injection alarm" is going off over combining literals with parameter values using "+". If you happened to be on SQL Server 2012 or higher, you could eliminate both the NULLs and the literal "+" using CONCAT.

Apologies in advance if my lack of breakfast is showing; I'll check back after eating.
0
 
LVL 1

Author Comment

by:megnin
ID: 41805106
Thank you, Megan.  I hope you enjoy your breakfast.  ;-)

That looks like a nice simple solution that just may work.  I'll check it in a little bit.
0
 
LVL 1

Author Comment

by:megnin
ID: 41806823
I got an "Incorrect syntax near ISNULL" error on "IN (ISNULL(@DISC, ''))" but "LIKE '%' + ISNULL(@DISC, '')" didn't cause an error.  Thank you for your input.
0
 
LVL 1

Author Closing Comment

by:megnin
ID: 41806824
Thank you for your assistance.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now