Brian
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
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;
}
}
STORED PROCEDURES:
ALTER PROCEDURE [dbo].[SelectState]
AS
SELECT state_id, state_name
FROM State
(
@state_id int
)
AS
SELECT city_id, city_name
FROM City
WHERE state_id = @state_id
ALTER PROCEDURE [dbo].[SelectZip]
(
@city_id int
)
AS
SELECT zip_id, zip_name
FROM Zip
WHERE city_id = @city_id
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>
Can you post sample data from your city table?
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
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.
When I tested using embedded sql, everything works fine for me.
But I want to test with your stored procedures.
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
STORED PROCEDURE (SelectCity):
ALTER PROCEDURE [dbo].[SelectCity]
(
@state_id int
)
AS
SELECT city_id, city_name
FROM City
WHERE state_id = @state_id
STORED PROCEDURE (SelectZip):
ALTER PROCEDURE [dbo].[SelectZip]
(
@city_id int
)
AS
SELECT zip_id, zip_name
FROM Zip
WHERE city_id = @city_id
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
see screenshot.
I am now trying it with your version of code.
ccs.JPG
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@sammySeltzer,
Thank you. Your solution worked. I had to make the changes below for it to work with my code. Please see below.
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;
}
}
ASKER
THANK YOU!!