Introduction:
In this article I will explain how to fill or filter a gridview
based on dropdown selected value in asp.net using c#, vb.net.
In his article I will filter the gridview data according to dropdown selection.
First of all I will bind the dropdown with data from database and then
gridview. After binding the data from database I will filter the data based on
dropdown selection.
Column Name
|
Data Type
|
Allow Nulls
|
EmpPer_Id
|
Int(IDENTITY=TRUE)
|
No
|
EmpName
|
varchar(100)
|
Yes
|
Age
|
int
|
Yes
|
Address
|
varchar(500)
|
Yes
|
You can use this script to create table.
CREATE TABLE [dbo].[Emp_Personal](
[EmpPer_Id] [int] IDENTITY(1,1) NOT NULL,
[EmpName] [varchar](100) NULL,
[Age] [int] NULL,
[Address] [varchar](500) NULL
)
Design Section:
Now create your webpage as given below:
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1"
runat="server">
<title> Filtering Gridview with
Dropdownlist </title>
</head>
<body>
<form id="form1"
runat="server">
<fieldset style="width:350px;"><legend><b>Filter
Gridview based on Dropdown selection</b></legend>
<div >
<table style="width: 420px" >
<tr style="color:White; background:Maroon; font-weight:bold;">
<td style="width: 40px;">
UserId
</td>
<td style="width: 120px;" >
LastName
</td>
<td style="width: 130px;">
UserName
</td>
<td style="width: 130px;">
Location
</td>
</tr>
<tr style="
background:#f2ff2f2;">
<td style="width: 40px;">
</td>
<td style="width: 120px;">
</td>
<td style="width: 130px;">
</td>
<td style="width: 130px;">
<asp:DropDownList ID="ddlEmp"
runat="server"
AutoPostBack="true"
Width="120px"
Font-Size="11px" onselectedindexchanged="ddlEmp_SelectedIndexChanged">
</asp:DropDownList>
</td>
</tr>
<tr>
<td colspan="4">
<asp:GridView runat="server"
ID="gvDetails"
ShowHeader="false"
AllowPaging="true"
PageSize="10"
AutoGenerateColumns="false"
Width="420px">
<Columns>
<asp:BoundField DataField="EmpPer_id"
HeaderText="Employee
Id" ItemStyle-Width="40px" />
<asp:BoundField DataField="age"
HeaderText="Age"
ItemStyle-Width="120px"
/>
<asp:BoundField DataField="EmpName"
HeaderText="Name"
ItemStyle-Width="130px"/>
<asp:BoundField DataField="Address"
HeaderText="Address"
ItemStyle-Width="130px"/>
</Columns>
</asp:GridView>
</td>
</tr>
</table>
</div></fieldset>
</form>
</body>
</html>
ASP.NET Code behind File Using C#:
C# Code
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System;
using System.Collections.Generic;
using System.Web.Services;
using System.Data.SqlClient;
using System.Data;
public partial
class autoComplete
: System.Web.UI.Page
{
string
strcon = "Data Source=localhost;Integrated
Security=true;Initial Catalog=test";
protected void
Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindLocation();
BindGridview();
}
}
protected void
BindLocation() {
DataTable dt = new
DataTable();
using (SqlConnection
con = new SqlConnection(strcon))
{
con.Open();
SqlCommand cmd = new
SqlCommand("Select
Distinct EmpName from Emp_Personal", con);
SqlDataAdapter da = new
SqlDataAdapter(cmd);
da.Fill(dt);
con.Close();
ddlEmp.DataSource
= dt;
ddlEmp.DataTextField
= "Empname";
ddlEmp.DataValueField
= "Empname";
ddlEmp.DataBind();
ddlEmp.Items.Insert(0,
new ListItem("All", ""));
}
}
protected void
BindGridview()
{
DataTable dt = new
DataTable();
using (SqlConnection
con = new SqlConnection(strcon))
{
con.Open();
SqlCommand cmd = new
SqlCommand("select
* from Emp_Personal", con);
SqlDataAdapter da = new
SqlDataAdapter(cmd);
da.Fill(dt);
con.Close();
gvDetails.DataSource
= dt;
gvDetails.DataBind();
}
}
protected void
ddlEmp_SelectedIndexChanged(object sender, EventArgs e)
{
DataTable dt = new
DataTable();
using (SqlConnection
con = new SqlConnection(strcon))
{
con.Open();
if (ddlEmp.SelectedValue != "")
{
SqlCommand
cmd = new SqlCommand("select * from Emp_Personal where Empname
=@name", con);
cmd.Parameters.AddWithValue("@name", ddlEmp.SelectedValue);
SqlDataAdapter da = new
SqlDataAdapter(cmd);
da.Fill(dt);
}
else
{
SqlCommand
cmd = new SqlCommand("select * from Emp_Personal", con);
SqlDataAdapter da = new
SqlDataAdapter(cmd);
da.Fill(dt);
}
con.Close();
gvDetails.DataSource
= dt;
gvDetails.DataBind();
}
}
}
VB.NET Code
Imports System.Collections.Generic
Imports System.Linq
Imports System.Web
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Collections.Generic
Imports System.Web.Services
Imports System.Data.SqlClient
Imports System.Data
Partial Public
Class autoComplete
Inherits
System.Web.UI.Page
Private
strcon As String
= "Data Source=localhost;Integrated
Security=true;Initial Catalog=test"
Protected Sub Page_Load(sender As
Object, e As EventArgs)
If Not IsPostBack Then
BindLocation()
BindGridview()
End If
End Sub
Protected Sub BindLocation()
Dim dt As New DataTable()
Using
con As New SqlConnection(strcon)
con.Open()
Dim
cmd As New SqlCommand("Select
Distinct EmpName from Emp_Personal", con)
Dim
da As New SqlDataAdapter(cmd)
da.Fill(dt)
con.Close()
ddlEmp.DataSource = dt
ddlEmp.DataTextField = "Empname"
ddlEmp.DataValueField = "Empname"
ddlEmp.DataBind()
ddlEmp.Items.Insert(0, New ListItem("All", ""))
End Using
End Sub
Protected Sub BindGridview()
Dim dt As New DataTable()
Using
con As New SqlConnection(strcon)
con.Open()
Dim
cmd As New SqlCommand("select
* from Emp_Personal", con)
Dim
da As New SqlDataAdapter(cmd)
da.Fill(dt)
con.Close()
gvDetails.DataSource = dt
gvDetails.DataBind()
End Using
End Sub
Protected Sub ddlEmp_SelectedIndexChanged(sender As Object, e As EventArgs)
Dim dt As New DataTable()
Using
con As New SqlConnection(strcon)
con.Open()
If
ddlEmp.SelectedValue <> "" Then
Dim
cmd As New SqlCommand("select
* from Emp_Personal where Empname =@name", con)
cmd.Parameters.AddWithValue("@name", ddlEmp.SelectedValue)
Dim
da As New SqlDataAdapter(cmd)
da.Fill(dt)
Else
Dim
cmd As New SqlCommand("select
* from Emp_Personal", con)
Dim
da As New SqlDataAdapter(cmd)
da.Fill(dt)
End
If
con.Close()
gvDetails.DataSource = dt
gvDetails.DataBind()
End Using
End Sub
End Class
0 comments:
Post a Comment