Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How to filter an ASP.Net GridView using a CheckBoxList

Posted on 2016-09-15
7
Medium Priority
?
191 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
7 Comments
 
LVL 16

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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 16

Accepted Solution

by:
Megan Brooks earned 2000 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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Loops Section Overview

610 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