Related Posts Plugin for WordPress, Blogger...

About

Follow Us

Tuesday, 9 June 2015

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.

Implementation:
Create a table in database i.e. Emp_personal. As given below:
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