Dynamically filled Drop Down List loses contents on postback

Question

I have spent the last two hours reading every possible combination of solutions for this problem, and I still don't have it working. So obviously I'm missing something. If have a Drop Down List that I populate with a database call. When the selected index on the list changes, I post back and populate a list box with the information from another database call. But on the post back I loose the contents of the original drop down list.

I've verified the drop down list and the page both have EnableViewState set to true.

I've tried various combinations of when to fill the drop down list: Page_Init, Page_Load with !isPostBack, and various attachments to the DDL it's self.

So I'm asking the experts if they can see anything wrong. I'm a forms and command line programmer moving into the web, so its quite possible I'm missing something stupid.

This is what I have so far:

public partial class Payroll_PrintTimeSheets : System.Web.UI.Page

{

protected void Page_Init(object sender, EventArgs e)
{
    LoadTheSupervisors();
}

protected void Page_Load(object sender, EventArgs e)
{
}    // Page_Load() ...

protected void LoadTheSupervisors()
{
    SqlConnection cnSQL;
    string sql;
    SqlCommand cmSQL;
    SqlDataReader drSQL;

    // Load the supervisors ...
    cnSQL = new SqlConnection(ConfigurationManager.ConnectionStrings["mySQLServer"].ToString());

    try
    {
        cnSQL.Open();
        sql = "SELECT e.Employee, e.First_Name, e.Last_Name " +
                "FROM [PRODUCTION].[dbo].[Employee] e " +
                "WHERE e.Employee IN ( " +
                    "SELECT distinct(e.Supervisor) " +
                    "FROM [PRODUCTION].[dbo].[Employee] e LEFT OUTER JOIN [PRODUCTION].[dbo].[User_Values] uv  " +
                        "ON e.User_Values = uv.User_Values " +
                    "WHERE e.Status = 'Active' AND uv.Text2 = 'No' " +
                ")" +
                "ORDER BY e.Last_Name ASC " +
                "; ";

        cmSQL = new SqlCommand();
        cmSQL.Connection = cnSQL;
        cmSQL.CommandText = sql;
        drSQL = cmSQL.ExecuteReader();

        ddlSupervisors.Items.Clear();

        while (drSQL.Read())
        {   
            string tmpString = drSQL["Last_Name"].ToString() + ", " + drSQL["First_Name"].ToString();
            ddlSupervisors.Items.Add(new ListItem(tmpString, drSQL["Employee"].ToString()));
            //ddlSupervisors.Items.Add(tmpString);
        }    // while ...
    }
    catch (Exception ex)
    {
        tbError.Text = tbError.Text + ";" + ex.Message;
    }
    finally
    {
        if (cnSQL.State == ConnectionState.Open) cnSQL.Close();
        cnSQL.Dispose();
    }    // try/catch/finally ...
}    // LoadTheSupervisors() ...


protected void ddlSupervisors_SelectedIndexChanged(object sender, EventArgs e)
{
    SqlConnection cnSQL;
    string sql;
    SqlCommand cmSQL;
    SqlDataReader drSQL;
    string selectedValue;

    //LoadTheSupervisors();

    selectedValue = ddlSupervisors.SelectedItem.Value.ToString();
    //tbError.Text = tbError.Text + ";" + selectedValue;
    // Load the employees ...
    cnSQL = new SqlConnection(ConfigurationManager.ConnectionStrings["mySQLServer"].ToString());

    try
    {    
        cnSQL.Open();
        sql = "SELECT e.Employee, e.First_Name, e.Last_Name " +
                "FROM [PRODUCTION].[dbo].[Employee] e LEFT OUTER JOIN [PRODUCTION].[dbo].[User_Values] uv  " +
                    "ON e.User_Values = uv.User_Values " +
                "WHERE e.Supervisor = '" + selectedValue + "' " +
                    "AND e.Status = 'Active' AND uv.Text2 = 'No' " + 
                "ORDER BY e.Last_Name ASC " +
                "; ";
        //tbError.Text = tbError.Text + ";" + sql;
        cmSQL = new SqlCommand();
        cmSQL.Connection = cnSQL;
        cmSQL.CommandText = sql;
        drSQL = cmSQL.ExecuteReader();

        ddlSupervisors.Items.Clear();

        while (drSQL.Read())
        {   
            string tmpString = drSQL["Last_Name"].ToString() + ", " + drSQL["First_Name"].ToString();
            lbEmployees.Items.Add(new ListItem(tmpString, drSQL["Employee"].ToString()));
            //ddlSupervisors.Items.Add(tmpString);
        }    // while ...
    }
    catch (Exception ex)
    {
        tbError.Text = tbError.Text + ";" + ex.Message;
    }
    finally
    {
        if (cnSQL.State == ConnectionState.Open) cnSQL.Close();
        cnSQL.Dispose();
    }    // try/catch/finally ...
}    // ddlSupervisors_SelectedIndexChanged() ...

} // class Payroll_PrintTimeSheets ...

AND

<%@ Page Title="" Language="C#"  EnableViewState="true" MasterPageFile="~/Site.master" AutoEventWireup="true" CodeFile="PrintTimeSheets.aspx.cs" Inherits="Payroll_PrintTimeSheets" %>

Supervisor

<p>
    <asp:TextBox ID="tbError" runat="server" TextMode="MultiLine"></asp:TextBox>
</p>
<p>
    &nbsp;</p>
<p>
    &nbsp;</p>

Any Suggestions?

Answer 1

You are clearing the contents of dropDownList instead of ListBox in the SelectedIndexChanged event before filling the listBox

ddlSupervisors.Items.Clear();

change that line to:

lbEmployees.Items.Clear();

Hope this helps...