asked on How to filter an ASP.Net GridView using a CheckBoxList
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):
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));"
Can that be fixed to account for the NULL values or is there an entirely different, better way to do it. Thanks in advance.
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.
ASP.NETC#Microsoft SQL Server 2008
Last Comment
David Megnin
ASKER
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));"
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));"
ASKER
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.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
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.
That looks like a nice simple solution that just may work. I'll check it in a little bit.
ASKER
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.
ASKER
Thank you for your assistance.
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?