Preselect multiple items in the list based on database values.
I have a list box that on page load I would select the options / options that are in the database. It's been a while since I've done anything with lists, so I'm a little confused as to how to fix the code for my GetClassification function to do just that. At the moment, it selects only one value in the list, regardless of the vendor ID 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();
}
}
}
}
source to share
You need to encode all elements and set 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);
Also, you have to be careful with a constructor SqlParameter
that takes two parameters if the second is int
, like here:
md.Parameters.Add(new SqlParameter("@VendorId", VendorId));
VendorId
will be dropped on SqlDbType
and a different overload is used . Instead, you must explicitly state Value
:
md.Parameters.Add(new SqlParameter("@VendorId", SqlDbType.Int) { Value = VendorId });
Edit : This is also described in the notes section :
Be careful when using this constructor overload
SqlParameter
to specify parameter valuesinteger
. Since this overload occupies a value of the typeObject
, 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 will work as well:
md.Parameters.Add(new SqlParameter("@VendorId", (object) VendorId));
source to share