Cascading Drop downlist in ASP.NET

Raj.Trivedi
Posted by in ASP.NET category on for Beginner level | Points: 250 | Views : 5665 red flag
Rating: 4 out of 5  
 1 vote(s)

Hello Team,

In this article we will see how we can fill second drop down on the basis of the selection on the first drop down

Introduction


In this article we will see how we can cascade the drop down list.

First let us understand what is cascading.

Cascading means filling other drop down on the basis of the selected Item of the above drop down.In this article we will have 2 drop down 

    1. Country
    2. States


Note :- Select State Drop down will be disabled on page load and it will enabled when the user selects Country

For demo purpose i have populated 2 Countries in the Country Table 

  1. India
  2. USA

So let us start the development



Objective


  1. Understanding Cascading 
  2. Fetching states from database on the selection of the country.



Using the code



  1. Open Visual Studio 2010 -> Create New Website
  2. Add a Webform and drag and drop 2 drop down list
  3. One will be for Country and another will be state
  4. Once the user selects country the States Drop down will be automatically filled as per the selection of the Country.


Explanation how the cascading will be achieved

  1. Once the user selects the Country, the value of the country will be passed as a Parameter to the store procedure that we have written to get the states on the base of country ID.
  2. In this application when the User selects INDIA the value of INDIA is 1 and that will be passed to the stored procedure on the select Index Change event of the Country Drop down.
  3. Once the records matching the parameters are fetched then the state drop down will be filled.

Note :- Table and Stored Procedure is in the code section

// Table and Stored Procedure
-- Table for Country
CREATE TABLE [dbo].[Country](
	[CounrtyID] [int] NULL,
	[CounrtyName] [varchar](50) NULL
) ON [PRIMARY]

GO


---- Table for States
CREATE TABLE [dbo].[States](
	[StateID] [int] IDENTITY(1,1) NOT NULL,
	[CountryID] [int] NULL,
	[StateName] [varchar](50) NULL
) ON [PRIMARY]

GO

--Stored Proc for Getting Countries
ALTER proc [dbo].[GetCountries]
as
begin
select * from Country
end


----Stored Proc for Getting Countries
ALTER proc [dbo].[getStates]
(
@CountryID int
)
as
begin
select * from States where CountryID = @CountryID
end
// HTML Mark up and Code behind
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;

public partial class CascadingDropDownt : System.Web.UI.Page
{
    string errdesc = "0";
    SqlConnection sqlconn = new SqlConnection("Data Source=.;Initial Catalog=DotNetFunda;Persist Security Info=True;User ID=sa;Password=sqluser");
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            // Function called on page load to bind countries

            BindCountries();
        }
    }


    // Function to Bind Countries
    private void BindCountries()
    {
        try
        {
            
            sqlconn.Open();
            DataSet dscountry = new DataSet();

            // Calling the Stored Procedure of Get Countries
            SqlCommand cmd = new SqlCommand("GetCountries", sqlconn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter dacountry = new SqlDataAdapter(cmd);
            dacountry.Fill(dscountry, "Country");
            xddlCountry.DataSource = dscountry;
           
            if (dscountry.Tables[0].Rows.Count > 0)
            {
                
                xddlCountry.DataTextField = "CounrtyName";
                xddlCountry.DataValueField = "CounrtyID";
                xddlCountry.DataBind();
                xddlCountry.Items.Insert(0, new ListItem("----Select Country----"));
            }
        }
        catch (Exception ex)
        {
            errdesc = ex.Message;
        }
        finally
        {
            sqlconn.Close();
        }

    }

    // Binding States on the base of the selection of the country on select Index Change
    protected void xddlCountry_SelectedIndexChanged(object sender, EventArgs e)
    {
        try
        {
            sqlconn.Open();
            DataSet dsstates = new DataSet();

            // Calling the Stored Procedure of getstates 
            SqlCommand cmd = new SqlCommand("getStates", sqlconn);
            cmd.CommandType = CommandType.StoredProcedure;

            // Passing the parameter to the stored procedure to get the States on the selection of the country
            cmd.Parameters.AddWithValue("@CountryID", SqlDbType.Int).Value = xddlCountry.SelectedValue; 
            SqlDataAdapter dastates = new SqlDataAdapter(cmd);
            dastates.Fill(dsstates, "States");
            xddlState.DataSource = dsstates;

            if (dsstates.Tables[0].Rows.Count > 0)
            {
                xddlState.Enabled = true;
                xddlState.DataTextField = "StateName";
                xddlState.DataValueField = "StateID";
                xddlState.DataBind();
            }
        }
        catch (Exception ex)
        {
            errdesc = ex.Message;
        }
        finally
        {
            sqlconn.Close();
        }

    }
}

Screen 1 :- Page Load





Screen 2 :- Selection of Country : India



Screen 3 :- Changing of Country from India to USA




Result Achieved



Conclusion


I hope this will be a useful resource for beginners


Page copy protected against web site content infringement by Copyscape

About the Author

Raj.Trivedi
Full Name: Raj Trivedi
Member Level:
Member Status: Member,MVP
Member Since: 6/16/2012 2:04:41 AM
Country: India
Regard's Raj.Trivedi "Sharing is Caring" Please mark as answer if your Query is resolved
http://www.dotnetfunda.com/profile/raj.trivedi.aspx
Raj Trivedi i.e. me started my career as Support Professional and then moved on the Software development eventually reached at these skills Software Development | Enthusiastic Blogger | Content Writer | Technical Writer | Problem Solver | Lecturer on Technology Subjects | Runnerup Award Winner on www.dotnetfunda.com and firm believer in Sharing as a way of Caring Yet this much achieved its still a long way to go and there is biggest dream lying to be one of the best entrepreneurs of India in Technology Department. The Dream has just started and i hope it follows. Highlights are mentioned in details in my profile at http://in.linkedin.com/pub/raj-trivedi/30/61/b30/

Login to vote for this post.

Comments or Responses

Posted by: Munnab07 on: 6/18/2013 | Points: 25
How To Create Cascading Drop downlist in ASP.NET
">How To Create Cascading Drop downlist in ASP.NET

-----------------------------------------------------------------------------------------------------------------------------------------------
//Create Table Country
-----------------------------------------------------------------------------------------------------------------------------------------------
Create TABLE COUNTRY

(
COUNTRY_ID int Primary key,
COUNTRY_NAME nvarchar(20)not null
)

-------------------------------------------------------------------------------------------------------------------------------------------------
//Create Table STATE and Use Primary Key from Country Table as Foreign Key
-------------------------------------------------------------------------------------------------------------------------------------------------

create table STATE1

(

STATEID int Primary Key,
COUNTRY_ID int,
STATENAME nvarchar(20)not null
FOREIGN KEY (COUNTRY_ID) REFERENCES COUNTRY(COUNTRY_ID)
)


Asp.Net SourceCode:
------------------------------------------------------------------------------------------------------------------------------------------------
//Insert Two DrropDownList on your Page first DrropDownList Name ddlCountry and 2nd DrropDownList Name ddState and write this down here Code on Page_load
---------------------------------------------------------------------------------------------------------------------------------------
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="D.aspx.cs" Inherits="D" %>


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>

Country:<asp:DropDownList ID="ddlCountry" runat="server" Height="14px"
Width="150px">
</asp:DropDownList>
<br />
STATE:<asp:DropDownList ID="ddlState" runat="server" Height="14px"
Width="150px">
</asp:DropDownList>
<br />

</div>
</form>
</body>
</html>

CODE C#
-----------------------------------------------------------------------------------------------------------------------------------------
protected void Page_Load(object sender, EventArgs e)

{
SqlConnection cn = new SqlConnection(connectionString);
cn.Open();
SqlCommand cmd = new SqlCommand("Select * from COUNTRY", cn);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
ddlCountry.DataSource = ds;
ddlCountry.DataValueField = "COUNTRY_ID";
ddlCountry.DataTextField = "COUNTRY_NAME";
ddlCountry.DataBind();
ddlCountry.Items.Insert(0, new ListItem("----Select Country----"));

}

------------------------------------------------------------------------------------------------------------------------------------------------
//Write this Code Dabble Click on Country DroppDownList
------------------------------------------------------------------------------------------------------------------------------------------------
protected void ddlCountry_SelectedIndexChanged(object sender, EventArgs e)

{
int COUNTRY_ID= Convert.ToInt32(ddlCountry.SelectedValue);
SqlConnection cn = new SqlConnection(connectionString);
cn.Open();
SqlCommand cmd=new SqlCommand("Select * from CITY Where COUNTRY_ID='"+COUNTRY_ID+"'",cn);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
ddlState.DataSource = ds;
ddlState.DataValueField = "STATE_ID"; //
ddlState.DataTextField = "STATE_NAME";
ddlState.DataBind();
ddlState.Items.Insert(0, new ListItem("----Select State----"));
}

Login to post response

Comment using Facebook(Author doesn't get notification)