c-sharp

How to bind country state with dropdownlist in asp.net C#

In this article, we learn how to perform Cascading operation with Drop-Down List in ASP.net using C#. 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.So let’s Start.

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


/*Insert data into Country Table*/

insert into Country(Cname) values('India')
insert into Country(Cname) values('Pakistan')
insert into Country(Cname) values('America')

/*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

Step 6. Open Visual Studio select File open new Project. Then Select Web and click on Asp. net Empty website.

In .aspx page drop two dropdownlist

<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();
}
}
}

Click here for video tutorial for this article

1 thought on “How to bind country state with dropdownlist in asp.net C#

Leave a Reply

Your email address will not be published. Required fields are marked *