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. 

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.

 Implementation: In previous articles i explained Get city, state and country based on zip code using Google map API and Ajax CascadingDropDown example in asp.net to Fill DropDownList with Countries,states and cities and Fill dropdownlist with days, month and year  and Validate DropDownList using jQuery and Fill CheckBoxList based on DropDownList selection and Retain password value in the asp.net TextBox after postback event and Get DropDownList selected value and Fill details in GridView and labels and Now Let's create a sample web application to see this example in action.

• 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 Object, ByVal e As System.EventArgs) Handles 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 Object, ByVal e As System.EventArgs) Handles 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."

• Tweet
• Share
• Share
• Share

Related Post