?
Solved

Cascading DropDownLists

Posted on 2011-10-03
8
Medium Priority
?
563 Views
Last Modified: 2012-05-12
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

0
Comment
Question by:asp_net2
  • 4
  • 4
8 Comments
 
LVL 29

Expert Comment

by:sammySeltzer
ID: 36904582
Can you post sample data from your city table?
0
 
LVL 4

Author Comment

by:asp_net2
ID: 36905148
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
 
LVL 29

Expert Comment

by:sammySeltzer
ID: 36905427
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 4

Author Comment

by:asp_net2
ID: 36905520
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
 
LVL 29

Expert Comment

by:sammySeltzer
ID: 36905771
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
 
LVL 29

Accepted Solution

by:
sammySeltzer earned 2000 total points
ID: 36906282
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
 
LVL 4

Author Comment

by:asp_net2
ID: 36907099
@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
 
LVL 4

Author Closing Comment

by:asp_net2
ID: 36907100
THANK YOU!!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question