In this article I am going to show how we can bind a
drop down list from Data Base and on selecting any record from this drop down
how we can fill respective records in a Grid View.
I am writing this article because I got a request from
one of my user cum friend. He stuck in this business requirement and asked me
to write something like this.
So I will show this functionality by using 2 ways.
Here in this part I am showing this by using ASP.NET C# and SQL Server and in
next part I will show it by using jQuery:
So below is my Data Table in design mode from which I
will demonstrate this requirement:
Image 1.
Script
of My Table:
CREATE TABLE [dbo].[EmployeeTeam](
[Employee_ID]
[int] IDENTITY(1,1) NOT NULL,
[Name]
[varchar](50) NULL,
[Manager_ID]
[int] NULL,
[Email]
[varchar](50) NULL,
[Mobile]
[varchar](50) NULL,
[Country]
[varchar](50) NULL,
[IsManager]
[bit] NULL,
CONSTRAINT [PK_EmployeeTeam] PRIMARY
KEY CLUSTERED
(
[Employee_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS
= ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
Now we will see Data in My
Table:
Image 2.
Here In this you can I have employee records with its Manager
Id. So in drop down I will see only Manage and on selecting Manager from Drop
down I will show their team information in Grid view:
Below is my aspx:
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="DropDownGridView.Default" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Fill Grid View On Selecting Record From
Drop Down</title>
</head>
<body>
<form id="form1" runat="server">
<table style="width: 100%; text-align: center; border: solid 5px red; background-color: yellow; vertical-align: top;">
<tr>
<td>
<div>
<fieldset style="width: 99%;">
<legend style="font-size: 20pt; color: red; font-family: Verdana">Fill Grid View On
Selecting Record From Drop Down</legend>
<table style="padding: 20px; background-color: skyblue; width: 100%; text-align: center;">
<tr style="background-color: orange; height: 40px;">
<td style="width: 25%; text-align: left; padding-left: 20px; font-family: Verdana">Select Manager: </td>
<td style="text-align: left;">
<asp:DropDownList ID="ddlManager" runat="server" AutoPostBack="True"
OnSelectedIndexChanged="ddlManager_SelectedIndexChanged" Height="18px"
Width="200px" CausesValidation="True">
</asp:DropDownList><br />
</td>
</tr>
<tr>
<td></td>
</tr>
<tr>
<td colspan="2">
<asp:GridView ID="GridViewEmployee" runat="server" AutoGenerateColumns="False" Width="100%"
BackColor="White" BorderColor="#336666" BorderStyle="Double" BorderWidth="3px" Font-Names="verdana"
CellPadding="4" GridLines="Horizontal" EmptyDataText="There is no
Employee.">
<Columns>
<asp:BoundField HeaderText="Emp Id" DataField="Employee_Id"
ItemStyle-HorizontalAlign="Left" HeaderStyle-HorizontalAlign="Left" />
<asp:BoundField HeaderText="Emp Name" DataField="Name"
ItemStyle-HorizontalAlign="Left" HeaderStyle-HorizontalAlign="Left" />
<asp:BoundField HeaderText="Email" DataField="Email"
ItemStyle-HorizontalAlign="Left" HeaderStyle-HorizontalAlign="Left" />
<asp:BoundField HeaderText="Mobile" DataField="Mobile"
ItemStyle-HorizontalAlign="Left" HeaderStyle-HorizontalAlign="Left" />
<asp:BoundField HeaderText="Country" DataField="Country"
ItemStyle-HorizontalAlign="Left" HeaderStyle-HorizontalAlign="Left" />
</Columns>
<FooterStyle BackColor="White" ForeColor="#333333" />
<HeaderStyle BackColor="#336666" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#336666" ForeColor="White" HorizontalAlign="Center" />
<RowStyle BackColor="White" ForeColor="#333333" />
<SelectedRowStyle BackColor="#339966" Font-Bold="True" ForeColor="White" />
<SortedAscendingCellStyle BackColor="#F7F7F7" />
<SortedAscendingHeaderStyle BackColor="#487575" />
<SortedDescendingCellStyle BackColor="#E5E5E5" />
<SortedDescendingHeaderStyle BackColor="#275353" />
</asp:GridView>
</td>
</tr>
<tr>
<td colspan="2"></td>
</tr>
</table>
</fieldset>
</div>
</td>
</tr>
</table>
</form>
</body>
</html>
Now
my aspx.cs code is:
using System;
using
System.Collections.Generic;
using
System.Configuration;
using System.Data;
using
System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using
System.Web.UI.WebControls;
namespace DropDownGridView
{
public partial class Default : System.Web.UI.Page
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["EMPCON"].ConnectionString);
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
BindAllManagerDropDown();
}
}
protected void
BindAllManagerDropDown()
{
SqlCommand cmd = new SqlCommand();
SqlDataAdapter da = new SqlDataAdapter();
DataTable dt = new DataTable();
try
{
cmd = new SqlCommand("Select *
from EmployeeTeam WHERE IsManager=1", con);
da.SelectCommand = cmd;
da.Fill(dt);
ddlManager.DataSource = dt;
ddlManager.DataTextField = "Name";
ddlManager.DataValueField = "Employee_Id";
ddlManager.DataBind();
ddlManager.Items.Insert(0, "-- Select
Manager --");
}
catch (Exception ex)
{
}
finally
{
cmd.Dispose();
da.Dispose();
dt.Clear();
dt.Dispose();
}
}
protected void
ddlManager_SelectedIndexChanged(object sender, EventArgs e)
{
try
{
int managerID = Convert.ToInt32(ddlManager.SelectedValue);
BindManagerEMPLOYEE(managerID);
}
catch (Exception ex)
{
}
}
private void
BindManagerEMPLOYEE(int managerID)
{
DataTable dt = new DataTable();
SqlDataAdapter adp = new SqlDataAdapter();
try
{
SqlCommand cmd = new SqlCommand("select
* from EmployeeTeam where
Manager_ID="
+ managerID + " ",
con);
adp.SelectCommand = cmd;
adp.Fill(dt);
if (dt.Rows.Count >
0)
{
GridViewEmployee.DataSource
= dt;
GridViewEmployee.DataBind();
}
else
{
GridViewEmployee.DataSource
= null;
GridViewEmployee.DataBind();
}
}
catch (Exception ex)
{
}
finally
{
dt.Clear();
dt.Dispose();
adp.Dispose();
}
}
}
}
My Connection
string in Web.config file:
<connectionStrings>
<add name="EMPCON" connectionString="Data Source=INDIA\MSSQLServer2k8;Initial
Catalog=TestDB;Integrated Security=True"/>
</connectionStrings>
Image 3.
Now run your application:
Image 4.
Image 5.
Image 6.
Image 7.
Image 8.
Image 9.
In next part I will show this functionality by using
jQuery J