Article

Country State dropdownlist in Asp.Net

Here Laxmikant has explained with an example, how to build Country State DropDownList in ASP.Net i.e. Country State Cascading DropDownList populated from Database and dependent on each other.

In this article, we will learn Country State Dropdownlist in Asp.Net. Here I have taken two dropdown list one for Country and another for State. Here we will see How to select State data based on the Country selection.Here when a Country is selected, the SelectedIndexChanged event will fire and the State DropDownList will be populated based on the selected Country.

Database
Two tables Country, State and City are created with the following schema.

Step 1. Open SQL Server

Step 2. Create a database and use this database
Create database CascadingEx //CascadingEx is database name
Use CascadingEx

Step 3. Create two table like Country and State and create a stored procedure

Create table Country(
Cid int primary key identity(1,1),
Cname varchar(30)
)

/*Create State Table*/

Create table State(
Sid int primary key identity(1,1),
Sname varchar(30),
Cid int foreign key references Country(Cid)
)

 

Note: we set the foreign key to provide a relation between them table this foreign key follows Country Id based on this ID we select state (“Suppose that whenever select India only India’s State show”)

Step 4. Insert data into Country and State Table

[php]

/*Insert data into Country Table*/

insert into Country(Cname) values(‘India’)
insert into Country(Cname) values(‘Pakistan’)
insert into Country(Cname) values(‘America’)

[/php]

/*Insert data into State Table*/
insert into State1(Sname,Cid) values(‘Bihar’,1)
insert into State1(Sname,Cid) values(‘UP’,1)
insert into State1(Sname,Cid) values(‘Gujrat’,1)
insert into State1(Sname,Cid) values(‘Lahore’,2)
insert into State1(Sname,Cid) values(‘Karachi’,2)
insert into State1(Sname,Cid) values(‘Newyork’,2)
insert into State1(Sname,Cid) values(‘Texas’,3)
insert into State1(Sname,Cid) values(‘Washington’,3)

 

Step 5. Create stored procedure for getting the data from country and state table.usp_country_get and usp_state_get procedure Name

 

create proc usp_country_get
as
begin
Select * from Country
end

create proc usp_state_get
@cid int
as
begin
Select * from State where Cid=@cid
end

 

Html

Step 6. Open Visual Studio select File open new Project. Then Select Web and click on Asp. net Empty website.
The HTML Markup consists of two DropDownLists each for Country and State. The Country DropDownLists have been assigned SelectedIndexChanged event handlers.

<head runat=”server”>
<title></title>
</head>
<body>
<form id=”form1″ runat=”server”>
<div>
<table>
<tr>
<td>Country</td>
<td><asp:DropDownList ID=”ddlcountry” runat=”server” AutoPostBack=”true” OnSelectedIndexChanged=”ddlcountry_SelectedIndexChanged”></asp:DropDownList></td>
</tr>

<tr>
<td>State</td>
<td><asp:DropDownList ID=”ddlstate” runat=”server”></asp:DropDownList></td>
</tr>
</table>
</div>
</form>
</body>
</html>

Step 7.Open web.config file and establish a connection with the database.

Note
In initial catalog write Database Name
In data source, you can simply write (.) /your server name/(local)
Integrated security for Authentication

<connectionStrings>
<add name=”DBCS” connectionString=”data source=.; initial catalog=CascadingEx;integrated security=true”/>
</connectionStrings>

Step 8. Open Code behind file .aspx.cs page.

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;
using System.Configuration;

namespace CascadingEx
{
public partial class DropDownEx : System.Web.UI.Page
{
//Establish a connection
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings[“DBCS”].ConnectionString);
protected void Page_Load(object sender, EventArgs e)
{
//on page load call CountryBind
if (!IsPostBack)
{
CountryBind();
ddlstate.Items.Insert(0, new ListItem(“–Select–“, “0”));
//ddlstate enabled is false.It is visible only country selection
ddlstate.Enabled = false;

}

}
//CountryBind is a method name
public void CountryBind()
{
con.Open();
SqlCommand cmd = new SqlCommand(“usp_country_get”, con);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
con.Close();
if (ds.Tables[0].Rows.Count > 0)
{
ddlcountry.DataValueField = “Cid”;
ddlcountry.DataTextField = “Cname”;
ddlcountry.DataSource = ds;
ddlcountry.DataBind();
ddlcountry.Items.Insert(0,new ListItem(“–Select Country–“,”0”));
}
}
//StateBind is a method name
public void StateBind()
{
con.Open();
SqlCommand cmd = new SqlCommand(“usp_state_get”, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue(“@cid”,ddlcountry.SelectedValue);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
con.Close();
if (ds.Tables[0].Rows.Count > 0)
{
ddlstate.DataValueField = “Sid”;
ddlstate.DataTextField = “Sname”;
ddlstate.DataSource = ds;
ddlstate.DataBind();
ddlstate.Items.Insert(0, new ListItem(“–Select State–“, “0”));
}

}
/// <summary>
/// Here i have to create event.Like Normal dropdown selected indexChanged event.
/// you can use this event whenever you have to require some action/operation on selected index
/// changed of DropDown control.you have to fired this event and perform some action
/// </summary>
protected void ddlcountry_SelectedIndexChanged(object sender, EventArgs e)
{
ddlstate.Enabled = true;
StateBind();
}
}
}
[/php]

Screenshot
country-state-dropdownlist-in-asp-net
country-state-dropdownlist-in-asp-net

Leave a Reply

Your email address will not be published.