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.

Fill Country,State and cities in the DropDownList 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."
Previous
Next Post »

14 comments

Click here for comments
Rahul Sharma
admin
August 26, 2013 ×

shows an error :- invalid object name Tbl_State

Reply
avatar
August 26, 2013 ×

Hello Rahul..have you created the table "Tbl_State" ? and have Replaced the Data Source and Initial Catalog(i.e. Database name) as per your application in the connection string?.

Reply
avatar
Rahul Sharma
admin
August 26, 2013 ×

Hi Lalit and thanx for reply, yes i've created same table and DB name as per your instruction. and everything fine within connection string.. and application also running fine, but problem occur when i select a country name from ddlist. a text appear on top "Error occured: Invalid object name 'Tbl_State'. "

Reply
avatar
August 26, 2013 ×

seems there is typo error when you type Tbl_State. please check is it Tbl_State. here l for lalit in Tbl not(one). Also try to run the query select * from Tbl_State in sql server and notify me what is the results..

Reply
avatar
Rahul Sharma
admin
August 27, 2013 ×

yes there is l not 1(one) and i run the query select * from [Tbl_ State] it execute fine and shows the data within table.

Reply
avatar
August 27, 2013 ×

Hello rahul..i rechecked the code and its working file on my end. Send your sample project to me on my email lalit24rocks@gmail.com and i will check what is the problem on your end and resolve it.

Reply
avatar
Unknown
admin
September 28, 2013 ×

Sir your way is perfect but unfortunately when i've tried to impliment it in one of my page named as Registration page whenever user selects state or city at the same time password entered by user gets erased because of AUTOPOSTBACK is there any solution for it..???

Reply
avatar
September 28, 2013 ×

Hello Chiraag..you need to maintain it on postback as:

protected void Page_Load(object sender, EventArgs e)
{

if (IsPostBack)
{
if (!(String.IsNullOrEmpty(txtPassword.Text.Trim()))
{
txtPassword.Attributes["value"]= txtPassword.Text;
}
}
}

try and let me know..

Reply
avatar
Unknown
admin
October 24, 2013 ×

Hello Sir,
i want complete database for these three table i.e for first country table india is there and for second table it has india's all states then for city it has all the cities of each state...can u provide me this.
Thanks in advance

Reply
avatar
October 26, 2013 ×

Hello Raj..You have to search on google for this...

Reply
avatar
asif
admin
October 28, 2013 ×

bro, can you please tell me How can I implement that in WinForms application ? there is no property like DataTextField, DataValueField and DataBind(); method

Reply
avatar
sanju
admin
October 28, 2013 ×

hello sir,
I have to populate various text box to take data form user according to each selection of dropDown list.

Reply
avatar
Unknown
admin
September 13, 2014 ×

thanks for the code i will try it

Reply
avatar
September 14, 2014 ×

your welcome Sanjay...sure try it ..

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