Populate Drop Down from Selection on second drop down

Question

So I am trying to populate one dropdown from the selection of another. I have tested the stored proc I am using and, when entering a value, I get the right results. I know there are many questions like this but none seem to fix my issue.

protected void Page_Load(object sender, EventArgs e)
{
    DataTable environments = new DataTable();
    var connection = ConfigurationManager.ConnectionStrings["Connection"].ConnectionString;

    using (SqlConnection conn = new SqlConnection(connection))
    {
        SqlDataAdapter adapter = new SqlDataAdapter("SELECT Environment FROM Environments", conn);
        adapter.Fill(environments);
        ddlEnvironment.Items.Insert(0, new ListItem(String.Empty, String.Empty));
        ddlEnvironment.SelectedIndex = 0;
        ddlEnvironment.DataSource = environments;
        ddlEnvironment.DataTextField = "Environment";
        ddlEnvironment.DataValueField = "Environment";
        ddlEnvironment.DataBind();

        SqlCommand cmd = new SqlCommand();
        SqlDataAdapter adapter2 = new SqlDataAdapter();
        DataTable servers = new DataTable();

        cmd = new SqlCommand("sp_EnvironmentSelection", conn);
        cmd.Parameters.AddWithValue("@Environment", ddlEnvironment.SelectedValue);
        cmd.CommandType = System.Data.CommandType.StoredProcedure;
        adapter2.SelectCommand = cmd;
        adapter2.Fill(servers);

        ddlServer.Items.Insert(0, new ListItem(String.Empty, String.Empty));
        ddlServer.SelectedIndex = 0;
        ddlServer.DataSource = servers;
        ddlServer.DataTextField = "ServerName";
        ddlServer.DataValueField = "ServerIP";
        ddlServer.DataBind();
    }
}

The issue is, I don't get any choices on the second drop down no matter my selection on the first drop down.

Here is the stored proc if needed.

@Environment    nvarchar(50)

AS
BEGIN

SET NOCOUNT ON

SELECT Server.ServerName, Server.ServerIP, Environments.Environment
FROM Server
INNER JOIN Environments
ON
Environments.Environment=Server.Environment
WHERE Server.Environment=@Environment

END

Answer 1

If you step through your code as it is executing, you will see that when cmd.Parameters.AddWithValue("@Environment", ddlEnvironment.SelectedValue); is called, ddlEnvironment.SelectedValue will not be set to anything. This is because at the time you're running this code, it's right after ddlEnvironment is being binded to its data. It has no information at that time about what the user selected.

You need to move your binding of the second list into an event handler that handles the ddlEvironment.SelectedIndexChanged event. In there, ddlEnvironment.SelectedValue will be set to what the user selected. And in Page_Load, you do not want to re-bind the first list each time there is a postback, so it needs to be wrapped in an if (!Page.IsPostBack).

See the question here: DropDownList's SelectedIndexChanged event not firing

Your first dropdown list in the asp code needs to look something like this:

<asp:DropDownList ID="ddlEnvironemnt" runat="server" AutoPostBack="True" 
        onselectedindexchanged="ddlEnvironemnt_SelectedIndexChanged">
    </asp:DropDownList>

Your page_load would be like this:

protected void Page_Load(object sender, EventArgs e)
{
    if (Page.IsPostBack)
    {
        return;
    }
    DataTable environments = new DataTable();
    var connection = ConfigurationManager.ConnectionStrings["Connection"].ConnectionString;

    using (SqlConnection conn = new SqlConnection(connection))
    {
        SqlDataAdapter adapter = new SqlDataAdapter("SELECT Environment FROM Environments", conn);
        adapter.Fill(environments);
        ddlEnvironment.Items.Insert(0, new ListItem(String.Empty, String.Empty));
        ddlEnvironment.SelectedIndex = 0;
        ddlEnvironment.DataSource = environments;
        ddlEnvironment.DataTextField = "Environment";
        ddlEnvironment.DataValueField = "Environment";
        ddlEnvironment.DataBind();
    }
}

And you would have an event handler:

protected void ddlEnvironemnt_SelectedIndexChanged(object sender, EventArgs e)
{

    var connection = ConfigurationManager.ConnectionStrings["Connection"].ConnectionString;

    using (SqlConnection conn = new SqlConnection(connection))
    {
        SqlCommand cmd = new SqlCommand();
        SqlDataAdapter adapter2 = new SqlDataAdapter();
        DataTable servers = new DataTable();

        cmd = new SqlCommand("sp_EnvironmentSelection", conn);
        cmd.Parameters.AddWithValue("@Environment", ddlEnvironment.SelectedValue);
        cmd.CommandType = System.Data.CommandType.StoredProcedure;
        adapter2.SelectCommand = cmd;
        adapter2.Fill(servers);

        ddlServer.Items.Insert(0, new ListItem(String.Empty, String.Empty));
        ddlServer.SelectedIndex = 0;
        ddlServer.DataSource = servers;
        ddlServer.DataTextField = "ServerName";
        ddlServer.DataValueField = "ServerIP";
        ddlServer.DataBind();
    }
}