Home AJAX DropDownList Country-State-City Cascading using Ajax

DropDownList Country-State-City Cascading using Ajax

139
0
DropDownList Country-State-City Cascading using Ajax
DropDownList Country-State-City Cascading using Ajax

Now here in this tutorial, I’ll explain how to bind dropdownlist country-state-city cascading using ajax without page refresh that depends on one another in asp.net using c# as well as vb.net with example code.

If you check my  previous tutorials, I’d explained about simpe country-state-city cascading for dropdownlist, ajax introduction with example, dynamically bind and show data in dropdownlist, and other amazing tutorials on DropDownList, GridView, and Asp.net

To explain further about cascading asp.net dropdownlist for country-state-city, we need to create database table to get data and bind retrieved resultset to dropdown list based on dropdown selections. So check out the following image that shows the relationship between country-state-city tables one another. If you want the database tables sample script, download sample code at the end of the tutorial else do it by yourself.

Cascading Country State & Ciity Database Relationship Example
Cascading Country State & Ciity Database Relationship Example

Asp.net DropDownList Country-State-City Cascading – [.aspx]

Following is the complete HTML Markup code for .aspx page that I use for this tutorial:

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title>Ajax bind cascading country-state-city dropdownlist without page refresh example in asp.net</title>
</head>
<body>
    <form id="form1" runat="server">
        <asp:ScriptManager ID="ScriptManager1" runat="server" />
        <div>
            <h4>Ajax bind cascading country-state-city dropdownlist without page refresh</h4>
            <asp:UpdatePanel ID="UpdatePanel1" runat="server">
                <ContentTemplate>
                    <table>
                        <tr>
                            <td>Select Country:
                                <asp:DropDownList ID="ddlCountry" runat="server" AutoPostBack="true"
                                    OnSelectedIndexChanged="ddlCountry_SelectedIndexChanged">
                                </asp:DropDownList>
                            </td>
                        </tr>
                        <tr>
                            <td>Select State:
                                <asp:DropDownList ID="ddlState" runat="server" AutoPostBack="true"
                                    OnSelectedIndexChanged="ddlState_SelectedIndexChanged">
                                </asp:DropDownList>
                            </td>
                        </tr>
                        <tr>
                            <td>Select City:
                                <asp:DropDownList ID="ddlCity" runat="server">
                                </asp:DropDownList>
                            </td>
                        </tr>
                    </table>
                    <asp:UpdateProgress ID="UpdateProgress1" AssociatedUpdatePanelID="UpdatePanel1"
                        runat="server">
                        <ProgressTemplate>
                            <div id="ajaxloader">
                                Loading..
                            </div>
                        </ProgressTemplate>
                    </asp:UpdateProgress>
                </ContentTemplate>
                <Triggers>
                    <asp:AsyncPostBackTrigger ControlID="ddlCountry" EventName="SelectedIndexChanged" />
                    <asp:AsyncPostBackTrigger ControlID="ddlState" EventName="SelectedIndexChanged" />
                </Triggers>
            </asp:UpdatePanel>
        </div>
    </form>
</body>
</html>

As you can see from above example, I used <asp:ScriptManager /> and <asp:UpdatePanel /> under form tag to use Ajax. I also defined <asp:AsyncPostBackTrigger /> under <Triggers /> tag of UpdatePanel which triggers DropDownList’s OnSelectedIndexChanged event without page refresh. Now check the below sample code snippet to bind cascading country-state-city of asp.net dropdown list.

Asp.net DropDownList Country-State-City Cascading In C#

Now add the following namespace that is required to connect with SQL server:

using System.Data;
using System.Data.SqlClient;

After that add the following code to .aspx.cs page:

//specify your connection string here..
public static string strConn = @"Data Source=datasource;Integrated Security=true;Initial Catalog=database";

protected void Page_Load(object sender, EventArgs e) {
    if (!IsPostBack) {
        BindCountryList();
    }
}

//bind countries to country dropdownlist
private void BindCountryList() {
    try {
        using (SqlConnection sqlConn = new SqlConnection(strConn)) {
            using (SqlCommand sqlCmd = new SqlCommand()) {
                sqlCmd.CommandText = "SELECT CountryId,CountryName FROM Country";
                sqlCmd.Connection = sqlConn;
                sqlConn.Open();
                SqlDataAdapter da = new SqlDataAdapter(sqlCmd);
                DataTable dt = new DataTable();
                da.Fill(dt);
                ddlCountry.DataSource = dt;
                ddlCountry.DataValueField = "CountryId";
                ddlCountry.DataTextField = "CountryName";
                ddlCountry.DataBind();
                sqlConn.Close();

                //Adding "Please select country" option in dropdownlist
                ddlCountry.Items.Insert(0, new ListItem("Please select country", "0"));

                //Adding initially value to "state" and "city" dropdownlist
                ddlState.Items.Insert(0, new ListItem("Please select state", "0"));
                ddlCity.Items.Insert(0, new ListItem("Please select city", "0"));
            }
        }
    } catch { }
}

//bind states to state dropdownlist on country change event
protected void ddlCountry_SelectedIndexChanged(object sender, EventArgs e) {
    try {
        using (SqlConnection sqlConn = new SqlConnection(strConn)) {
            using (SqlCommand sqlCmd = new SqlCommand()) {
                sqlCmd.CommandText = "SELECT StateId,StateName FROM State
            WHERE CountryId = @CountryId";
            sqlCmd.Parameters.AddWithValue("@CountryId", ddlCountry.SelectedValue);
                sqlCmd.Connection = sqlConn;
                sqlConn.Open();
                SqlDataAdapter da = new SqlDataAdapter(sqlCmd);
                DataTable dt = new DataTable();
                da.Fill(dt);
                ddlState.DataSource = dt;
                ddlState.DataValueField = "StateId";
                ddlState.DataTextField = "StateName";
                ddlState.DataBind();
                sqlConn.Close();

                //Adding "Please select state" option in dropdownlist
                ddlState.Items.Insert(0, new ListItem("Please select state", "0"));

                //also clear city dropdownlist because we are changing country
                ddlCity.Items.Clear();
                ddlCity.Items.Insert(0, new ListItem("Please select city", "0"));
            }
        }
    } catch { }
}

//bind cities to city dropdownlist on state change event
protected void ddlState_SelectedIndexChanged(object sender, EventArgs e) {
    try {
        using (SqlConnection sqlConn = new SqlConnection(strConn)) {
            using (SqlCommand sqlCmd = new SqlCommand()) {
                sqlCmd.CommandText = "SELECT CityId,CityName FROM City WHERE StateId = @StateId";
            sqlCmd.Parameters.AddWithValue("@StateId", ddlState.SelectedValue);
                sqlCmd.Connection = sqlConn;
                sqlConn.Open();
                SqlDataAdapter da = new SqlDataAdapter(sqlCmd);
                DataTable dt = new DataTable();
                da.Fill(dt);
                ddlCity.DataSource = dt;
                ddlCity.DataValueField = "CityId";
                ddlCity.DataTextField = "CityName";
                ddlCity.DataBind();
                sqlConn.Close();

                //Adding "Please select city" option in dropdownlist
                ddlCity.Items.Insert(0, new ListItem("Please select city", "0"));
            }
        }
    } catch { }
}

Asp.net DropDownList Country-State-City Cascading In Vb.net

Now add the following namespace that is required to connect with sql server:

Imports System.Data.SqlClient

After that add the following code to .aspx.vb page:

'specify your connection string here..'
Public Shared strConn As String = "Data Source=datasource;Integrated Security=true;Initial Catalog=database"
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    If Not IsPostBack Then
        BindCountryList()
    End If
End Sub

'bind countries to country dropdownlist'
Private Sub BindCountryList()
    Try
        Using sqlConn As New SqlConnection(strConn)
            Using sqlCmd As New SqlCommand()
                sqlCmd.CommandText = "SELECT CountryId,CountryName FROM Country"
                sqlCmd.Connection = sqlConn
                sqlConn.Open()
                Dim da As New SqlDataAdapter(sqlCmd)
                Dim dt As New DataTable()
                da.Fill(dt)
                ddlCountry.DataSource = dt
                ddlCountry.DataValueField = "CountryId"
                ddlCountry.DataTextField = "CountryName"
                ddlCountry.DataBind()
                sqlConn.Close()

                'Adding "Please select country" option in dropdownlist'
                ddlCountry.Items.Insert(0, New ListItem("Please select country", "0"))

                'Adding initially value to "state" and "city" dropdownlist'
                ddlState.Items.Insert(0, New ListItem("Please select state", "0"))
                ddlCity.Items.Insert(0, New ListItem("Please select city", "0"))
            End Using
        End Using
    Catch
    End Try
End Sub

'bind states to state dropdownlist on country change event'
Protected Sub ddlCountry_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs)
    Try
        Using sqlConn As New SqlConnection(strConn)
            Using sqlCmd As New SqlCommand()
                sqlCmd.CommandText = "SELECT StateId,StateName FROM State WHERE CountryId=@CountryId"
                sqlCmd.Parameters.AddWithValue("@CountryId", ddlCountry.SelectedValue)
                sqlCmd.Connection = sqlConn
                sqlConn.Open()
                Dim da As New SqlDataAdapter(sqlCmd)
                Dim dt As New DataTable()
                da.Fill(dt)
                ddlState.DataSource = dt
                ddlState.DataValueField = "StateId"
                ddlState.DataTextField = "StateName"
                ddlState.DataBind()
                sqlConn.Close()

                'Adding "Please select state" option in dropdownlist'
                ddlState.Items.Insert(0, New ListItem("Please select state", "0"))

                'also clear city dropdownlist because we are changing country'
                ddlCity.Items.Clear()
                ddlCity.Items.Insert(0, New ListItem("Please select city", "0"))
            End Using
        End Using
    Catch
    End Try
End Sub

'bind cities to city dropdownlist on state change event'
Protected Sub ddlState_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs)
    Try
        Using sqlConn As New SqlConnection(strConn)
            Using sqlCmd As New SqlCommand()
                sqlCmd.CommandText = "SELECT CityId,CityName FROM City WHERE StateId=@StateId"
                sqlCmd.Parameters.AddWithValue("@StateId", ddlState.SelectedValue)
                sqlCmd.Connection = sqlConn
                sqlConn.Open()
                Dim da As New SqlDataAdapter(sqlCmd)
                Dim dt As New DataTable()
                da.Fill(dt)
                ddlCity.DataSource = dt
                ddlCity.DataValueField = "CityId"
                ddlCity.DataTextField = "CityName"
                ddlCity.DataBind()
                sqlConn.Close()

                'Adding "Please select city" option in dropdownlist'
                ddlCity.Items.Insert(0, New ListItem("Please select city", "0"))
            End Using
        End Using
    Catch
    End Try
End Sub

Example Result

DropDownList Country-State-City Cascading using Ajax
DropDownList Country-State-City Cascading using Ajax

Download Example

Git Repo

Previous articleDropDownList Cascading: Country-State-City Dependency in Asp.net
Next articleBreadCrumb: Asp.net SiteMapPath Navigation Control Example
Hi there, I am Mayank, the man behind Technical Mack. I started AspnetO with a motive to educate people on various programming languages ranging from beginners to expert level. Through this blog, I aim to provide more insightful content.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

fourteen − 4 =