Please disable your adblock and script blockers to view this page
 

Ajax CascadingDropDown example in asp.net to Fill DropDownList with Countries,states and cities.

Introduction: In previous articles i explained How to Fill Country,State,Cities in the DropDownList and Ajax TabContainer example to create multiple tabs/panels and Ajax Accordion example to create Vertical DropDown menu in asp.net and Create contact us form/page and Ajax HtmlEditorExtender control to format textbox text and send formatted text in email and  Drag & drop to upload multiple files using AjaxFileUpload like Facebook in asp.net and Get age in years,months,days,hours and seconds from DOB in asp.net C#,Vb.Net and Ajax AutoCompleteExtender control example in asp.net C#,VB.Net using web service .
In this article I am going to explain the example of How to use Ajax CascadingDropDown to fill/Bind/Load Countries, states and cities in the DropDownList controls from the Sql Server database by defining web methods in the web service to fetch/get/read Countries, states and cities from database in asp.net using both C# and Vb.net Language.

Fill Country,State and City DropDownList using Ajax CascadeDropDown in asp.net
Click on image to enlarge
  Description: So basically you will learn the following:
  • How to use Ajax Cascading Dropdown control to fill country, state and city using web service.
  • How to Fill Country, state and city in the DropDownList from the Sql Server Database.
  • How to populate one dropdown based on other dropdown. e.g  Populate State dropdownlist based on country dropdownlist selection and similarly populating city dropdownlist based on state dropdownlist selection. 

 ImplementationLet's create the web application to understand. First create a Database in Sql Server and name it "Emp_DB" or whatever you want. Now we need to create tables for County, State and City. Create and Insert some data in all the tables as shown below.

  • Create the table and name it “Tbl_Country
Column Name
Data type

Country_Id_Pk
int
Set is identity=yes
Country_Name
varchar(100)


Tbl_Country table data
Country_Id_Pk
Country_Name
1
India
2
Australia

  • Create the table and name it  “Tbl_ State
Column Name
Data type

State_Id_pk
int
Set is identity=yes
State_Name
varchar(100)

country_Id_Fk
int


Tbl_State table data
State_Id_pk
State_Name
country_Id_Fk
1
Haryana
1
2
Punjab
1
3
Himachal Pradesh
1
4
Queensland
2

  • Create the table and name it  “Tbl_City
Column Name
Data type

City_Id_Pk
int
Set is identity=yes
City_Name
varchar(100)

State_Id_Fk
int



Tbl_City table data
City_Id_Pk
City_Name
State_Id_Fk
1
Panchkula
1
2
Kalka
1
3
Ambala
1
4
Moga
2
5
Bathinda
2
6
Shimla
3
7
kasauli
3
8
Brisbane
4
9
Townsville
4

  • In the web.config file create the connection string in <connectionString> element as:
<connectionStrings>
    <add name="conStr" connectionString="Data Source=LocalServer;Initial Catalog=Emp_DB;Integrated Security=True"/>
  </connectionStrings> 

Note: Replace the Data Source and Initial Catalog as per your application.
  • In the design page ( .aspx) place a Scriptmanager  control from the AJAX Extension category of the Visual Studio toolbox. And place 3 DropDownList controls and a Label control from the standard category of the Visual Studio toolbox and also place 3 CascadingDropDown controls from the AjaxControlToolkit. If you have not installed the AjaxControlToolkit then read the article How to install AjaxControlToolkit in Visual Studio.

<asp:ScriptManager ID="ScriptManager1" runat="server">
        </asp:ScriptManager>
    <fieldset style="width:340px;">
    <legend>Fill Country,State and City DropDownList in asp.net</legend>
    <table>
    <tr>
        <td width="40%">Select Country:</td>
        <td>
        <asp:DropDownList ID="ddlCountry" runat="server" 
                 Width="187px"></asp:DropDownList>

                </td>
        <asp:CascadingDropDown ID="csdCountry" runat="server"
        Category="Country"
        TargetControlID="ddlCountry"
         PromptText="-- Select Country --"
         LoadingText="[Loading Countries...]"
         ServiceMethod="FetchCountries"
         ServicePath="AjaxCascadingDropDown.asmx">
        </asp:CascadingDropDown>
    </tr>
    <tr>
        <td>Select State:</td>
        <td>
        <asp:DropDownList ID="ddlState" runat="server"
                Width="187px"></asp:DropDownList>
                <asp:CascadingDropDown ID="csdState" runat="server"
        ParentControlID="ddlCountry"
        Category="State"
        TargetControlID="ddlState"
         PromptText="-- Select State --"
         LoadingText="[Loading States...]"
         ServiceMethod="FetchStates"
         ServicePath="AjaxCascadingDropDown.asmx">
        </asp:CascadingDropDown>
                </td>
        </tr>
    <tr>
        <td>Select City:</td>
        <td>
        <asp:DropDownList ID="ddlCity" runat="server" Width="187px" AutoPostBack="True"
                onselectedindexchanged="ddlCity_SelectedIndexChanged"></asp:DropDownList>
        <asp:CascadingDropDown ID="csdCity" runat="server"
        ParentControlID="ddlState"
        Category="City"
        TargetControlID="ddlCity"
         PromptText="-- Select City --"
         LoadingText="[Loading Cities...]"
         ServiceMethod="FetchCities"
         ServicePath="AjaxCascadingDropDown.asmx">
        </asp:CascadingDropDown>
        </td>
    </tr>
    <tr>
        <td colspan="2">
            <asp:Label ID="lblStatus" runat="server" Text="" style="color: #006600"></asp:Label>
        </td>
    </tr>
    </table>
        </fieldset>

Note: In the very first line of the design page(.aspx) set the EnableEventvalidation to false. Like this
 <%@ Page Language="C#" EnableEventValidation="false" AutoEventWireup="true" CodeFile="AjaxCascade.aspx.cs" Inherits="AjaxCascade" %>
  
Note: Have you noticed the line <%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="asp" %> added automatically next to the very first line in the design page. Actually it registers the Ajax Control on placing CascadingDropDown control on design page.

  •  Now create the web service. Go to website menu -> Add New Item..-> select C# or Visual Basic language from the left pane and Select Web Service from the center pane and name it “AjaxCascadingDropDown.asmx”.

C#.Net Code to fill/Bind/Load Countries, states and cities in the DropDownList using Ajax CascadingDropDown control
The complete code in the code behind file "AjaxCascadingDropDown.cs" that is placed inside App_Code folder will be as:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using AjaxControlToolkit;
using System.Collections.Specialized;

[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
 [System.Web.Script.Services.ScriptService]
public class AjaxCascadingDropDown : System.Web.Services.WebService
{
    SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["conStr"].ToString());
  
    public AjaxCascadingDropDown () {

        //Uncomment the following line if using designed components
        //InitializeComponent();
    }

    [WebMethod]
    public CascadingDropDownNameValue[] FetchCountries(string knownCategoryValues, string category)
    {
        if (con.State == ConnectionState.Closed)
        {
            con.Open();
        }
        SqlCommand cmd = new SqlCommand("select * from Tbl_Country", con);  
        cmd.ExecuteNonQuery();
        SqlDataAdapter adp = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        adp.Fill(ds);
        con.Close();
      
        List<CascadingDropDownNameValue> countries = new List<CascadingDropDownNameValue>();
        foreach (DataRow dtRow in ds.Tables[0].Rows)
        {
            string CountryID = dtRow["Country_Id_Pk"].ToString();
            string CountryName = dtRow["Country_Name"].ToString();
            countries.Add(new CascadingDropDownNameValue(CountryName, CountryID));
        }
        return countries.ToArray();
    }

    [WebMethod]
    public CascadingDropDownNameValue[] FetchStates(string knownCategoryValues, string category)
    {
        int countryId;      
        StringDictionary strCountries = AjaxControlToolkit.CascadingDropDown.ParseKnownCategoryValuesString(knownCategoryValues);
        countryId = Convert.ToInt32(strCountries["Country"]);
        con.Open();
        SqlCommand cmd = new SqlCommand("select * from Tbl_State where Country_ID_Fk=@CountryID", con);
        cmd.Parameters.AddWithValue("@CountryID", countryId);
        cmd.ExecuteNonQuery();
        SqlDataAdapter dastate = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        dastate.Fill(ds);
        con.Close();      
        List<CascadingDropDownNameValue> states = new List<CascadingDropDownNameValue>();
        foreach (DataRow dtRow in ds.Tables[0].Rows)
        {
            string StateID = dtRow["State_Id_Pk"].ToString();
            string StateName = dtRow["State_Name"].ToString();
            states.Add(new CascadingDropDownNameValue(StateName, StateID));
        }
        return states.ToArray();
    }
  
    [WebMethod]
    public CascadingDropDownNameValue[] FetchCities(string knownCategoryValues, string category)
    {
        int stateId;     
        StringDictionary strStates = AjaxControlToolkit.CascadingDropDown.ParseKnownCategoryValuesString(knownCategoryValues);
        stateId = Convert.ToInt32(strStates["State"]);
        con.Open();
        SqlCommand cmd = new SqlCommand("select * from Tbl_City where State_ID_Fk=@StateID", con);
        cmd.Parameters.AddWithValue("@StateID", stateId);
        cmd.ExecuteNonQuery();
        SqlDataAdapter daregion = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        daregion.Fill(ds);
        con.Close();     
        List<CascadingDropDownNameValue> cities = new List<CascadingDropDownNameValue>();
        foreach (DataRow dtRow in ds.Tables[0].Rows)
        {
            string RegionID = dtRow["City_id_pk"].ToString();
            string RegionName = dtRow["City_Name"].ToString();
            cities.Add(new CascadingDropDownNameValue(RegionName, RegionID));
        }
        return cities.ToArray();
    }   
}

Note: In the code behind file(.aspx.cs) write the following code on selectedindexchanged event of the City dropdownlist to print the selected Country,State and City as:

protected void ddlCity_SelectedIndexChanged(object sender, EventArgs e)
    {
        lblStatus.Text = "You have chosen Country: " + ddlCountry.Text + ", State: " + ddlState.Text + ", City:  " + ddlCity.Text;
    }

VB.Net Code to fill/Bind/Load Countries, states and cities in the DropDownList using Ajax CascadingDropDown control

The complete code in the code behind file "AjaxCascadingDropDown.vb" that is placed inside App_Code folder will be as:

Imports System.Web
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports AjaxControlToolkit
Imports System.Collections.Specialized

' To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
<System.Web.Script.Services.ScriptService()> _
<WebService(Namespace:="http://tempuri.org/")> _
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _
Public Class AjaxCascadingDropDown
    Inherits System.Web.Services.WebService

    Dim con As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("conStr").ToString())

    <WebMethod()> _
    Public Function HelloWorld() As String
        Return "Hello World"
    End Function

    <WebMethod()> _
    Public Function FetchCountries(knownCategoryValues As String, category As StringAs CascadingDropDownNameValue()
        If con.State = ConnectionState.Closed Then
            con.Open()
        End If
        Dim cmd As New SqlCommand("select * from Tbl_Country", con)
        cmd.ExecuteNonQuery()
        Dim adp As New SqlDataAdapter(cmd)
        Dim ds As New DataSet()
        adp.Fill(ds)
        con.Close()

        Dim countries As New List(Of CascadingDropDownNameValue)()
        For Each dtRow As DataRow In ds.Tables(0).Rows
            Dim CountryID As String = dtRow("Country_Id_Pk").ToString()
            Dim CountryName As String = dtRow("Country_Name").ToString()
            countries.Add(New CascadingDropDownNameValue(CountryName, CountryID))
        Next
        Return countries.ToArray()
    End Function

    <WebMethod()> _
    Public Function FetchStates(knownCategoryValues As String, category As StringAs CascadingDropDownNameValue()
        Dim countryId As Integer
        Dim strCountries As StringDictionary = AjaxControlToolkit.CascadingDropDown.ParseKnownCategoryValuesString(knownCategoryValues)
        countryId = Convert.ToInt32(strCountries("Country"))
        con.Open()
        Dim cmd As New SqlCommand("select * from Tbl_State where Country_ID_Fk=@CountryID", con)
        cmd.Parameters.AddWithValue("@CountryID", countryId)
        cmd.ExecuteNonQuery()
        Dim dastate As New SqlDataAdapter(cmd)
        Dim ds As New DataSet()
        dastate.Fill(ds)
        con.Close()
        Dim states As New List(Of CascadingDropDownNameValue)()
        For Each dtRow As DataRow In ds.Tables(0).Rows
            Dim StateID As String = dtRow("State_Id_Pk").ToString()
            Dim StateName As String = dtRow("State_Name").ToString()
            states.Add(New CascadingDropDownNameValue(StateName, StateID))
        Next
        Return states.ToArray()
    End Function

    <WebMethod()> _
    Public Function FetchCities(knownCategoryValues As String, category As StringAs CascadingDropDownNameValue()
        Dim stateId As Integer
        Dim strStates As StringDictionary = AjaxControlToolkit.CascadingDropDown.ParseKnownCategoryValuesString(knownCategoryValues)
        stateId = Convert.ToInt32(strStates("State"))
        con.Open()
        Dim cmd As New SqlCommand("select * from Tbl_City where State_ID_Fk=@StateID", con)
        cmd.Parameters.AddWithValue("@StateID", stateId)
        cmd.ExecuteNonQuery()
        Dim daregion As New SqlDataAdapter(cmd)
        Dim ds As New DataSet()
        daregion.Fill(ds)
        con.Close()
        Dim cities As New List(Of CascadingDropDownNameValue)()
        For Each dtRow As DataRow In ds.Tables(0).Rows
            Dim RegionID As String = dtRow("City_id_pk").ToString()
            Dim RegionName As String = dtRow("City_Name").ToString()
            cities.Add(New CascadingDropDownNameValue(RegionName, RegionID))
        Next
        Return cities.ToArray()
    End Function
End Class

Note: In the code behind file(.aspx.cs) write the following code on selectedindexchanged event of the City dropdownlist to print the selected Country,State and City as:

Protected Sub ddlCity_SelectedIndexChanged(ByVal sender As ObjectByVal e As System.EventArgsHandles ddlCity.SelectedIndexChanged
        lblStatus.Text = "You have chosen Country: " & ddlCountry.Text & ", State: " & ddlState.Text & ", City:  " & ddlCity.Text
    End Sub

Now over to you:
"If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linked in and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates."
Previous
Next Post »

11 comments

Click here for comments
Anonymous
admin
September 26, 2013 ×

Thanks for the post! Easy to follow and I had no problems!

Reply
avatar
September 26, 2013 ×

great !!!! i am glad this article helped you...keep reading for more useful updates..

Reply
avatar
Gregg
admin
October 04, 2013 ×

Your example is easy to follow UP TO THE POINT where the green label is displayed. This seems to be an OFTEN OVERLOOKED step when anyone leaves a "SAMPLE" of CascadingDropDowns. It is the ONLY part I DO NOT get. HOW DID YOU DO IT? Could you publish THAT code as well? Thanks.

Reply
avatar
October 04, 2013 ×

Hi, by mistake i skipped that code in this article..Now it is updated in the article..you just need to add the following code in the code behind file as:
protected void ddlCity_SelectedIndexChanged(object sender, EventArgs e)
{
lblStatus.Text = "You have chosen Country: " + ddlCountry.Text + ", State: " + ddlState.Text + ", City: " + ddlCity.Text;
}

let me know if you face any problem..

Reply
avatar
burning.bits
admin
March 04, 2014 ×

Lalit Raghuvanshi thanks for sharing your work, your website is one of the best across the network, I am always checking your updates, congratulations you do an excellent job, thanks.

Reply
avatar
March 04, 2014 ×

Hi burning.bits..thanks for appreciating my work and your feedback..it is always nice to hear that my website helped anyone..stay connected and keep reading for more useful updates..:)

Reply
avatar
Ishu nanda
admin
March 18, 2014 ×

Not sure why but data is not populating in the dropdown.. Any ideas why? I followed all the steps you mentioned above?

Reply
avatar
March 18, 2014 ×

Hello Ishu nanda...i suggest you to recheck and try once again..if still you face problem then let me know..i will help you to sort out the issue..:)

Reply
avatar
Ishu nanda
admin
March 19, 2014 ×

Thanks for responding.. I think the issue is with connection string in web config.. Since im new to this, not sure if iam doing this correct.. Could you please assist..

I am putting this in web config -

Is this correct? I am using SQL server.. How would i know the data source that is being used in my application?

Everything else is working fine. Thanks

Reply
avatar
March 19, 2014 ×

Hi Ishu nanda...When you launch sql server management studio...then notice the name of the server..It is the Data source..it still you didn't get it then let me know..

Reply
avatar
Unknown
admin
January 26, 2016 ×

Thank you for this wonderful tutorial. I have been following all of your tutorial and learning. In the ddlCity_SelectedIndexChanged, I had to change dd1County.Text, ddlState.Text, and ddlCity.Text to .SelectedItem.ToString() to show the text of each selected ddl. The ddl.Text only show numbers.

Reply
avatar

If you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily.. ConversionConversion EmoticonEmoticon