Country, City, State Example With Database

Hi Guys, In this tutorial i will explain how to populate states according to the country selected and populate city according to the state i have selected.Here we are using DropDownList’s for all the three fields, For this we have to design three tables for Country,State and City.We will bind country DropDownList on the Page Load Event.

Database Design:

-----------Country Table----------
CREATE TABLE [dbo].[Country_List](
	[countryid] [float] NULL,
	[Country] [nvarchar](255) NULL
) ON [PRIMARY]
GO
---------State Table-----------
CREATE TABLE [dbo].[State](
	[Stateid] [float] NULL,
	[statename] [nvarchar](255) NULL,
	[CountryCode] [float] NULL
) ON [PRIMARY]
GO
--------City Table-------------
CREATE TABLE [dbo].[city](
	[cityid] [float] NULL,
	[stateid] [float] NULL,
	[City] [nvarchar](255) NULL
) ON [PRIMARY]
GO

Design View:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Hightechnology</title>
<style type="text/css">
.auto-style1 {
width: 900px;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<div>
<table align="center" cellpadding="2" class="auto-style1">
<tr>
<td>Country</td>
<td>
<asp:DropDownList ID="DropDownList1" AutoPostBack="true" OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged" runat="server">
</asp:DropDownList>
</td>
</tr>
<tr>
<td>State</td>
<td>
<asp:DropDownList ID="DropDownList2" OnSelectedIndexChanged="DropDownList2_SelectedIndexChanged" AutoPostBack="true" runat="server">
</asp:DropDownList>
</td>
</tr>
<tr>
<td>City</td>
<td>
<asp:DropDownList ID="DropDownList3" runat="server">
</asp:DropDownList>
</td>
</tr>
</table>
</div>
</form>
</body>
</html>

Code View:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

public partial class _Default : System.Web.UI.Page
{
    string strcon = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            Country();
        }
    }
    protected void Country()
    {   
        SqlConnection con = new SqlConnection(strcon);
        con.Open();
        SqlCommand cmd = new SqlCommand("select * from country_list", con);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        da.Fill(ds);
        con.Close();
        DropDownList1.DataSource = ds;
        DropDownList1.DataTextField = "Country";
        DropDownList1.DataValueField = "CountryID";
        DropDownList1.DataBind();
        DropDownList1.Items.Insert(0, new ListItem("--Select--", "0"));
        DropDownList2.Items.Insert(0, new ListItem("--Select--", "0"));
        DropDownList3.Items.Insert(0, new ListItem("--Select--", "0"));

    }
    protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
    {
        int CountryID = Convert.ToInt32(DropDownList1.SelectedValue);
        SqlConnection con = new SqlConnection(strcon);
        con.Open();
        SqlCommand cmd = new SqlCommand("select * from state where Countrycode=" + CountryID, con);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        da.Fill(ds);
        con.Close();
        DropDownList2.DataSource = ds;
        DropDownList2.DataTextField = "StateName";
        DropDownList2.DataValueField = "StateID";
        DropDownList2.DataBind();
        DropDownList2.Items.Insert(0, new ListItem("--Select--", "0"));

    }
    protected void DropDownList2_SelectedIndexChanged(object sender, EventArgs e)
    {
        int StateID = Convert.ToInt32(DropDownList2.SelectedValue);
        SqlConnection con = new SqlConnection(strcon);
        con.Open();
        SqlCommand cmd = new SqlCommand("select * from city where StateID=" + StateID, con);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        da.Fill(ds);
        con.Close();
        DropDownList3.DataSource = ds;
        DropDownList3.DataTextField = "city";
        DropDownList3.DataValueField = "cityid";
        DropDownList3.DataBind();
        DropDownList3.Items.Insert(0, new ListItem("--Select--", "0"));
    }
   
}