csharp

How to populate one listbox based on another listbox in ASP.net C#/VB.net/How to creating Cascading Listbox and insert data in second Listbox based on first Listbox using ASP.net C#/VB.net

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–>&nbsp 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"/>&nbsp;

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> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;
<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> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<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.

2 thoughts on “How to populate one listbox based on another listbox in ASP.net C#/VB.net/How to creating Cascading Listbox and insert data in second Listbox based on first Listbox using ASP.net C#/VB.net

Leave a Reply

Your email address will not be published.