Using a ListBox Using SelectionMode Multiple with a GridView

1/22/2010

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:

   1: public class OurListBox : ListBox
   2: {
   3:     public OurListBox()
   4:         : base()
   5:     {
   6:     }
   7:  
   8:     public string SelectedValueList
   9:     {
  10:         get
  11:         {
  12:             bool None = true;
  13:             string Seperator = "";
  14:             StringBuilder Builder = new StringBuilder();
  15:             foreach (ListItem Item in Items)
  16:             {
  17:                 if (Item.Selected)
  18:                 {
  19:                     None = false;
  20:                     Builder.Append(Seperator).Append(Item.Value);
  21:                     Seperator = ",";
  22:                 }
  23:             }
  24:             if (None)
  25:             {
  26:                 Builder.Append("0");
  27:             }
  28:             return Builder.ToString();
  29:         }
  30:     }
  31: }

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:

   1: <asp:SqlDataSource ID="SqlDataSource2" runat="server" 
   2:    ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 
   3:    SelectCommand="SELECT * FROM Table WHERE Value in (SELECT Value FROM dbo.Split(@Value, ','))">
   4:    <SelectParameters>
   5:        <asp:ControlParameter ControlID="ListBox1" DefaultValue="0" Name="Value" PropertyName="SelectedValueList" />
   6:    </SelectParameters>
   7: </asp:SqlDataSource>
   8: <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.



Comments

James Craig
August 04, 2010 7:55 AM

I've run into that issue a couple of times. No idea why the datasource would pick it up though (the inner workings of Visual Studio are still a mystery to me).

Orio
August 04, 2010 3:57 AM

Found the problem. Although it was correctly declared in the aspx as the new type I replaced the original Listbox therefore it never changed the definition to the new Listbox in the designer.cs. Still a mystery why the datasource picked it up correctly.

Orio
August 04, 2010 3:19 AM

Excellent method James. I have given this an hour or two before I got to your post.Exactly what I needed. Just one mystery. The added property does not show up in VS's intellisence although it is public etc. So it was a surprise that it does get picked up by the datasource and causes a compiler error when accessed in code.Any idea why this would be? Do I just need to put an intellisence descriptions above the property?Cheers for this!

James Craig
February 24, 2010 2:02 PM

To be honest, I'm confused as to what your issue is and can't really say what the fix would be. I'm not sure what you mean by edit mode. Also, the SelectedValue property in a listbox should not be used for really anything if it allows multiple selection as at best it will hold one item. You need to go through the list of items and see if they're selected (which is what the SelectedValueList property does in the wrapper class). You may want to either email me or post a better/longer description of what your issue is.

will
February 24, 2010 10:42 AM

Nice post, that helped a ton, problem i'm seeing is that for whatever reason, the datasource is not getting bound before the call to bind the selected value when entering edit mode. So when the selectedvalue property gets set, there are no items in the list. I'm lost on this, have you ever run into this issue? Thanks again for the post. - WL