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();
            }
        }
    }
}

Answer 1

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));