Cascading DropDownLists

Hello Experts,

I need help with 3 DropDownList Controls that need to Cascade. I'm trying to follow the example from the following article below and I'm having trouble getting values for the third DropDown Control (ddlCity) once a value is selected from the Second DropDownList Control (ddlCountry). Please see my CodeBehind, HTML Markup, and Stored Procedures used.

So to recap, I'm having trouble getting the values from the DropDownList Control ddlCountry once a value is selected from it. It should populate the third DropDownList Control (ddlCity) but does not.

Article: http://www.aspsnippets.com/Articles/Creating-Cascading-DropDownLists-in-ASP.Net.aspx


CODEBEHIND:

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class dropdowntest : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            ddlContinents.AppendDataBoundItems = true;

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["HealthCourses"].ConnectionString))
            {

                SqlCommand cmdddl1 = new SqlCommand();
                cmdddl1.CommandText = "SelectState";
                cmdddl1.CommandType = CommandType.StoredProcedure;
                cmdddl1.Connection = conn;

                try
                {
                    conn.Open();

                    SqlDataReader rdr = cmdddl1.ExecuteReader();

                    ddlContinents.DataSource = rdr;
                    ddlContinents.DataValueField = "state_id";
                    ddlContinents.DataTextField = "state_name";
                    ddlContinents.DataBind();
                }

                catch (Exception ex)
                {
                    ex.Message.ToString();
                }

                finally
                {
                    conn.Close();
                }
            }
        }
    }

    //Populating the Country DropDownList
    protected void ddlContinents_SelectedIndexChanged(object sender, EventArgs e)
    {
        ddlCountry.Items.Clear();
        ddlCountry.Items.Add(new ListItem("--Select Country--", ""));
        ddlCity.Items.Clear();
        ddlCity.Items.Add(new ListItem("--Select City--", ""));

        ddlCountry.AppendDataBoundItems = true;

        using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["HealthCourses"].ConnectionString))
        {
            SqlCommand cmdddl2 = new SqlCommand();
            cmdddl2.CommandText = "SelectCity";
            cmdddl2.CommandType = CommandType.StoredProcedure;
            cmdddl2.Connection = conn;

            cmdddl2.Parameters.AddWithValue("@state_id", ddlContinents.SelectedItem.Value);

            try
            {
                conn.Open();

                ddlCountry.DataSource = cmdddl2.ExecuteReader();
                ddlCountry.DataValueField = "city_id";
                ddlCountry.DataTextField = "city_name";
                ddlCountry.DataBind();

                if (ddlCountry.Items.Count > 1)
                {
                    ddlCountry.Enabled = true;
                }

                else
                {
                    ddlCountry.Enabled = false;
                    ddlCity.Enabled = false;
                }
            }

            catch (Exception ex)
            {
                ex.Message.ToString();
            }

            finally
            {
                conn.Close();
            }
        }
    }

    // Populating the City DropDownList
    protected void ddlCountry_SelectedIndexChanged(object sender, EventArgs e)
    {
        ddlCity.Items.Clear();
        ddlCity.Items.Add(new ListItem("--Select City--", ""));

        ddlCity.AppendDataBoundItems = true;

        using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["HealthCourses"].ConnectionString))
        {
            SqlCommand cmdddl3 = new SqlCommand();
            cmdddl3.CommandText = "SelectZip";
            cmdddl3.CommandType = CommandType.StoredProcedure;
            cmdddl3.Connection = conn;

            cmdddl3.Parameters.AddWithValue("@city_id", ddlCountry.SelectedItem.Value);

            try
            {
                conn.Open();

                ddlCity.DataSource = cmdddl3;
                ddlCity.DataValueField = "zip_id";
                ddlCity.DataTextField = "zip_name";
                ddlCity.DataBind();

                if (ddlCity.Items.Count > 1)
                {
                    ddlCity.Enabled = true;
                }

                else
                {
                    ddlCity.Enabled = false;
                }
            }

            catch (Exception ex)
            {
                ex.Message.ToString();
            }

            finally
            {
                conn.Close();
            }
        }
    }

    // Displaying the Results 
    protected void ddlCity_SelectedIndexChanged(object sender, EventArgs e)
    {
        lblResults.Text = "You Selected " +
                          ddlContinents.SelectedItem.Text + " -----> " +
                          ddlCountry.SelectedItem.Text + " -----> " +
                          ddlCity.SelectedItem.Text;
    }
}

Open in new window

STORED PROCEDURES:

ALTER PROCEDURE [dbo].[SelectState]

AS

SELECT state_id, state_name
FROM State

Open in new window

(
@state_id int
)

AS

SELECT city_id, city_name
FROM City
WHERE state_id = @state_id

Open in new window

ALTER PROCEDURE [dbo].[SelectZip]

(
@city_id int
)

AS

SELECT zip_id, zip_name
FROM Zip
WHERE city_id = @city_id

Open in new window

HTML MARKUP:

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
<asp:DropDownList ID="ddlContinents" runat="server" AutoPostBack="true" OnSelectedIndexChanged="ddlContinents_SelectedIndexChanged">
    <asp:ListItem Text="--Select Continent--" Value=""></asp:ListItem>
</asp:DropDownList>
<br /><br />
<span style ="font-family:Arial">Select Country : </span> 
<asp:DropDownList ID="ddlCountry" runat="server" AutoPostBack="true" Enabled="false" OnSelectedIndexChanged="ddlCountry_SelectedIndexChanged"> 
    <asp:ListItem Text="--Select Country--" Value=""></asp:ListItem>
</asp:DropDownList>
<br /><br />
<span style ="font-family:Arial">Select City : </span> 
<asp:DropDownList ID="ddlCity" runat="server" AutoPostBack="true" Enabled="false" OnSelectedIndexChanged="ddlCity_SelectedIndexChanged">
    <asp:ListItem Text="--Select City--" Value=""></asp:ListItem>
</asp:DropDownList>
<br /><br />
<asp:Label ID="lblResults" runat="server" Text="" Font-Names = "Arial" />
    </div>
    </form>
</body>
</html>

Open in new window

LVL 4
asp_net2Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sammySeltzerCommented:
Can you post sample data from your city table?
0
asp_net2Author Commented:
Below is sameple data for each Table (DropDownList)

State Table:
    state_id  int  PK
    state_name  varchar

City Table:
    city_id  int  PK
    state_id  int  FK to State Table
    city_name  varchar

Zip Table:
    zip_id  int  PK
    city_id  int  FK to City Table
    zip_name  varchar


State Table Sample Data:
    10    PA
    11    WV

City Table Sample Data:
    100    10    Allentown
    101    10    Pittsburgh
    102    11    Chester
    103    11    Newell

Zip Table Sample Data:
    200    100    23322
    201    100    34432
    202    100    21123
    203    101    34444
    204    101    23323
    205    101    84453
    206    102    94487
    207    102    83378
0
sammySeltzerCommented:
Can you review your stored procedures again and post the complete and accurate ones?

When I tested using embedded sql, everything works fine for me.

But I want to test with your stored procedures.
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

asp_net2Author Commented:
I have attached the Stored Procedure's that I have setup for this project.
STORED PROCEDURE (SelectState)

ALTER PROCEDURE [dbo].[SelectState]

AS

SELECT state_id, state_name
FROM State

Open in new window

STORED PROCEDURE (SelectCity):

ALTER PROCEDURE [dbo].[SelectCity]

(
@state_id int
)

AS

SELECT city_id, city_name
FROM City
WHERE state_id = @state_id

Open in new window

STORED PROCEDURE (SelectZip):

ALTER PROCEDURE [dbo].[SelectZip]

(
@city_id int
)

AS

SELECT zip_id, zip_name
FROM Zip
WHERE city_id = @city_id

Open in new window

0
sammySeltzerCommented:
it works with my version which is vb but with your stored proc.

see screenshot.

I am now trying it with your version of code.


ccs.JPG
0
sammySeltzerCommented:
This works for me: Be sure to change name from CS to dropdowntest or whatever your preferred name is.

Also, change your connection string from connstr to Healthcares or something like that.

markup:

<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="CS.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
   <title>Cascading DropDownList Example</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <span style ="font-family:Arial">Select Continent : </span> 
        <asp:DropDownList ID="ddlContinents" runat="server" AutoPostBack = "true" OnSelectedIndexChanged="ddlContinents_SelectedIndexChanged">
        <asp:ListItem Text = "--Select Continent--" Value = ""></asp:ListItem>
        </asp:DropDownList>
        <br /><br />
        <span style ="font-family:Arial">Select Country : </span> 
        <asp:DropDownList ID="ddlCountry" runat="server" Enabled = "false" AutoPostBack = "true" OnSelectedIndexChanged="ddlCountry_SelectedIndexChanged">
        <asp:ListItem Text = "--Select Country--" Value = ""></asp:ListItem>
        </asp:DropDownList>
        <br /><br />
        <span style ="font-family:Arial">Select City : </span> 
        <asp:DropDownList ID="ddlCity" runat="server" Enabled = "false" AutoPostBack = "true" OnSelectedIndexChanged="ddlCity_SelectedIndexChanged">
        <asp:ListItem Text = "--Select City--" Value = ""></asp:ListItem>
        </asp:DropDownList>
        <br /><br />
        <asp:Label ID="lblResults" runat="server" Text="" Font-Names = "Arial"></asp:Label>        
    </div>
    </form>
</body>
</html>

Open in new window


CodeBehind:

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            ddlContinents.AppendDataBoundItems = true;
            String strConnString = ConfigurationManager
                .ConnectionStrings["conString"].ConnectionString;
            //String strQuery = "select ID, ContinentName from Continents";
            SqlConnection con = new SqlConnection(strConnString);
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "SelectState";
            cmd.Connection = con;
            try
            {
                con.Open();
                ddlContinents.DataSource = cmd.ExecuteReader();
                ddlContinents.DataTextField = "state_name";
                ddlContinents.DataValueField = "state_id";
                ddlContinents.DataBind();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
                con.Dispose();
            }
        }
    }
    protected void ddlContinents_SelectedIndexChanged(object sender, EventArgs e)
    {
        ddlCountry.Items.Clear();
        ddlCountry.Items.Add(new ListItem("--Select Country--", ""));
        ddlCity.Items.Clear();
        ddlCity.Items.Add(new ListItem("--Select City--", ""));    

        ddlCountry.AppendDataBoundItems = true;
        String strConnString = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
        //String strQuery = "select ID, CountryName from Countries where ContinentID=@ContinentID";
        SqlConnection con = new SqlConnection(strConnString);
        SqlCommand cmd = new SqlCommand();
        cmd.Parameters.AddWithValue("@state_id", ddlContinents.SelectedItem.Value);  
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "SelectCity";
        cmd.Connection = con;
        try
        {
            con.Open();
            ddlCountry.DataSource = cmd.ExecuteReader();
            ddlCountry.DataTextField = "city_name";
            ddlCountry.DataValueField = "city_id";
            ddlCountry.DataBind();
            if (ddlCountry.Items.Count > 1)
            {
                ddlCountry.Enabled = true;
            }
            else
            {
                ddlCountry.Enabled = false;
                ddlCity.Enabled = false; 
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            con.Close();
            con.Dispose();
        }
    }
    protected void ddlCountry_SelectedIndexChanged(object sender, EventArgs e)
    {
        ddlCity.Items.Clear();
        ddlCity.Items.Add(new ListItem("--Select City--", ""));
        ddlCity.AppendDataBoundItems = true;
        String strConnString = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
        //String strQuery = "select ID, CityName from Cities where CountryID=@CountryID";
        SqlConnection con = new SqlConnection(strConnString);
        SqlCommand cmd = new SqlCommand();
        cmd.Parameters.AddWithValue("@city_id", ddlCountry.SelectedItem.Value);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "SelectZip";
        cmd.Connection = con;
        try
        {
            con.Open();
            ddlCity.DataSource = cmd.ExecuteReader();
            ddlCity.DataTextField = "zip_name";
            ddlCity.DataValueField = "zip_id";
            ddlCity.DataBind();
            if (ddlCity.Items.Count > 1)
            {
                ddlCity.Enabled = true;
            }
            else
            {
                ddlCity.Enabled = false;
            }
          
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            con.Close();
            con.Dispose();
        }
    }

protected void ddlCity_SelectedIndexChanged(object sender, EventArgs e)
{
    lblResults.Text = "You Selected " +
                      ddlContinents.SelectedItem.Text + " -----> " +
                      ddlCountry.SelectedItem.Text + " -----> " +
                      ddlCity.SelectedItem.Text; 
}
}

Open in new window



Screenshot:

ccs.JPG
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
asp_net2Author Commented:
@sammySeltzer,

Thank you. Your solution worked. I had to make the changes below for it to work with my code. Please see below.
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class dropdowntest : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            ddlContinents.AppendDataBoundItems = true;

            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["HealthCourses"].ConnectionString);

            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "SelectState";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = conn;

            try
            {
                conn.Open();

                ddlContinents.DataSource = cmd.ExecuteReader();
                ddlContinents.DataTextField = "state_name";
                ddlContinents.DataValueField = "state_id";
                ddlContinents.DataBind();
            }

            catch (Exception ex)
            {
                throw ex;
            }

            finally
            {
                conn.Close();
                conn.Dispose();
            }
        }
    }

    protected void ddlContinents_SelectedIndexChanged(object sender, EventArgs e)
    {
        ddlCountry.Items.Clear();
        ddlCountry.Items.Add(new ListItem("--Select Country--", ""));
        ddlCity.Items.Clear();
        ddlCity.Items.Add(new ListItem("--Select City--", ""));

        ddlCountry.AppendDataBoundItems = true;

        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["HealthCourses"].ConnectionString);

        SqlCommand cmd = new SqlCommand();
        cmd.CommandText = "SelectCity";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Connection = conn;

        cmd.Parameters.AddWithValue("@state_id", ddlContinents.SelectedItem.Value);

        try
        {
            conn.Open();
            ddlCountry.DataSource = cmd.ExecuteReader();
            ddlCountry.DataTextField = "city_name";
            ddlCountry.DataValueField = "city_id";
            ddlCountry.DataBind();

            if (ddlCountry.Items.Count > 1)
            {
                ddlCountry.Enabled = true;
            }
            else
            {
                ddlCountry.Enabled = false;
                ddlCity.Enabled = false;
            }
        }

        catch (Exception ex)
        {
            throw ex;
        }

        finally
        {
            conn.Close();
            conn.Dispose();
        }
    }

    protected void ddlCountry_SelectedIndexChanged(object sender, EventArgs e)
    {
        ddlCity.Items.Clear();
        ddlCity.Items.Add(new ListItem("--Select City--", ""));
        ddlCity.AppendDataBoundItems = true;

        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["HealthCourses"].ConnectionString);

        SqlCommand cmd = new SqlCommand();
        cmd.CommandText = "SelectZip";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Connection = conn;

        cmd.Parameters.AddWithValue("@city_id", ddlCountry.SelectedItem.Value);

        try
        {
            conn.Open();
            ddlCity.DataSource = cmd.ExecuteReader();
            ddlCity.DataTextField = "zip_name";
            ddlCity.DataValueField = "zip_id";
            ddlCity.DataBind();
            if (ddlCity.Items.Count > 1)
            {
                ddlCity.Enabled = true;
            }
            else
            {
                ddlCity.Enabled = false;
            }

        }

        catch (Exception ex)
        {
            throw ex;
        }

        finally
        {
            conn.Close();
            conn.Dispose();
        }
    }

    protected void ddlCity_SelectedIndexChanged(object sender, EventArgs e)
    {
        lblResults.Text = "You Selected " +
                          ddlContinents.SelectedItem.Text + " -----> " +
                          ddlCountry.SelectedItem.Text + " -----> " +
                          ddlCity.SelectedItem.Text;
    }

}

Open in new window

0
asp_net2Author Commented:
THANK YOU!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.