Link to home
Start Free TrialLog in
Avatar of Brian
BrianFlag for United States of America

asked on

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

Avatar of sammySeltzer
sammySeltzer
Flag of United States of America image

Can you post sample data from your city table?
Avatar of Brian

ASKER

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
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.
Avatar of Brian

ASKER

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

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
ASKER CERTIFIED SOLUTION
Avatar of sammySeltzer
sammySeltzer
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Brian

ASKER

@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

Avatar of Brian

ASKER

THANK YOU!!