Bind country state city in asp.net: In this tutorial, I am going to show you "how to bind country state city in asp.net using SQL Server". In this process, we need three dropdown i.e. country state city. If you select country dropdown then the related state will be bind as same as if we select state drop down then related city will be bind. It means that It is a cascading process of the binding dropdown. #cascadingdropdown
Steps to implement a cascading drop-down list for country state city in asp.net: Here are some simple step to implement.
1. Create three tables country state and city.
2. Create three procedure in SQL Server.
3. Add one Asp.net web form.
Back-End Tables
Tables
Create Table For Country
CREATE TABLE [Country](
[cid] [int] IDENTITY(1,1) NOT NULL Primary Key,
[cname] [varchar](50) NULL,
)
Create Table For State
CREATE TABLE [State]
(
[sid] [int] IDENTITY(1,1) NOT NULL Primary Key,
[sname] [varchar](50) NULL,
[cid] [int] NULL,
)
Create Table For City
CREATE TABLE [City]
(
[ctid] [int] IDENTITY(1,1) NOT NULL Primary Key,
[ctname] [varchar](50) NULL,
[sid] [int] NULL)
After creating tables do mapping of it.
SQL Server Procedures
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
===============================
create proc usp_city_get
@sid int
as
begin
select * from City where sid=@sid
end
Connection String in webConfig
<?xml version="1.0"?>
<!--
For more information on how to configure your ASP.NET application, please visit
http://go.microsoft.com/fwlink/?LinkId=169433
-->
<configuration>
<system.web>
<compilation debug="true" targetFramework="4.5" />
<!--<httpRuntime targetFramework="4.5" />-->
</system.web>
<connectionStrings>
<add name="con" connectionString="data source=SANJAY\SQL2012;initial catalog=DotNet;integrated security=true"/>
</connectionStrings>
</configuration>
Front-End
Aspx Page
<table>
<tr>
<td>Name :</td>
<td>
<asp:TextBox ID="txtname" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td>Country :</td>
<td>
<asp:DropDownList ID="ddlcountry" runat="server" AutoPostBack="true" DataValueField="cid" DataTextField="cname" OnSelectedIndexChanged="ddlcountry_SelectedIndexChanged"></asp:DropDownList></td>
</tr>
<tr>
<td>State :</td>
<td>
<asp:DropDownList ID="ddlstate" runat="server" AutoPostBack="true" DataValueField="sid" DataTextField="sname" OnSelectedIndexChanged="ddlstate_SelectedIndexChanged"></asp:DropDownList></td>
</tr>
<tr>
<td>City :</td>
<td>
<asp:DropDownList ID="ddlcity" runat="server" DataValueField="ctid" DataTextField="ctname"></asp:DropDownList></td>
</tr>
<tr>
<td></td>
<td>
<asp:Button ID="btnsave" runat="server" Text="Save" /></td>
</tr>
</table>
Code Behind (.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.Configuration;
using System.Data.SqlClient;
using System.IO;
namespace DDL_DotNet
{
public partial class country_state_city : System.Web.UI.Page
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString);
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
Fill_Country();
ddlstate.Items.Insert(0, new ListItem("--select state--", "0"));
ddlcity.Items.Insert(0, new ListItem("--select city--", "0"));
ddlstate.Enabled = false;
ddlcity.Enabled = false;
}
}
public void Fill_Country()
{
DataSet ds = new DataSet();
ds = GetData("usp_country_get",null);
if (ds.Tables[0].Rows.Count > 0)
{
ddlcountry.DataSource = ds;
ddlcountry.DataBind();
ddlcountry.Items.Insert(0, new ListItem("--select country--", "0"));
}
}
private DataSet GetData(string SP_Name, SqlParameter param)
{
SqlDataAdapter da = new SqlDataAdapter(SP_Name,con);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
if (param != null)
{
da.SelectCommand.Parameters.Add(param);
}
DataSet ds = new DataSet();
da.Fill(ds);
return ds;
}
protected void ddlcountry_SelectedIndexChanged(object sender, EventArgs e)
{
if (ddlcountry.SelectedValue == "0")
{
ddlstate.SelectedValue = "0";
ddlstate.Enabled = false;
}
else
{
ddlstate.Enabled = true;
SqlParameter param = new SqlParameter("@cid",ddlcountry.SelectedValue);
DataSet ds = new DataSet();
ds = GetData("usp_state_get",param);
if (ds.Tables[0].Rows.Count > 0)
{
ddlstate.DataSource = ds;
ddlstate.DataBind();
ddlstate.Items.Insert(0, new ListItem("--select state--", "0"));
}
}
ddlcity.SelectedValue = "0";
ddlcity.Enabled = false;
}
protected void ddlstate_SelectedIndexChanged(object sender, EventArgs e)
{
if (ddlstate.SelectedValue == "0")
{
ddlcity.SelectedValue = "0";
ddlcity.Enabled = false;
}
else
{
ddlcity.Enabled = true;
SqlParameter param = new SqlParameter("@sid", ddlstate.SelectedValue);
DataSet ds = new DataSet();
ds = GetData("usp_city_get", param);
if (ds.Tables[0].Rows.Count > 0)
{
ddlcity.DataSource = ds;
ddlcity.DataBind();
ddlcity.Items.Insert(0, new ListItem("--select city--", "0"));
}
}
}
}
}
Thanks for reading the post if you have any doubt please feel free to comment below.
#cascadingdropdown
#cascadingdropdown
the cascading dropdownlist store only iDs in the database
ReplyDeletePost a Comment