Populate a number of DropDownLists with MySQL reader

Question

I have 10 DropDownLists (ddlQuestion1IDs, ddlQuestion2IDs, ddlQuestion3IDs...) that I want to populate with data from a database. However, the following code only populates ddlQuestion1IDs. Why is this and what's the most efficient way to populate all 10 with the same data?

protected void getQuestionIDs()
{
    string connStr = ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString;
    MySqlConnection conn = new MySqlConnection(connStr);
    MySqlDataReader reader;

    string cmdText = "SELECT * FROM questions WHERE module_id=@ModuleID";
    MySqlCommand cmd = new MySqlCommand(cmdText, conn);
    cmd.Parameters.Add("@ModuleID", MySqlDbType.Int32);
    cmd.Parameters["@ModuleID"].Value = ddlModules.SelectedValue;

    try
    {
        conn.Open();
        reader = cmd.ExecuteReader();
        for (int i = 1; i <= 10; i++)
        {
            var ddlQuestion = (DropDownList)FindControl("ddlQuestion" + i.ToString() + "IDs");
            ddlQuestion.DataSource = reader;
            ddlQuestion.DataValueField = "question_id";
            ddlQuestion.DataTextField = "question_id";
            ddlQuestion.DataBind();
            ddlQuestion.Items.Insert(0, new ListItem(string.Empty, "blank"));
        }
        reader.Close();
    }
    catch
    {
        lblError.Text = "Database connection error - failed to get question IDs.";
    }
    finally
    {
        conn.Close();
    }
}

Why don't you use a single variable instead of always searching the same control? That would be more efficient and - more important - much more readable. `var ddlQuestion = (DropDownList)FindControl("ddlQuestion" + i.ToString() + "IDs"); // set all properties...` — Tim Schmelter, Jul 22 '14 at 11:04
@TimSchmelter Modified. Thanks though still only the first DropDownList gets populated when I actually want to populate all ten. — Bhav, Jul 22 '14 at 11:11
I would use a `MySqlDataAdapter` to fill a `DataTable` and use that as `DataSource`. — Tim Schmelter, Jul 22 '14 at 11:21

score 0 · Accepted Answer · edited May 23 '17 at 10:25

0

My wild-ass-guess is that the reader is stream-based, forward-only and the first bind successfully iterates over the data while the subsequent binds find the data stream to be at the end.

I would try copying the data into an array and try binding to the array. Here's a question that might be helpful.

Can I reset and loop again through MySqlDataReader?

edited May 23 '17 at 10:25

Community

1
1

answered Jul 22 '14 at 11:16

JohnCampbellJr

126
3

Populate a number of DropDownLists with MySQL reader

1 Answers1