How to Fill Country,State,Cities in the DropDownList in asp.net C#,VB.Net

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.

In previous articles i explained How to Get city, state and country based on zip code using Google map API in asp.net and Ajax CascadingDropDown example in asp.net to Fill DropDownList with Countries,states and cities and Bind state categories and cities sub categories in single dropdownlist and How to fill dropdownlist with days, month and year in asp.net(C#, VB) and Fill CheckBoxList based on DropDownList selection and Example to Validate DropDownList using jQuery in asp.net.

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();

        } 

    }

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

• In the design page(.aspx) Place 3 DropDownList controls  from the visual studio’s toolbar and design the page 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"

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

    </tr>

    <tr>

        <td>Select State:</td>

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

                 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>

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

First import the following namerspaces:

Imports System.Data

Imports System.Data.SqlClient

Imports System.Configuration

Then write the code as:

'Creating and initializing connection object.

    Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("conStr").ConnectionString)

    Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load

        'Checking Connection State and opening if closed

        If con.State = ConnectionState.Closed Then

            con.Open()

        End If

        If Not Page.IsPostBack Then

            'Call countries DropDownList on page load event

            BindContriesDropDownList()

        End If

    End Sub

    Protected Sub ddlCountry_SelectedIndexChanged(sender As Object, e As System.EventArgs) Handles ddlCountry.SelectedIndexChanged

        Try

            Dim CountryId As Integer = Convert.ToInt32(ddlCountry.SelectedValue)

            'Select all States corresponding to the selected Country

            Dim adp As New SqlDataAdapter("select * from Tbl_State where Country_ID_Fk=" & CountryId, con)

            Dim ds As 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" Then

                ddlCity.Items.Clear()

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

            End If

        Catch ex As Exception

            'Printing any exception if occcured.

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

        Finally

            'Close the connection

            con.Close()

        End Try

    End Sub

    Protected Sub ddlState_SelectedIndexChanged(sender As Object, e As System.EventArgs) Handles ddlState.SelectedIndexChanged

        Try

            Dim StateId As Integer = Convert.ToInt32(ddlState.SelectedValue)

            'Select all Cities corresponding to the selected State

            Dim adp As New SqlDataAdapter("select * from Tbl_City where State_ID_Fk=" & StateId, con)

            Dim ds As 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 ex As Exception

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

        Finally

            con.Close()

        End Try

    End Sub

    Protected Sub BindContriesDropDownList()

        Try

            Dim adp As New SqlDataAdapter("select * from Tbl_Country", con)

            Dim ds As 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 ex As Exception

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

        Finally

            con.Close()

        End Try

    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."

• Tweet
• Share
• Share
• Share

Related Post