In this article, we will learn how to populate one ListBox based on another ListBox to perform Cascading operation with Listbox in ASP.net using C# and vb.net.Here I have taken two ListBox one for Country and another for State.Here we will see How to select State data based on Country selection and insert state name into state ListBox based on country name and how to delete data from country and state ListBox.So lets Statt.
[php]
Step 1. Open SQL Server
Step 2. Create database and use this database
Create database CascadingListBox //CascadingListBox is a database name
Use CascadingListBox
[/php]
Step 3. Create two table like Country and State
[php]
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
)
[/php]
Step 4. Insert data into Country and State Table
[php]
insert into Country(Cname) values(‘India’,’Pakistan’,’America’)
[/php]
[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)
[/php]
Step 5. Create stored procedure for getting the data from country and state table usp_country_get and usp_state_get procedure Name
[php]
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
[/php]
Step 6. Create stored procedure for insert data in country and state table
Note–>sp_Country_insert and sp_state_insert is a procedure Name
[php]
create proc sp_Country_insert
@cname varchar(30)
as
begin
insert into Country(Cname) values(@cname)
end
Note: sp_state_insert is a procedure name this stored procedure insert data into State based on Country id
create proc sp_state_insert
@cid int,
@sname varchar(30)
as
begin
(SELECT * FROM [Country] WHERE Cid = @cid)
INSERT INTO [State]
(Sname,Cid)
VALUES
(@sname,@cid)
end
[/php]
Step 7. Create stored procedure for delete data from country and state table
Notes–> sp_Country_Delete and sp_liststate_Delete is a procedure Name
[php]
create proc sp_Country_Delete
@cid int
as
begin
delete from Country where Cid=@cid
end
create proc sp_liststate_Delete
@sid int
as
begin
Delete from State where Sid=@sid
end
[/php]
Step 7. Open .aspx page and write html code
Note–>  is used for space
[php]
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<table>
<tr>
<td>Enter Name<asp:TextBox ID="txtlist" runat="server" ValidationGroup="ABC"></asp:TextBox>
<asp:Button ID="btnsave" runat="server" Text="Add" OnClick="btnsave_Click"/>
<asp:Button ID="btndelete" runat="server" Text="Delete" OnClick="btndelete_Click"/>
Enter Data: <asp:TextBox ID="txtbox1" runat="server" ValidationGroup="ABCD"></asp:TextBox>
<asp:Button ID="btnadd" runat="server" Text="Add" OnClick="btnadd_Click" />
<asp:Button ID="btndel" runat="server" Text="Delete" OnClick="btndel_Click" />
</td>
</tr>
<tr>
<td><asp:TextBox ID="TextBox1" runat="server" Text="Country ListBox:" ReadOnly="true"></asp:TextBox>
<asp:TextBox ID="TextBox3" runat="server" Text="State ListBox" ReadOnly="true"></asp:TextBox></td>
</tr>
<tr>
<td>
<asp:ListBox ID="listcountry" runat="server" AutoPostBack="true" OnSelectedIndexChanged="listcountry_SelectedIndexChanged"></asp:ListBox>
<asp:ListBox ID="listState" runat="server" AutoPostBack="true" OnSelectedIndexChanged="listState_SelectedIndexChanged"></asp:ListBox>
</td>
</tr>
</table>
</div>
</form>
</body>
</html>
[/php]
Step 8. 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
[php]
<connectionStrings>
<add name="DBCS" connectionString="data source=.; initial catalog=CascadingListBox;integrated security=true"/>
</connectionStrings>
[/php]
Step 9.Open code behind file (.aspx.cs) page and add namespace
[php]
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
[/php]
Step 10.C# Code
[php]
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 CascadingListBox
{
public partial class ListBoxDemo : System.Web.UI.Page
{
//Establish connection with database
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString);
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
//call BindCountryListBox method on page Load
BindCountryListBox();
listState.Enabled = false; //Liststate enabled false
}
}
//Bind CountryList
public void BindCountryListBox()
{
con.Open();
SqlCommand cmd = new SqlCommand("usp_country_get", con);//usp_country_get is a stored procedure name
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)
{
listcountry.DataValueField = "Cid"; //Cid and Cname is a name of Country Column
listcountry.DataTextField = "Cname";
listcountry.DataSource = ds;
listcountry.DataBind();
listcountry.SelectionMode = ListSelectionMode.Single;
}
}
//Bind StateListBox
public void BindStaeListBox()
{
con.Open();
SqlCommand cmd = new SqlCommand("usp_state_get", con);//usp_state_get is a stored procedure name
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@cid", listcountry.SelectedValue);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
con.Close();
if (ds.Tables[0].Rows.Count > 0)
{
//Sid and Sname is a name of State Column
listState.DataValueField = "Sid";
listState.DataTextField = "Sname";
listState.DataSource = ds;
listState.DataBind();
listState.SelectionMode = ListSelectionMode.Single;
}
}
//Insert a country name into Country table button onclick Event
protected void btnsave_Click(object sender, EventArgs e)
{
con.Open();
SqlCommand cmd = new SqlCommand("sp_Country_insert", con); //sp_Country_insert is a stored procedure name
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@cname", txtlist.Text);
cmd.ExecuteNonQuery();
con.Close();
BindCountryListBox();
}
//delete data from CountryListBox button onclick
protected void btndelete_Click(object sender, EventArgs e)
{
con.Open();
SqlCommand cmd = new SqlCommand("sp_Country_Delete", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@cid", ViewState["Value"]);
cmd.ExecuteNonQuery();
con.Close();
BindCountryListBox();
}
//Insert state name into state table based on country selection on button onclick Event
protected void btnadd_Click(object sender, EventArgs e)
{
con.Open();
SqlCommand cmd = new SqlCommand("sp_state_insert", con);
cmd.CommandType = CommandType.StoredProcedure;
//cmd.Parameters.AddWithValue("@value", Session["Value"]);
cmd.Parameters.AddWithValue("@cid", ViewState["Value"]);
cmd.Parameters.AddWithValue("@sname", txtbox1.Text);
//Session["Name"] = txtbox1.Text;
cmd.ExecuteNonQuery();
con.Close();
BindStaeListBox();
}
//delete data from Staelistbox button onclick
protected void btndel_Click(object sender, EventArgs e)
{
con.Open();
SqlCommand cmd = new SqlCommand("sp_liststate_Delete", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@sid", ViewState["Value1"]);
cmd.ExecuteNonQuery();
con.Close();
BindStaeListBox();
}
protected void listcountry_SelectedIndexChanged(object sender, EventArgs e)
{
listState.Enabled = true;//liststae is enabled true listcountry_SelectedIndexChanged
BindStaeListBox(); //call BindStaeListBox() method country onSelectIndexedChanged
ViewState["Value"] = listcountry.SelectedValue; //store country value in viewstate. this value is
//used insert state in listState based on country
}
protected void listState_SelectedIndexChanged(object sender, EventArgs e)
{
ViewState["Value1"] = listState.SelectedValue;//store State value in viewstate. this value is
//used delete state from liststate
}
}
}
[/php]
VB.Net Code
[php]
Imports System
Imports System.Collections.Generic
Imports System.Linq
Imports System.Web
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Namespace CascadingListBox
Public Partial Class ListBoxDemo
Inherits System.Web.UI.Page
‘Establish connection with database
Private con As New SqlConnection(ConfigurationManager.ConnectionStrings("DBCS").ConnectionString)
Protected Sub Page_Load(sender As Object, e As EventArgs)
If Not IsPostBack Then
‘call BindCountryListBox method on page Load
BindCountryListBox()
‘Liststate enabled false
listState.Enabled = False
End If
End Sub
‘Bind CountryList
Public Sub BindCountryListBox()
con.Open()
Dim cmd As New SqlCommand("usp_country_get", con)
‘usp_country_get is a stored procedure name
cmd.CommandType = CommandType.StoredProcedure
Dim da As New SqlDataAdapter(cmd)
Dim ds As New DataSet()
da.Fill(ds)
con.Close()
If ds.Tables(0).Rows.Count > 0 Then
listcountry.DataValueField = "Cid"
‘Cid and Cname is a name of Country Column
listcountry.DataTextField = "Cname"
listcountry.DataSource = ds
listcountry.DataBind()
listcountry.SelectionMode = ListSelectionMode.[Single]
End If
End Sub
‘Bind StateListBox
Public Sub BindStaeListBox()
con.Open()
Dim cmd As New SqlCommand("usp_state_get", con)
‘usp_state_get is a stored procedure name
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@cid", listcountry.SelectedValue)
Dim da As New SqlDataAdapter(cmd)
Dim ds As New DataSet()
da.Fill(ds)
con.Close()
If ds.Tables(0).Rows.Count > 0 Then
‘Sid and Sname is a name of State Column
listState.DataValueField = "Sid"
listState.DataTextField = "Sname"
listState.DataSource = ds
listState.DataBind()
listState.SelectionMode = ListSelectionMode.[Single]
End If
End Sub
‘Insert the country name into Country table button onclick Event
Protected Sub btnsave_Click(sender As Object, e As EventArgs)
con.Open()
Dim cmd As New SqlCommand("sp_Country_insert", con)
‘sp_Country_insert is a stored procedure name
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@cname", txtlist.Text)
cmd.ExecuteNonQuery()
con.Close()
BindCountryListBox()
End Sub
‘delete data from CountryListBox button onclick
Protected Sub btndelete_Click(sender As Object, e As EventArgs)
con.Open()
Dim cmd As New SqlCommand("sp_Country_Delete", con)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@cid", ViewState("Value"))
cmd.ExecuteNonQuery()
con.Close()
BindCountryListBox()
End Sub
‘Insert state name into state table based on country selection on button onclick Event
Protected Sub btnadd_Click(sender As Object, e As EventArgs)
con.Open()
Dim cmd As New SqlCommand("sp_state_insert", con)
cmd.CommandType = CommandType.StoredProcedure
‘cmd.Parameters.AddWithValue("@value", Session["Value"]);
cmd.Parameters.AddWithValue("@cid", ViewState("Value"))
cmd.Parameters.AddWithValue("@sname", txtbox1.Text)
‘Session["Name"] = txtbox1.Text;
cmd.ExecuteNonQuery()
con.Close()
BindStaeListBox()
End Sub
‘delete data from Staelistbox button onclick
Protected Sub btndel_Click(sender As Object, e As EventArgs)
con.Open()
Dim cmd As New SqlCommand("sp_liststate_Delete", con)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@sid", ViewState("Value1"))
cmd.ExecuteNonQuery()
con.Close()
BindStaeListBox()
End Sub
Protected Sub listcountry_SelectedIndexChanged(sender As Object, e As EventArgs)
listState.Enabled = True ‘liststae is enabled true listcountry_SelectedIndexChanged
BindStaeListBox()
‘call BindStaeListBox() method country onSelectIndexedChanged
ViewState("Value") = listcountry.SelectedValue
‘store country value in viewstate. this value is used insert state in listState based on country selection
End Sub
Protected Sub listState_SelectedIndexChanged(sender As Object, e As EventArgs)
ViewState("Value1") = listState.SelectedValue
‘store State value in viewstate. this value is used delete state from liststate
End Sub
End Class
End Namespace
[/php]
Screenshot Describe Output after run the project.
Very helpful, thanks
How to work Jason in asp.net please tell me….