Pre-select multiple items in listbox based on database values

Question

I have a list box that, upon loading the page, I would like to have selected the choices/options that are in the database. It's been a while since I've done anything with list boxes, so I'm a bit confused on how to fix the code for my GetClassification function that is meant to do exactly this. At the moment, it only selects one value in the listbox regardless of the vendor id is associated with more than one.

This is the code for the GetClassification function:

protected void GetClassification(int VendorId)
{
    using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["AbleCommerce"].ToString()))
    {
        SqlCommand cmd = new SqlCommand("SELECT uidClassification FROM Baird_Vendors_Extension WHERE uidVendor = @VendorId", cn);
        cmd.CommandType = CommandType.Text;
        cmd.Parameters.Add(new SqlParameter("@VendorId", VendorId));
        cn.Open();
        using (IDataReader reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                vendorType.SelectedValue =reader["uidClassification"].ToString();
            }
        }
    }
}

You don't need @ClassId since you didn't declare this parameter in your SqlCommand. — Soner Gönül
– Soner Gönül, Commented Sep 23, 2014 at 12:47

Tim Schmelter · Accepted Answer · 2014-09-23 13:15:04Z

2

You have to loop all items and set the Selected-property accordingly:

List<string> uidClassificationList = new List<string>();
using (IDataReader reader = cmd.ExecuteReader())
{
    while (reader.Read())
    {
        int column = reader.GetOrdinal("uidClassification");
        uidClassificationList.Add(reader.GetInt32( column ).ToString());
    }
}
foreach(ListItem item in vendorType.Items)
    item.Selected = uidClassificationList.Contains(item.Value);

Apart from that you should be careful with the SqlParameter constructor that takes two parameters if the second is an int like here:

md.Parameters.Add(new SqlParameter("@VendorId", VendorId));

The VendorId will be casted to SqlDbType and a different overload is used. Instead you should specify the Value explicitly:

md.Parameters.Add(new SqlParameter("@VendorId", SqlDbType.Int) { Value = VendorId });

Edit: this is also documented in the remarks-section:

Use caution when you use this overload of the SqlParameter constructor to specify integer parameter values. Because this overload takes a value of type Object, you must convert the integral value to an Object type when the value is zero, as the following C# example demonstrates.

Parameter = new SqlParameter("@pname", (object)0);

If you do not perform this conversion, the compiler assumes that you are trying to call the SqlParameter (string, SqlDbType) constructor overload.

So this would work also:

md.Parameters.Add(new SqlParameter("@VendorId", (object) VendorId));

edited Sep 23, 2014 at 13:15

answered Sep 23, 2014 at 12:52

Tim Schmelter

462k79 gold badges720 silver badges981 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Dejsa Cocan Over a year ago

The only problem with the above is that, for the line inside the while loop, I see this error: The best overload match for System.Data.IDataRecord.GetString(int) has some invalid arguments...

Tim Schmelter Over a year ago

@gallifrey1212: then it's actually an int. I prefer the Get... methods since they fail fast if i mistakenly use the wrong datatype and because i don't get implicit conversion(like from DateTime including localization issues). Note that i've added my answer.

Dejsa Cocan Over a year ago

Ok, I applied the changes and it works perfectly. Thanks a lot :)

MANOJ GOPI · Accepted Answer · 2014-09-23 13:14:45Z

0

Check if the ListBox's SelectionMode property is given as Multiple which will enable the multiple selection.

e.g

<asp:ListBox ID="ListBox1" runat="server" SelectionMode="Multiple"></asp:ListBox>

answered Sep 23, 2014 at 13:14

MANOJ GOPI

1,27710 silver badges31 bronze badges

Comments

Loureiro · Accepted Answer · 2022-08-31 16:47:45Z

0

If someone is looking for how to just select multiple values from the Items(Collection). You need your SelectionMode to be MultiSimple and then do like this for example

listBoxName.SetSelected(0, true);
listBoxName.SetSelected(1, true);

In this case the 1st and 2nd values will be pre-selected.

If you need a better example.

edited Aug 31, 2022 at 16:47

answered Aug 31, 2022 at 16:43

Loureiro

12 bronze badges

Collectives™ on Stack Overflow

Pre-select multiple items in listbox based on database values

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related