How to bind state categories and cities sub categories in single dropdownlist in asp.net

Introduction:  In this article i am going to explain with example How to Bind/ Load/ Fill Category e.g. States and Sub category e.g. Cities Corresponding to States in a single DropDownList in asp.net with both C# and VB.Net language. 

Bind state Catogory and City sub category in single asp.net DropDownList
Click on image to enlarge
Description: While working on asp.net website i got the requirement of filling Categories and their corresponding sub categories in the single DropDownList. It was one of the common requirement while working on web application to show the category and sub categories in the single dropdownlist so that it will be easy for the user to select the appropriate item. E.g. here in this example i have demonstrated the example of state and cities in dropdownlist. Cities will be categorized based on the States. So it will be user friendly to just look for his state and select his city.

  • First of all create a Database in Sql server e.g."MyDataBase" and create a table with the Columns and Data Type as shown in fig below and name it "Tb_States":
  
Column Name
Data Type
STATE_ID_PK
Int(Primary key. So set Is Identity=True)
STATE_NAME
varchar(100)




  •  Open the Table "Tb_States" and enter the data as:

STATE_ID_PK
STATE_NAME
1
Haryana
2
Himachal
3
Punjab

  • Also create a table "Tb_Cities" as:

Column Name
Data Type
CITY_ID_PK
Int(Primary key. So set Is Identity=True)
STATE_ID_FK
Int
CITY_NAME
varchar(100)
  
  • Open the Table "Tb_Cities" and enter the data as:

CITY_ID_PK
STATE_ID_FK
CITY_NAME
1
1
Panchkula
2
1
Ambala
3
1
Sirsa
4
1
Gurgoan
5
2
Dharamshala
6
2
Chamba
7
2
Manali
8
2
Shimla
9
3
Patiala
10
3
Amritsar
11
3
Ludhiana

  •  Now create a stored procedure to fill States and Cities in the Single DropDownList as:
CREATE PROCEDURE FillCities_Sp               
AS
BEGIN 
                SET NOCOUNT ON;
                SELECT '--> ' + CITY_NAME as CityName,CITY_ID_PK,STATE_ID_FK FROM Tb_Cities
                UNION SELECT UPPER(STATE_NAME),-1,STATE_ID_PK FROM Tb_States ORDER BY STATE_ID_FK,CITY_ID_PK
END 
  • In the web.config file create the connection string to connect the Sql server database with our asp.net web application as:
<connectionStrings>
    <add name="MyDbCon" connectionString="Data Source=LALIT;Initial Catalog=MyDataBase;Integrated Security=True"/>
  </connectionStrings>

Note: replace the Data Source and the Initial catalog as per your application.
C#.Net Section

  • In the <Form> tag of Design page(.aspx) design the page as:
<div>
    <fieldset style="width:300px; height: 290px;">
    <legend>Fill States and Cities in Dropdowlist</legend>   
    <asp:DropDownList ID="ddlCities" runat="server" AutoPostBack="True"
            onselectedindexchanged="ddlCities_SelectedIndexChanged" Width="171px"></asp:DropDownList>
            <br />       
        <asp:Label ID="lblMsg" runat="server" Text="" style="color: #009933"></asp:Label>
     </fieldset>
    </div>

C#.Net Code to bind category and sub category in single DropDownList
  • In the code behind file(.aspx.cs) write the code as:
But first include the following namespaces and then write the code:
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["MyDbCon"].ConnectionString);
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            FillDeptDropdownList();
        }

        foreach (ListItem item in ddlCities.Items)
            {
                if (item.Value.ToString() == "-1")
                {
                    item.Attributes.Add("Style""color:orange");
                    item.Attributes.Add("Disabled""true");
                }
                else
                {
                    item.Attributes.Add("Style""color:#888");
                }
            }       
    }

    protected void FillDeptDropdownList()
    {
        DataTable dt = new DataTable();
        SqlDataAdapter adp = new SqlDataAdapter();
        try
        {
            SqlCommand cmd = new SqlCommand("FillCities_Sp", con);
            cmd.CommandType = CommandType.StoredProcedure;
            adp = new SqlDataAdapter(cmd);
            adp.Fill(dt);
            ddlCities.DataSource = dt;
            ddlCities.DataTextField = "CityName";
            ddlCities.DataValueField = "CITY_ID_PK";
            ddlCities.DataBind();
            ddlCities.Items.Insert(0, "-- Select City --");       
        }
        catch(Exception ex)       
        {
            Response.Write("Oops!! error occured : " + ex.Message.ToString());
        }
        finally
        {
            con.Close();
            adp.Dispose();
            dt.Clear();
            dt.Dispose();
        }       
    }

    protected void ddlCities_SelectedIndexChanged(object sender, EventArgs e)
    {
        string str = ddlCities.SelectedItem.Text;
        string strCity= str.Remove(0, 4); // removing  -- > from the City Name
        lblMsg.Text = "Selected City: " + strCity + " & Selected Value: " + ddlCities.SelectedValue;        
    }

 VB.Net Code to bind category and sub category in single DropDownList
  • Design the page same as described in the C#.Net section but replace the line <asp:DropDownList ID="ddlCities" runat="server" AutoPostBack="True"            onselectedindexchanged="ddlCities_SelectedIndexChanged" Width="171px"></asp:DropDownList>

with the line
<asp:DropDownList ID="ddlCities" runat="server" AutoPostBack="True"
            Width="171px"></asp:DropDownList>

  • Then in the code behind file (.aspx.vb) write the code as:
First import the following namespaces and then write the code as:

Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration

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

    Protected Sub Page_Load(ByVal sender As ObjectByVal e As System.EventArgsHandles Me.Load
        If Not Page.IsPostBack Then
            FillDeptDropdownList()
        End If

        For Each item As ListItem In ddlCities.Items
            If item.Value.ToString() = "-1" Then
                item.Attributes.Add("Style""color:orange")
                item.Attributes.Add("Disabled""true")
            Else
                item.Attributes.Add("Style""color:#888")
            End If
        Next
    End Sub

    Protected Sub FillDeptDropdownList()
        Dim dt As New DataTable()
        Dim adp As New SqlDataAdapter()
        Try
            Dim cmd As New SqlCommand("FillCities_Sp", con)
            cmd.CommandType = CommandType.StoredProcedure
            adp = New SqlDataAdapter(cmd)
            adp.Fill(dt)
            ddlCities.DataSource = dt
            ddlCities.DataTextField = "CityName"
            ddlCities.DataValueField = "CITY_ID_PK"
            ddlCities.DataBind()
            ddlCities.Items.Insert(0, "-- Select City --")
        Catch ex As Exception
            Response.Write("Oops!! error occured : " & ex.Message.ToString())
        Finally
            con.Close()
            adp.Dispose()
            dt.Clear()
            dt.Dispose()
        End Try
    End Sub

    Protected Sub ddlCities_SelectedIndexChanged(ByVal sender As ObjectByVal e As System.EventArgsHandles ddlCities.SelectedIndexChanged
        Dim str As String = ddlCities.SelectedItem.Text
        ' removing  -- > from the City Name
        Dim strCity As String = str.Remove(0, 4)
        lblMsg.Text = "Selected City: " & strCity & " & Selected Value: " + ddlCities.SelectedValue
    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 »

11 comments

Click here for comments
Unknown
admin
October 16, 2013 ×

Hey this is a very nice code but can u please suggest how to implement it for country,state and city. Any suggestions would be appreciated.

Reply
avatar
October 16, 2013 ×

Hi Akshay Barve..thanks for appreciating my work..i will create an article as per your requirement and publish very soon..so stay connected and keep reading for more useful updates like this.. :)

Reply
avatar
Unknown
admin
October 18, 2013 ×

Hello sir. Its Good Job. I am praying for you to do a excellent work for us. Keep it Up.Thanks Sir
You are Genius. I am waiting for your next article

Reply
avatar
October 18, 2013 ×

Thanks Faisal Hasan for appreciating my work..stay connected for more useful updates like this..:)

Reply
avatar
November 11, 2013 ×

this is really awesome article I learn something new today thanks sir . I hope I will find every article in this blog that help me to join a any big software company
Presently I am working in a small it company and from last one year i did not do work on big project Now i am following your blog from last few days. I really hope that i get everything here which is useful for me to be a good web developer :)
thanks lalit sir

Reply
avatar
November 11, 2013 ×

Hi, Azad Chouhan..It is always nice to hear that my articles helped anyone..I suggest you to read my articles as much as you can..it will definitely help you clearing the concepts and become good developer and join big IT company..Best of luck for your future..:)

Reply
avatar
Unknown
admin
December 04, 2013 ×

Thanks you very much

Reply
avatar
December 05, 2013 ×

Your welcome Pawan..stay connected and keep reading..:)

Reply
avatar
Unknown
admin
January 05, 2014 ×

Thank you for your great effort you are excellent man i like people that help people , please can you send me how to make it n country , state , city. Thank you very much

Reply
avatar
January 09, 2014 ×

Thanks for you .. Great work....

Reply
avatar
January 09, 2014 ×

Thanks jayaprakash for appreciating my work..stay connected and keep reading..:)

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