Here Mudassar Ahmed Khan here explained How to create a Cascading DropDownLists in ASP.Net that is DropDownLists using jQuery and AJAX which depend on the previous or parent DropDownList for the data
In my previous article I explained Creating Cascading DropDownLists in ASP.Net. This article is extension of my previous article as here I am using jQuery JavaScript Library to make the User Interface pleasant by making use of JQuery AJAX for the Cascading DropDownList functionality
Cascading DropDownList means a series of dependent DropDownLists where one DropDownList is dependent on the parent or previous DropDownList and is populated based on the item selected by the user. On many occasions we need to make use of Cascading DropDownLists as I have here
Continent - Country - City
City is dependent on Country and in turn Country is dependent on the Continent thus we need a series of Cascading DropDownList here.
Database Design
For this tutorial I have created three tables whose designs are given below
Continents Table
Countries Table
Cities Table
Connection String
<connectionStrings>
<addname="conString"connectionString="Data Source=.\SQLEXPRESS;
database=CascadingDDL;Integrated Security=true"/>
</connectionStrings>
Front End Design
I have used 3 DropDownLists one for each entity the mapping is shown below
1. ddlContinents - List of Continents
2. ddlCountries- List of Countries
3. ddlCities - List of Cities
Continents:<asp:DropDownList ID="ddlContinents" runat="server" AppendDataBoundItems="true"
onchange = "PopulateContinents();">
<asp:ListItem Text = "Please select" Value = "0"></asp:ListItem>
</asp:DropDownList>
<br /><br />
Country:<asp:DropDownList ID="ddlCountries" runat="server"
onchange = "PopulateCities();">
<asp:ListItem Text = "Please select" Value = "0"></asp:ListItem>
</asp:DropDownList>
<br /><br />
City:<asp:DropDownList ID="ddlCities" runat="server">
<asp:ListItem Text = "Please select" Value = "0"></asp:ListItem>
</asp:DropDownList>
<br />
<asp:Button ID="btnSubmit" runat="server" Text="Submit" OnClick = "Submit" />
Populating the Continents DropDownList
On the Page_Load Event of the Page I am populating the Continents DropDownList
C#
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
this.PopulateContinents();
}
}
private void PopulateContinents()
{
String strConnString = ConfigurationManager
.ConnectionStrings["conString"].ConnectionString;
String strQuery = "select ID, ContinentName from Continents";
using (SqlConnection con = new SqlConnection(strConnString))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = strQuery;
cmd.Connection = con;
con.Open();
ddlContinents.DataSource = cmd.ExecuteReader();
ddlContinents.DataTextField = "ContinentName";
ddlContinents.DataValueField = "ID";
ddlContinents.DataBind();
con.Close();
}
}
}
VB.Net
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
If Not IsPostBack Then
Me.PopulateContinents()
End If
End Sub
Private Sub PopulateContinents()
Dim strConnString As String = ConfigurationManager.ConnectionStrings("conString").ConnectionString
Dim strQuery As String = "select ID, ContinentName from Continents"
Dim con As SqlConnection = New SqlConnection(strConnString)
Dim cmd As SqlCommand = New SqlCommand
cmd.CommandType = CommandType.Text
cmd.CommandText = strQuery
cmd.Connection = con
con.Open()
ddlContinents.DataSource = cmd.ExecuteReader
ddlContinents.DataTextField = "ContinentName"
ddlContinents.DataValueField = "ID"
ddlContinents.DataBind()
con.Close()
End Sub
Populating Countries
I am populating the Countries DropDownList based on the ID of the Continent Selected by the user. The below method is declared as Web Method and Static (C#) Shared (VB) so that it can be called using jQuery.
For more information on calling server side methods using jQuery. Refer my article
C#
[System.Web.Services.WebMethod]
public static ArrayList PopulateCountries(int continentId)
{
ArrayList list = new ArrayList();
String strConnString = ConfigurationManager
.ConnectionStrings["conString"].ConnectionString;
String strQuery = "select ID, CountryName from Countries where ContinentID=@ContinentID";
using (SqlConnection con = new SqlConnection(strConnString))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@ContinentID", continentId);
cmd.CommandText = strQuery;
cmd.Connection = con;
con.Open();
SqlDataReader sdr = cmd.ExecuteReader();
while (sdr.Read())
{
list.Add(new ListItem(
sdr["CountryName"].ToString(),
sdr["ID"].ToString()
));
}
con.Close();
return list;
}
}
}
VB.Net
<System.Web.Services.WebMethod()> _
Public Shared Function PopulateCountries(ByVal continentId As Integer) As ArrayList
Dim list As ArrayList = New ArrayList
Dim strConnString As String = ConfigurationManager.ConnectionStrings("conString").ConnectionString
Dim strQuery As String = "select ID, CountryName from Countries where ContinentID=@ContinentID"
Dim con As SqlConnection = New SqlConnection(strConnString)
Dim cmd As SqlCommand = New SqlCommand
cmd.CommandType = CommandType.Text
cmd.Parameters.AddWithValue("@ContinentID", continentId)
cmd.CommandText = strQuery
cmd.Connection = con
con.Open()
Dim sdr As SqlDataReader = cmd.ExecuteReader
While sdr.Read
list.Add(New ListItem(sdr("CountryName").ToString, sdr("ID").ToString))
End While
con.Close()
Return list
End Function
You will notice I am passing the ID of the continent as parameter to the Query using SelectedItemValue property of the DropDownList thus the query returns the records (Countries) for that Continent ID which are then bind to the Country DropDownList
Populating the Cities
Now on the selection of the Country I am filling the Cities for that country into the City DropDownList. The following method is also a Web Method so that the same came be called up using jQuery AJAX.
C#
[System.Web.Services.WebMethod]
public static ArrayList PopulateCities(int countryId)
{
ArrayList list = new ArrayList();
String strConnString = ConfigurationManager
.ConnectionStrings["conString"].ConnectionString;
String strQuery = "select ID, CityName from Cities where CountryID=@CountryID";
using (SqlConnection con = new SqlConnection(strConnString))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@CountryID", countryId);
cmd.CommandText = strQuery;
cmd.Connection = con;
con.Open();
SqlDataReader sdr = cmd.ExecuteReader();
while (sdr.Read())
{
list.Add(new ListItem(
sdr["CityName"].ToString(),
sdr["ID"].ToString()
));
}
con.Close();
return list;
}
}
}
VB.Net
<System.Web.Services.WebMethod()> _
Public Shared Function PopulateCities(ByVal countryId As Integer) As ArrayList
Dim list As ArrayList = New ArrayList
Dim strConnString As String = ConfigurationManager.ConnectionStrings("conString").ConnectionString
Dim strQuery As String = "select ID, CityName from Cities where CountryID=@CountryID"
Dim con As SqlConnection = New SqlConnection(strConnString)
Dim cmd As SqlCommand = New SqlCommand
cmd.CommandType = CommandType.Text
cmd.Parameters.AddWithValue("@CountryID", countryId)
cmd.CommandText = strQuery
cmd.Connection = con
con.Open()
Dim sdr As SqlDataReader = cmd.ExecuteReader
While sdr.Read
list.Add(New ListItem(sdr("CityName").ToString, sdr("ID").ToString))
End While
con.Close()
Return list
End Function
Above I am firing a query on the Cities Table and getting all the cities that belong to that country which was selected by the user.
Populating Countries DropDownList Client Side
The following function gets called on the onchange event of Continents DropDownList. You will notice below that this function simply calls the PopulateCountries method with the ID of the selected Continent as parameter using jQuery AJAX that described earlier. And on OnSuccess event of the jQuery AJAX call the Countries DropDownList is populated. The pageUrl variable stores the URL of the page that will handle the jQuery AJAX calls. Note for VB.Net the URL will be set to VB.aspx
<script type = "text/javascript">
var pageUrl = '<%=ResolveUrl("~/CS.aspx")%>'
function PopulateContinents() {
$("#<%=ddlCountries.ClientID%>").attr("disabled", "disabled");
$("#<%=ddlCities.ClientID%>").attr("disabled", "disabled");
if ($('#<%=ddlContinents.ClientID%>').val() == "0") {
$('#<%=ddlCountries.ClientID %>').empty().append('<option selected="selected" value="0">Please select</option>');
$('#<%=ddlCities.ClientID %>').empty().append('<option selected="selected" value="0">Please select</option>');
}
else {
$('#<%=ddlCountries.ClientID %>').empty().append('<option selected="selected" value="0">Loading...</option>');
$.ajax({
type: "POST",
url: pageUrl + '/PopulateCountries',
data: '{continentId: ' + $('#<%=ddlContinents.ClientID%>').val() + '}',
contentType: "application/json; charset=utf-8",
dataType: "json",
success: OnCountriesPopulated,
failure: function(response) {
alert(response.d);
}
});
}
}
function OnCountriesPopulated(response) {
PopulateControl(response.d, $("#<%=ddlCountries.ClientID %>"));
}
</script>
Populating Cities DropDownList Client Side
In the similar way I am populating the Cities DropDownList on the onchange event of the Countries DropDownList, I am calling the PopulateCities method described earlier with selected country as the parameter. And on OnSuccess event of the jQuery AJAX call I am populating the cities DropDownList
<script type = "text/javascript">
function PopulateCities() {
$("#<%=ddlCities.ClientID%>").attr("disabled", "disabled");
if ($('#<%=ddlCountries.ClientID%>').val() == "0") {
$('#<%=ddlCities.ClientID %>').empty().append('<option selected="selected" value="0">Please select</option>');
}
else {
$('#<%=ddlCities.ClientID %>').empty().append('<option selected="selected" value="0">Loading...</option>');
$.ajax({
type: "POST",
url: pageUrl + '/PopulateCities',
data: '{countryId: ' + $('#<%=ddlCountries.ClientID%>').val() + '}',
contentType: "application/json; charset=utf-8",
dataType: "json",
success: OnCitiesPopulated,
failure: function(response) {
alert(response.d);
}
});
}
}
function OnCitiesPopulated(response) {
PopulateControl(response.d, $("#<%=ddlCities.ClientID %>"));
}
</script>
Populating the DropDownLists with data
The following method is used to populate the DropDownList with the data returned from the server. It accepts the list of data along with the reference of the control that needs to be populated.
<script type = "text/javascript">
function PopulateControl(list, control) {
if (list.length > 0) {
control.removeAttr("disabled");
control.empty().append('<option selected="selected" value="0">Please select</option>');
$.each(list, function() {
control.append($("<option></option>").val(this['Value']).html(this['Text']));
});
}
else {
control.empty().append('<option selected="selected" value="0">Not available<option>');
}
}
</script>
Fetching the Selections
On click event of button I am fetching the selected values server side. Since the Countries and Cities DropDownLists are populated client side their selections won’t be available directly and hence to get the values back we need to do the following
C#
protected void Submit(object sender, EventArgs e)
{
string continent = Request.Form[ddlContinents.UniqueID];
string country = Request.Form[ddlCountries.UniqueID];
string city = Request.Form[ddlCities.UniqueID];
// Repopulate Countries and Cities
PopulateDropDownList(PopulateCountries(int.Parse(continent)), ddlCountries);
PopulateDropDownList(PopulateCities(int.Parse(country)), ddlCities);
ddlCountries.Items.FindByValue(country).Selected = true;
ddlCities.Items.FindByValue(city).Selected = true;
}
VB.Net
Protected Sub Submit(ByVal sender As Object, ByVal e As EventArgs)
Dim continent As String = Request.Form(ddlContinents.UniqueID)
Dim country As String = Request.Form(ddlCountries.UniqueID)
Dim city As String = Request.Form(ddlCities.UniqueID)
' Repopulate Countries and Cities
PopulateDropDownList(PopulateCountries(Integer.Parse(continent)), ddlCountries)
PopulateDropDownList(PopulateCities(Integer.Parse(country)), ddlCities)
ddlCountries.Items.FindByValue(country).Selected = True
ddlCities.Items.FindByValue(city).Selected = True
End Sub
Above you will notice I am again populating the DropDownLists and then selecting the values back again so that we can preserve the user selections across postbacks. Below is the method that populates the DropDownLists server side.
C#
private void PopulateDropDownList(ArrayList list, DropDownList ddl)
{
ddl.DataSource = list;
ddl.DataTextField = "Text";
ddl.DataValueField = "Value";
ddl.DataBind();
}
VB.Net
Private Sub PopulateDropDownList(ByVal list As ArrayList, ByVal ddl As DropDownList)
ddl.DataSource = list
ddl.DataTextField = "Text"
ddl.DataValueField = "Value"
ddl.DataBind()
End Sub
The output is shown in the screenshot below
Thus we come to the end of this article. Download the sample source code in VB.Net and C# using the link below
CascadingDropDownListsJQuery.zip