HOW TO FILL COUNTRY,STATE,CITIES IN THE DROPDOWNLIST IN ASP.NET C#

Introduction: In this article i am going to explain the following:

• How to Bind /Load /Fill Countries, States and Cities in the DropDownList from Sql Server Database in asp.net using C# and Vb.net language
• How to Fill another DropDownList based on DropDownList item selection e.g. here in this example On the basis of selected Country corresponding States will be filled in the State DropDownList and on the basis of selected state corresponding cities will be filled in City DropDownList. 
• Cascade DropDownList example to fill city,state and country.

Implementation: Let's create the application to understand. First create a Database in Sql Server and name it "Emp_DB" or whatever you want. Now we need to create table for County, State and City. 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(i.e. Database name) as per your application.

• In the design page(.aspx) Place 3 DropDownList controls from the visual studio’s toolbar and design the form as:

<fieldset style="width:340px;">

    <legend>Fill City,State and Country DropDownList in asp.net</legend>

    <table>

    <tr>

        <td width="40%">Select Country:</td>

        <td><asp:DropDownList ID="ddlCountry" runat="server" AutoPostBack="True"

                onselectedindexchanged="ddlCountry_SelectedIndexChanged" Width="187px"></asp:DropDownList></td>

    </tr>

    <tr>

        <td>Select State:</td>

        <td><asp:DropDownList ID="ddlState" runat="server" AutoPostBack="True"

                onselectedindexchanged="ddlState_SelectedIndexChanged" Width="187px"></asp:DropDownList></td>

        </tr>

    <tr>

        <td>Select City:</td>

        <td> <asp:DropDownList ID="ddlCity" runat="server" Width="187px"></asp:DropDownList></td>

    </tr>

    </table>

        </fieldset>

C#.Net Code  to Fill Country, State and cities in the DropDownList.

• In the Code behind file (.aspx.cs) write the code as:

First include the following namespaces:

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

Then write the code as:

//Creating and initializing connection object.

    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString);

    protected void Page_Load(object sender, EventArgs e)

    {

        //Checking Connection State and opening if closed

        if (con.State == ConnectionState.Closed)

        {

            con.Open();

        }

        if (!Page.IsPostBack)

        {

            //Call countries DropDownList on page load event

            BindContriesDropDownList();

        }

    }

    protected void ddlCountry_SelectedIndexChanged(object sender, EventArgs e)

    {

        try

        {

            int CountryId = Convert.ToInt32(ddlCountry.SelectedValue);          

            //Select all States corresponding to the selected Country

            SqlDataAdapter adp = new SqlDataAdapter("select * from Tbl_State where Country_ID_Fk=" + CountryId, con);

            DataSet ds = new DataSet();

            adp.Fill(ds);           

            ddlState.DataSource = ds;

            ddlState.DataTextField = "State_Name";

            ddlState.DataValueField = "State_Id_Pk";

            ddlState.DataBind();

            ddlState.Items.Insert(0, new ListItem("--Select--", "0"));

            //If State is not selected then clear City DropDownList also

            if (ddlState.SelectedValue == "0")

            {

                ddlCity.Items.Clear();

                ddlCity.Items.Insert(0, new ListItem("--Select--", "0"));

            }

        }

        catch (Exception ex)

        {

            //Printing any exception if occcured.

            Response.Write("Error occured: " + ex.Message.ToString());

        }

        finally

        {

            //Close the connection

            con.Close();

        }      

    }

    protected void ddlState_SelectedIndexChanged(object sender, EventArgs e)

    {

        try

        {

            int StateId = Convert.ToInt32(ddlState.SelectedValue);

            //Select all Cities corresponding to the selected State

            SqlDataAdapter adp = new SqlDataAdapter("select * from Tbl_City where State_ID_Fk=" + StateId, con);

            DataSet ds = new DataSet();

            adp.Fill(ds);           

            ddlCity.DataSource = ds;

            ddlCity.DataTextField = "City_Name";

            ddlCity.DataValueField = "City_id_pk";

            ddlCity.DataBind();

            ddlCity.Items.Insert(0, new ListItem("--Select--", "0"));

        }

        catch (Exception ex)

        {

            Response.Write("Error occured : " + ex.Message.ToString());

        }

        finally

        {

            con.Close();

        }       

    }

    protected void BindContriesDropDownList()

    {

        try

        {           

            SqlDataAdapter adp = new SqlDataAdapter("select * from Tbl_Country", con);

            DataSet ds = new DataSet();

            adp.Fill(ds);         

            ddlCountry.DataSource = ds;

            ddlCountry.DataTextField = "Country_Name";

            ddlCountry.DataValueField = "Country_Id_Pk";

            ddlCountry.DataBind();

            ddlCountry.Items.Insert(0, new ListItem("--Select--", "0"));

            ddlState.Items.Insert(0, new ListItem("--Select--", "0"));

            ddlCity.Items.Insert(0, new ListItem("--Select--", "0"));

        }

        catch (Exception ex)

        {

            Response.Write("Error occured : " + ex.Message.ToString());

        }

        finally

        {

            con.Close();

        } 

    }