Populate dropdownlist with datareader from database

Question

Problem with populate specific dropdownlist values from database, i want to shows user all the current data from database tables to let them able to make changes, but i coulnd't shows the specific dropdownlist that user selected before. Im using linqdatasource to show all the dropdownlist value.

public partial class Update : System.Web.UI.Page
{
    string cs = Global.CS;    
    DataClasses1DataContext db = new DataClasses1DataContext();

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack) // only during initial load
        {
            string id = Request.QueryString["Item_ID"] ?? "";

            string sql = "Select * FROM MenuItem WHERE Item_ID = @id";


            SqlConnection con = new SqlConnection(cs);
            SqlCommand cmd = new SqlCommand(sql, con);

            cmd.Parameters.AddWithValue("@Id", id);

            con.Open();
            SqlDataReader dr = cmd.ExecuteReader();

            if (dr.Read())
            {
                if ((string)dr["Category"] == "Breakfast" || (string)dr["Category"] == "Lunch" || (string)dr["Category"] == "Dinner")
                {
                    DataBind();
                    lblId.Text = (string)dr["Item_ID"].ToString();
                    txtItemName.Text = (string)dr["ItemDesc"];
                    txtPrice.Text = (string)dr["Price"].ToString();
                    ddlCategory.Text = (string)dr["Category"];




                        //foreach (var checking in db.Sets)
                        //{
                        //    string setID = checking.Set_ID.ToString();
                        //    if (setID == (string)dr["Item_ID"])
                        //    {
                        //        ddlAlacarte.DataSourceID = "ldsAlacarte";
                        //        **ddlAlacarte.DataTextField = (string)dr["ItemDesc"].ToString();
                        //        ddlAlacarte.DataValueField = (string)dr["Item_ID"].ToString();**
                        //    }
                        //}                            




                }
                else
                {
                    ddlAlacarte.Enabled = false;
                    ddlBeverage.Enabled = false;
                    ddlSide.Enabled = false;
                    DataBind();

                    lblId.Text = (string)dr["Item_ID"].ToString();
                    txtItemName.Text = (string)dr["ItemDesc"];
                    txtPrice.Text = (string)dr["Price"].ToString();
                    ddlCategory.Text = (string)dr["Category"];


                }
            }
            else
            {
                Response.Redirect("MenuAdmin.aspx");
            }
            DataBind();
            dr.Close();
            con.Close();
        }
    }
    protected void ddlCategory_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (ddlCategory.SelectedItem.Text == "Breakfast" || ddlCategory.SelectedItem.Text == "Lunch" || ddlCategory.SelectedItem.Text == "Dinner")
        {
            ddlAlacarte.Enabled = true;
            ddlBeverage.Enabled = true;
            ddlSide.Enabled = true;
            DataBind();
        }
        else
        {
            ddlAlacarte.Enabled = false;
            ddlBeverage.Enabled = false;
            ddlSide.Enabled = false;
            DataBind();
        }
    }

}

Answer 1

I would use something like this

dropDownList.Items.Add(
    new ListItem(){ Text = dr["Breakfast"], Value = dr["Breakfast"] }
);

and iterate through, populating the dropdown list. Is that what you want ?

Answer 2

You need to add items in Dropdownlist while reading values.

Add the following code while you are reading values by using SqlDataReader.

while(dr.Read())
{
  ListItem listItem = new ListItem(); 

  listItem.Text = dr["Category"].ToString();
  listItem.Value = dr["Category"].ToString();

  categoryDropDownList.Items.Add(listItem);
}