Country State City Cascading DropDownList with Database in ASP.Net

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 int

Table 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

Country State City DropDownList in ASP.Net

Country State City DropDownList in ASP.Net C#

Table Design for StateMst

Country State City DropDownList in ASP.Net

Country State City DropDownList in ASP.Net C#

Table Design for CityMst

Country State City DropDownList in ASP.Net

Country State City DropDownList in ASP.Net C#

Here, below screen we can see the Database Example contains three table CountryMst, StateMst and CityMst with columns  and datatype in sql server.

Country State City DropDownList in ASP.Net

Country State City DropDownList in ASP.Net

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

 Stored Procedure : COUNTRY_INSERT

 Stored Procedure  : STATE_SELECT

  Stored Procedure  : STATE_INSERT

   Stored Procedure  : STATE_SELECT_BY_CID

 Stored Procedure  : CITY_SELECT

 Stored Procedure  : CITY_INSERT

Stored Procedure :  CITY_SELECT_BY_SID

 

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.

Country State City DropDownList in ASP.Net

Country State City DropDownList in ASP.Net

The HTML Source code for above web page design is :

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.

Cascading dropdownlist in asp.net

Cascading dropdownlist in asp.net

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.

The C# Code for ADD State Button :

The C# Code for ADD City Button :

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 :

Cascading dropdownlist in asp.net

Add New County Data in to CountryMst Table

The C# code for ADD button to insert new country records.

Now, After adding some country records insert new state record respectively to country records.

Cascading dropdownlist in asp.net

Add New State Data in to StateMst Table

The C# code for ADD State records :

Now, Add new city records as per the selected country and state record.

Cascading dropdownlist in asp.net

Add New City Data in to CityMst Table

Cascading dropdownlist in asp.net

Add New City Data in to CityMst Table

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

Country State City DropDownList in ASP.Net

Country State City DropDownList in ASP.Net

The code for County Dropdownlist SelectedIndexChnaged events :

The C# Code for Add new City Records :

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.

Country State City DropDownList in ASP.Net

Country State City DropDownList in ASP.Net

The HTML Source code for cascading dropdownlist example is :

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.

Country State City DropDownList in ASP.Net

Country State City DropDownList in ASP.Net

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.

Country State City DropDownList in ASP.Net

Country State City DropDownList in ASP.Net

The C# code for County Dropdownlist SelectedIndexChanged events :

Country State City DropDownList in ASP.Net

Country State City DropDownList in ASP.Net

The C# code for State Dropdownlist SelectedIndexChanged events :

Country State City DropDownList in ASP.Net

Country State City DropDownList in ASP.Net

Country State City DropDownList in ASP.Net

Country State City DropDownList in ASP.Net

I hope this asp.net country, state and city cascading dropdownlist from database example will help you…..

Leave a Reply

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

5 + 9 =


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">