How to filter an ASP.Net GridView using a CheckBoxList

Posted on 2016-09-15
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: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.
Question by:megnin
  • 5
  • 2
LVL 14

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?

Author Comment

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));"

Author Comment

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.
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

LVL 14

Accepted Solution

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.

Author Comment

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.

Author Comment

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.

Author Closing Comment

ID: 41806824
Thank you for your assistance.

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Problem to error 4 43
Syntax Error 2 42
Server Error 11 47
Using CTE to insert records into a table 2 26
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Need to grow your business through quality cloud solutions? With everything required to build a cloud platform and solution, you may feel like the distance between you and the cloud is quite long. Help is here. Spend some time learning about the Con…
A company’s greatest vulnerability is their email. CEO fraud, ransomware and spear phishing attacks are the no1 threat to a company’s security. Cybercrime is responsible for the largest loss of money to companies today with losses projected to r…

930 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