You've probably run into this before. You have a ListBox (Or CheckBoxList or whatever) that lists a number of options and you can select multiple items. You then want it to be used to update the content of a GridView. Most likely you tried setting up the select statement as something like this: "SELECT * FROM Table WHERE Value=@Value" or even "SELECT * FROM Table WHERE Value in (@Value)". With @Value pointing to the SelectedValue parameter of the ListBox. If you tried that you'll notice that it didn't work. Well I mean it will update the GridView but it will only show that you selected one item. So how do we fix this?
One of the things that I like about the way the classes like GridView, etc. were set up is that you can inherit from them. In our case we want to create an item that uses the ListBox object:
public class OurListBox : ListBox
{
public OurListBox()
: base()
{
}
public string SelectedValueList
{
get
{
bool None = true;
string Seperator = "";
StringBuilder Builder = new StringBuilder();
foreach (ListItem Item in Items)
{
if (Item.Selected)
{
None = false;
Builder.Append(Seperator).Append(Item.Value);
Seperator = ",";
}
}
if (None)
{
Builder.Append("0");
}
return Builder.ToString();
}
}
}
Ok, so now we have something that gives us a comma delimited string of the selected values. That's great but it doesn't help us much as sending that to @Value is just going to come back with an error or not much at all... So what we need is an SQL function that can parse that string. Luckily I found one that I could simply copy from here. So we create the function, switch our ListBox to the OurListBox class that we just created, and switch our select statement to this: "SELECT * FROM Table where Value in (SELECT Value FROM dbo.Split(@Value, ','))". And our controlparameter pointing towards the SelectedValueList property... And suddenly we have a working solution. Oh and just so you have something to look at:
<asp:SqlDataSource ID="SqlDataSource2" runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT * FROM Table WHERE Value in (SELECT Value FROM dbo.Split(@Value, ','))">
<SelectParameters>
<asp:ControlParameter ControlID="ListBox1" DefaultValue="0" Name="Value" PropertyName="SelectedValueList" />
</SelectParameters>
</asp:SqlDataSource>
<cc1:OurListBox ID="ListBox1" runat="server" SelectionMode="Multiple"></cc1:OurListBox>
That's it. So hopefully this will help you out. Try it out, leave feedback, and happy coding.
52a1c3b1-f9ff-445f-8800-4718ad329703|2|3.0