Cascading DropDownList Control with Database in ASP.Net C#.
In previous asp.net article, we have discussed about how to display cascading dropdownlist control without database in asp.net.
In this asp.net tutorial we will learn how to display cascading dropdownlist control using database in asp.net. For display values in dropdownlist control, first we need to insert data into database. Here, we use sql server database, first create a new database and create three table for County data, State Data and last one for City Date.
The Database table design should be like :
TableName = CountryMst
Columns : CID (primary key / AutoIncrement) , CountyName as Nvarchar(200)TableName = StateMst
Columns : SID (primary key / AutoIncrement), StateName as Nvarchar(200), CID as intTable Name = CityMst
Columns : CID (primary key / AutoIncrement), CityName as Nvarchar(200), SID as int
Here is the screen short of sql server database table.
Table Design for CountryMst
Table Design for StateMst
Table Design for CityMst
Here, below screen we can see the Database Example contains three table CountryMst, StateMst and CityMst with columns and datatype in sql server.
Now, After Design Database table it’s time to create a stored procedure for manipulate database table.
Create New Stored procedure :
Database –> Example –> Programmability — > Stored Procedure
Here is code for all stored procedure we have created for our asp.net example.
Stored Procedure : COUNTRY_SELECT
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[COUNTRY_SELECT] AS BEGIN SELECT * FROM COUNTRYMST END
Stored Procedure : COUNTRY_INSERT
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[COUNTRY_INSERT] @COUNTRYNAME AS NVARCHAR(256) AS BEGIN INSERT INTO COUNTRYMST VALUES(@COUNTRYNAME) END
Stored Procedure : STATE_SELECT
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[STATE_SELECT] AS BEGIN SELECT * FROM STATEMST END
Stored Procedure : STATE_INSERT
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[STATE_INSERT] @STATENAME AS NVARCHAR(256), @CID AS INT AS BEGIN INSERT INTO STATEMST VALUES(@STATENAME,@CID) END
Stored Procedure : STATE_SELECT_BY_CID
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[STATE_SELECT_BY_CID] @CID AS INT AS BEGIN SELECT * FROM STATEMST WHERE CID=@CID END
Stored Procedure : CITY_SELECT
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[CITY_SELECT] AS BEGIN SELECT * FROM CITYMST END
Stored Procedure : CITY_INSERT
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[CITY_INSERT] @CITYNAME AS NVARCHAR(256), @SID AS INT AS BEGIN INSERT INTO CITYMST VALUES(@CITYNAME,@SID) END
Stored Procedure : CITY_SELECT_BY_SID
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[CITY_SELECT_BY_SID] @SID AS INT AS BEGIN SELECT * FROM CITYMST WHERE SID=@SID END
Now, Create a new asp.net web application and design a web forms for inset data into sql server database.
First Design asp.net web page like below for insert country, state and city data.
The HTML Source code for above web page design is :
<table align="center" class="style1" style="border: thin solid #008080">
<tr>
<td class="style2"
style="text-align: center; border-bottom-style: solid; border-bottom-width: thin; border-bottom-color: #008080;">
Cascading DropDownList Example</td>
</tr>
<tr>
<td style="text-align: center">
<asp:Button ID="btnaddcountry" runat="server"
onclick="btnaddcountry_Click" style="font-weight: 700" Text="ADD Country" />
<asp:Button ID="btnaddstate" runat="server" onclick="btnaddstate_Click"
style="font-weight: 700" Text="ADD State" />
<asp:Button ID="btnaddcity" runat="server" onclick="btnaddcity_Click"
style="font-weight: 700" Text="ADD City" />
</td>
</tr>
<tr>
<td style="text-align: center">
<asp:MultiView ID="MultiView1" runat="server">
<asp:View ID="View1" runat="server">
<table class="style3" style="border: thin solid #008080">
<tr>
<td colspan="2" style="border-bottom: thin solid #008080; font-weight: 700;">
ADD New Country</td>
</tr>
<tr>
<td style="text-align: right">
Country Name :</td>
<td style="text-align: left">
<asp:TextBox ID="txtnewcountry" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td>
</td>
<td style="text-align: left">
<asp:Button ID="btncountryadd" runat="server" onclick="btncountryadd_Click"
Text="ADD" Width="61px" />
</td>
</tr>
<tr>
<td colspan="2">
<asp:GridView ID="GvCountry" runat="server" BackColor="White"
BorderColor="#E7E7FF" BorderStyle="None" BorderWidth="1px" CellPadding="3"
GridLines="Horizontal">
<RowStyle BackColor="#E7E7FF" ForeColor="#4A3C8C" />
<FooterStyle BackColor="#B5C7DE" ForeColor="#4A3C8C" />
<PagerStyle BackColor="#E7E7FF" ForeColor="#4A3C8C" HorizontalAlign="Right" />
<SelectedRowStyle BackColor="#738A9C" Font-Bold="True" ForeColor="#F7F7F7" />
<HeaderStyle BackColor="#4A3C8C" Font-Bold="True" ForeColor="#F7F7F7" />
<AlternatingRowStyle BackColor="#F7F7F7" />
</asp:GridView>
</td>
</tr>
</table>
</asp:View>
<asp:View ID="View2" runat="server">
<table class="style3" style="border: thin solid #008080">
<tr>
<td colspan="2" style="border-bottom: thin solid #008080; font-weight: 700;">
ADD New State</td>
</tr>
<tr>
<td style="text-align: right">
Select Country :</td>
<td style="text-align: left">
<asp:DropDownList ID="drpcountryforstate" runat="server" Width="130px">
</asp:DropDownList>
</td>
</tr>
<tr>
<td style="text-align: right">
State Name :</td>
<td style="text-align: left">
<asp:TextBox ID="txtnewstate" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td>
</td>
<td style="text-align: left">
<asp:Button ID="btnstateadd" runat="server" onclick="btnstateadd_Click"
Text="ADD" Width="61px" />
</td>
</tr>
<tr>
<td colspan="2">
<asp:GridView ID="GvState" runat="server" BackColor="White"
BorderColor="#E7E7FF" BorderStyle="None" BorderWidth="1px" CellPadding="3"
GridLines="Horizontal">
<RowStyle BackColor="#E7E7FF" ForeColor="#4A3C8C" />
<FooterStyle BackColor="#B5C7DE" ForeColor="#4A3C8C" />
<PagerStyle BackColor="#E7E7FF" ForeColor="#4A3C8C" HorizontalAlign="Right" />
<SelectedRowStyle BackColor="#738A9C" Font-Bold="True" ForeColor="#F7F7F7" />
<HeaderStyle BackColor="#4A3C8C" Font-Bold="True" ForeColor="#F7F7F7" />
<AlternatingRowStyle BackColor="#F7F7F7" />
</asp:GridView>
</td>
</tr>
</table>
</asp:View>
<asp:View ID="View3" runat="server">
<table class="style3" style="border: thin solid #008080">
<tr>
<td colspan="2" style="border-bottom: thin solid #008080; font-weight: 700;">
ADD New City</td>
</tr>
<tr>
<td style="text-align: right">
Select Country :</td>
<td style="text-align: left">
<asp:DropDownList ID="drpcountryforcity" runat="server" Width="130px"
AutoPostBack="True"
onselectedindexchanged="drpcountryforcity_SelectedIndexChanged">
</asp:DropDownList>
</td>
</tr>
<tr>
<td style="text-align: right">
Select State :</td>
<td style="text-align: left">
<asp:DropDownList ID="drpstateforcity" runat="server" Width="130px">
</asp:DropDownList>
</td>
</tr>
<tr>
<td style="text-align: right">
City Name :</td>
<td style="text-align: left">
<asp:TextBox ID="txtnewcity" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td>
</td>
<td style="text-align: left">
<asp:Button ID="btncityadd" runat="server" onclick="btncityadd_Click"
Text="ADD" Width="61px" />
</td>
</tr>
<tr>
<td colspan="2">
<asp:GridView ID="GvCity" runat="server" BackColor="White"
BorderColor="#E7E7FF" BorderStyle="None" BorderWidth="1px" CellPadding="3"
GridLines="Horizontal">
<RowStyle BackColor="#E7E7FF" ForeColor="#4A3C8C" />
<FooterStyle BackColor="#B5C7DE" ForeColor="#4A3C8C" />
<PagerStyle BackColor="#E7E7FF" ForeColor="#4A3C8C" HorizontalAlign="Right" />
<SelectedRowStyle BackColor="#738A9C" Font-Bold="True" ForeColor="#F7F7F7" />
<HeaderStyle BackColor="#4A3C8C" Font-Bold="True" ForeColor="#F7F7F7" />
<AlternatingRowStyle BackColor="#F7F7F7" />
</asp:GridView>
</td>
</tr>
</table>
</asp:View>
</asp:MultiView>
</td>
</tr>
<tr>
<td style="text-align: center">
</td>
</tr>
</table>
In this asp.net cascading dropdownlist example we use MultiView control for make user friendly design to easily understand. we took here three view control each for county, state and city inside the multiview control.
Now, first we have to insert country data then after state data and finally insert city data according to inserted data.
Here, the first output screen of insert data in to table for country, state and city.
In above screen there are three button control for display view control one by one as per button click.
First import three namespace on code behind page for sql connectivity.
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
Write a connection string code above page_load events :
Here, we have created a private function bindconnection for selecting data from database using SELECT Storedprocedure.
string strconn = “Data Source=.\\NEWSQLEXPRESS;Initial Catalog=’EXAMPLE’;Integrated Security=True”;
protected void Page_Load(object sender, EventArgs e)
{}
private DataTable bindconnection(string queryname)
{SqlConnection sqlconn = new SqlConnection(strconn);
SqlCommand sqlcmd = new SqlCommand(queryname, sqlconn);SqlDataAdapter adapter = new SqlDataAdapter(sqlcmd);
DataTable DT = new DataTable();
adapter.Fill(DT);
return DT;
}
The asp.net c# code behind code for ADDCounty Button control click event.
protected void btnaddcountry_Click(object sender, EventArgs e)
{
MultiView1.ActiveViewIndex = 0;
DataTable DTT = bindconnection("COUNTRY_SELECT");
GvCountry.DataSource = DTT;
GvCountry.DataBind();
}
The C# Code for ADD State Button :
protected void btnaddstate_Click(object sender, EventArgs e)
{
MultiView1.ActiveViewIndex = 1;
DataTable DTT = bindconnection("COUNTRY_SELECT");
drpcountryforstate.DataSource = DTT;
drpcountryforstate.DataTextField = "CountryName";
drpcountryforstate.DataValueField = "CID";
drpcountryforstate.DataBind();
DataTable DDT = bindconnection("STATE_SELECT");
GvState.DataSource = DDT;
GvState.DataBind();
drpcountryforstate.Items.Insert(0, "SELECT");
}
The C# Code for ADD City Button :
protected void btnaddcity_Click(object sender, EventArgs e)
{
MultiView1.ActiveViewIndex = 2;
DataTable DTT = bindconnection("COUNTRY_SELECT");
drpcountryforcity.DataSource = DTT;
drpcountryforcity.DataTextField = "CountryName";
drpcountryforcity.DataValueField = "CID";
drpcountryforcity.DataBind();
drpcountryforcity.Items.Insert(0, "SELECT");
drpstateforcity.Items.Insert(0, "SELECT");
DataTable DDT = bindconnection("CITY_SELECT");
GvCity.DataSource = DDT;
GvCity.DataBind();
}
Now, when we select the add country button then the only view1 is displayed for insert country data in to CountryMst table in database.
Add New County Data in to CountyMst table in sql server :
The C# code for ADD button to insert new country records.
protected void btncountryadd_Click(object sender, EventArgs e)
{
SqlConnection sqlconn = new SqlConnection(strconn);
SqlCommand sqlcmd = new SqlCommand("COUNTRY_INSERT", sqlconn);
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcmd.Parameters.AddWithValue("@COUNTRYNAME", txtnewcountry.Text);
SqlDataAdapter adapter = new SqlDataAdapter(sqlcmd);
DataTable DT = new DataTable();
adapter.Fill(DT);
DataTable DTT = bindconnection("COUNTRY_SELECT");
GvCountry.DataSource = DTT;
GvCountry.DataBind();
}
Now, After adding some country records insert new state record respectively to country records.
The C# code for ADD State records :
protected void btnstateadd_Click(object sender, EventArgs e)
{
if (drpcountryforstate.SelectedIndex != 0)
{
SqlConnection sqlconn = new SqlConnection(strconn);
SqlCommand sqlcmd = new SqlCommand("STATE_INSERT", sqlconn);
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcmd.Parameters.AddWithValue("@STATENAME", txtnewstate.Text);
sqlcmd.Parameters.AddWithValue("@CID", Convert.ToInt32(drpcountryforstate.SelectedValue));
SqlDataAdapter adapter = new SqlDataAdapter(sqlcmd);
DataTable DT = new DataTable();
adapter.Fill(DT);
DataTable DDT = bindconnection("STATE_SELECT");
GvState.DataSource = DDT;
GvState.DataBind();
}
}
Now, Add new city records as per the selected country and state record.
Here, when we select country from county dropdownlist then the related state will be displayed automatically in state dropdownlist control. for do this write a code behind c# code on Country Dropdownlist SelectedIndexChanged and set AutoPostBack = True
The code for County Dropdownlist SelectedIndexChnaged events :
protected void drpcountryforcity_SelectedIndexChanged(object sender, EventArgs e)
{
if (drpcountryforcity.SelectedIndex != 0)
{
SqlConnection sqlconn = new SqlConnection(strconn);
SqlCommand sqlcmd = new SqlCommand("STATE_SELECT_BY_CID", sqlconn);
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcmd.Parameters.AddWithValue("@CID", Convert.ToInt32(drpcountryforcity.SelectedValue));
SqlDataAdapter adapter = new SqlDataAdapter(sqlcmd);
DataTable DT = new DataTable();
adapter.Fill(DT);
drpstateforcity.DataSource = DT;
drpstateforcity.DataTextField = "StateName";
drpstateforcity.DataValueField = "SID";
drpstateforcity.DataBind();
drpstateforcity.Items.Insert(0, "SELECT");
}
else
{
drpstateforcity.Items.Clear();
drpstateforcity.Items.Insert(0, "SELECT");
}
}
The C# Code for Add new City Records :
protected void btncityadd_Click(object sender, EventArgs e)
{
if (drpcountryforcity.SelectedIndex != 0 && drpstateforcity.SelectedIndex != 0)
{
SqlConnection sqlconn = new SqlConnection(strconn);
SqlCommand sqlcmd = new SqlCommand("CITY_INSERT", sqlconn);
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcmd.Parameters.AddWithValue("@CityName", txtnewcity.Text);
sqlcmd.Parameters.AddWithValue("@SID", Convert.ToInt32(drpstateforcity.SelectedValue));
SqlDataAdapter adapter = new SqlDataAdapter(sqlcmd);
DataTable DT = new DataTable();
adapter.Fill(DT);
DataTable DDT = bindconnection("CITY_SELECT");
GvCity.DataSource = DDT;
GvCity.DataBind();
}
}
Now, up to this we have successfully added country, state and city records in database.
Let’s take an example to understand cascading dropdownlist from database. In above example we have insert records in to databse for country, state and city. Now desing an asp.net web page for cascading dropdownlist for country, state and city. In cascading drodownlist example we display records respectively from database, which we alreay inserted in above example.
First Design asp.net web page for cascading dropdownlist example.
The HTML Source code for cascading dropdownlist example is :
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default17.aspx.cs" Inherits="Default17" %>
<!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>
<style type="text/css">
.style1
{
width: 497px;
}
.style2
{
font-size: xx-large;
font-weight: bold;
color: #CC0000;
}
.style3
{
width: 271px;
height: 156px;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<div>
<table align="center" class="style1" style="border: thin solid #008080">
<tr>
<td class="style2"
style="text-align: center; border-bottom-style: solid; border-bottom-width: thin; border-bottom-color: #008080;">
Cascading DropDownList Example</td>
</tr>
<tr>
<td style="text-align: center">
</td>
</tr>
<tr>
<td style="text-align: center">
<table class="style3">
<tr>
<td style="text-align: right; font-weight: 700">
Select Country :</td>
<td style="text-align: left">
<asp:DropDownList ID="drpcountry" runat="server" AutoPostBack="True"
onselectedindexchanged="drpcountry_SelectedIndexChanged" Width="140px">
</asp:DropDownList>
</td>
</tr>
<tr>
<td style="text-align: right; font-weight: 700">
Select State :</td>
<td style="text-align: left">
<asp:DropDownList ID="drpstate" runat="server" AutoPostBack="True"
onselectedindexchanged="drpstate_SelectedIndexChanged" Width="140px">
</asp:DropDownList>
</td>
</tr>
<tr>
<td style="text-align: right; font-weight: 700">
Select City :</td>
<td style="text-align: left">
<asp:DropDownList ID="drpcity" runat="server" Width="140px">
</asp:DropDownList>
</td>
</tr>
<tr>
<td>
</td>
<td>
</td>
</tr>
</table>
</td>
</tr>
<tr>
<td style="text-align: center">
</td>
</tr>
</table>
</div>
</form>
</body>
</html>
For display state recordes according to select country records and display city records according to select country and state record, we need to set AutoPostBack = true for Country and State Dropdownlist control.
Import namespace and write connection string code like :
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;string strconn = “Data Source=.\\NEWSQLEXPRESS;Initial Catalog=’EXAMPLE’;Integrated Security=True”;
protected void Page_Load(object sender, EventArgs e)
{}
write below code in page_load events for bind country dropdownlist control from databse.
protected void Page_Load(object sender, EventArgs e)
{
if (Page.IsPostBack == false)
{
SqlConnection sqlconn = new SqlConnection(strconn);
SqlCommand sqlcmd = new SqlCommand("COUNTRY_SELECT", sqlconn);
sqlcmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter adapter = new SqlDataAdapter(sqlcmd);
DataTable DT = new DataTable();
adapter.Fill(DT);
drpcountry.DataSource = DT;
drpcountry.DataTextField = "CountryName";
drpcountry.DataValueField = "CID";
drpcountry.DataBind();
drpcountry.Items.Insert(0, "SELECT");
drpstate.Items.Insert(0, "SELECT");
drpcity.Items.Insert(0, "SELECT");
}
}
The C# code for County Dropdownlist SelectedIndexChanged events :
protected void drpcountry_SelectedIndexChanged(object sender, EventArgs e)
{
if (drpcountry.SelectedIndex != 0)
{
SqlConnection sqlconn = new SqlConnection(strconn);
SqlCommand sqlcmd = new SqlCommand("STATE_SELECT_BY_CID", sqlconn);
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcmd.Parameters.AddWithValue("@CID", Convert.ToInt32(drpcountry.SelectedValue));
SqlDataAdapter adapter = new SqlDataAdapter(sqlcmd);
DataTable DT = new DataTable();
adapter.Fill(DT);
drpstate.DataSource = DT;
drpstate.DataTextField = "StateName";
drpstate.DataValueField = "SID";
drpstate.DataBind();
drpstate.Items.Insert(0, "SELECT");
drpcity.Items.Clear();
drpcity.Items.Insert(0, "SELECT");
}
else
{
drpstate.Items.Clear();
drpstate.Items.Insert(0, "SELECT");
drpcity.Items.Clear();
drpcity.Items.Insert(0, "SELECT");
}
}
The C# code for State Dropdownlist SelectedIndexChanged events :
protected void drpstate_SelectedIndexChanged(object sender, EventArgs e)
{
if (drpstate.SelectedIndex != 0)
{
SqlConnection sqlconn = new SqlConnection(strconn);
SqlCommand sqlcmd = new SqlCommand("CITY_SELECT_BY_SID", sqlconn);
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcmd.Parameters.AddWithValue("@SID", Convert.ToInt32(drpstate.SelectedValue));
SqlDataAdapter adapter = new SqlDataAdapter(sqlcmd);
DataTable DT = new DataTable();
adapter.Fill(DT);
drpcity.DataSource = DT;
drpcity.DataTextField = "CityName";
drpcity.DataValueField = "CID";
drpcity.DataBind();
drpcity.Items.Insert(0, "SELECT");
}
else
{
drpcity.Items.Clear();
drpcity.Items.Insert(0, "SELECT");
}
}
I hope this asp.net country, state and city cascading dropdownlist from database example will help you…..