Post
Ask Question

Fill ASP.Net GridView on Selecting Record From DropDownList

I am writing this article because I got a request from one of my users and  friends. He stuck in this business requirement and asked me to write something about this. So I will show this functionality in 2 ways. Here in this part I will show this using ASP.NET C# and SQL Server and in the next part I will show it using jQuery.

Figure 1 shows my Data Table in design mode from which I will show this requirement.

table design
Figure 1

Script of My Table

  1. CREATE TABLE [dbo].[EmployeeTeam](  
  2.     [Employee_ID] [int] IDENTITY(1,1) NOT NULL,  
  3.     [Name] [varchar](50) NULL,  
  4.     [Manager_ID] [int] NULL,  
  5.     [Email] [varchar](50) NULL,  
  6.     [Mobile] [varchar](50) NULL,  
  7.     [Country] [varchar](50) NULL,  
  8.     [IsManager] [bit] NULL,  
  9.  CONSTRAINT [PK_EmployeeTeam] PRIMARY KEY CLUSTERED   
  10. (  
  11.     [Employee_ID] ASC  
  12. )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]  
  13. ) ON [PRIMARY]  
  14.   
  15. GO  
  16.   
  17. SET ANSI_PADDING OFF  
  18. GO 

Figure 2 shows the data in the table.

table
Figure 2.

Here In this you can see I have employee records with Manager Id. So in DropDown I will see only Manager and on selecting a Manager from the DropDown I will show the team information in the GridView:

The following is my aspx:

  1. <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="DropDownGridView.Default" %>  
  2.   
  3. <!DOCTYPE html>  
  4.   
  5. <html xmlns="http://www.w3.org/1999/xhtml">  
  6. <head runat="server">  
  7.     <title>Fill Grid View On Selecting Record From Drop Down</title>  
  8. </head>  
  9. <body>  
  10.     <form id="form1" runat="server">  
  11.         <table style="width: 100%; text-align: center; border: solid 5px red; background-color: yellow; vertical-align: top;">  
  12.             <tr>  
  13.                 <td>  
  14.                     <div>  
  15.                         <fieldset style="width: 99%;">  
  16.                             <legend style="font-size: 20pt; color: red; font-family: Verdana">Fill Grid View On Selecting Record From Drop Down</legend>  
  17.                             <table style="padding: 20px; background-color: skyblue; width: 100%; text-align: center;">  
  18.                                 <tr style="background-color: orange; height: 40px;">  
  19.                                     <td style="width: 25%; text-align: left; padding-left: 20px; font-family: Verdana">Select Manager: </td>  
  20.                                     <td style="text-align: left;">  
  21.                                         <asp:DropDownList ID="ddlManager" runat="server" AutoPostBack="True"  
  22.                                             OnSelectedIndexChanged="ddlManager_SelectedIndexChanged" Height="18px"  
  23.                                             Width="200px" CausesValidation="True">  
  24.                                         </asp:DropDownList><br />  
  25.   
  26.                                     </td>  
  27.                                 </tr>  
  28.                                 <tr>  
  29.                                     <td></td>  
  30.                                 </tr>  
  31.                                 <tr>  
  32.                                     <td colspan="2">  
  33.                                         <asp:GridView ID="GridViewEmployee" runat="server" AutoGenerateColumns="False" Width="100%"  
  34.                                             BackColor="White" BorderColor="#336666" BorderStyle="Double" BorderWidth="3px" Font-Names="verdana"  
  35.                                             CellPadding="4" GridLines="Horizontal" EmptyDataText="There is no Employee.">  
  36.                                             <Columns>  
  37.                                                 <asp:BoundField HeaderText="Emp Id" DataField="Employee_Id" ItemStyle-HorizontalAlign="Left" HeaderStyle-HorizontalAlign="Left" />  
  38.                                                 <asp:BoundField HeaderText="Emp Name" DataField="Name" ItemStyle-HorizontalAlign="Left" HeaderStyle-HorizontalAlign="Left" />  
  39.                                                 <asp:BoundField HeaderText="Email" DataField="Email" ItemStyle-HorizontalAlign="Left" HeaderStyle-HorizontalAlign="Left" />  
  40.                                                 <asp:BoundField HeaderText="Mobile" DataField="Mobile" ItemStyle-HorizontalAlign="Left" HeaderStyle-HorizontalAlign="Left" />  
  41.                                                 <asp:BoundField HeaderText="Country" DataField="Country" ItemStyle-HorizontalAlign="Left" HeaderStyle-HorizontalAlign="Left" />  
  42.                                             </Columns>  
  43.                                             <FooterStyle BackColor="White" ForeColor="#333333" />  
  44.                                             <HeaderStyle BackColor="#336666" Font-Bold="True" ForeColor="White" />  
  45.                                             <PagerStyle BackColor="#336666" ForeColor="White" HorizontalAlign="Center" />  
  46.                                             <RowStyle BackColor="White" ForeColor="#333333" />  
  47.                                             <SelectedRowStyle BackColor="#339966" Font-Bold="True" ForeColor="White" />  
  48.                                             <SortedAscendingCellStyle BackColor="#F7F7F7" />  
  49.                                             <SortedAscendingHeaderStyle BackColor="#487575" />  
  50.                                             <SortedDescendingCellStyle BackColor="#E5E5E5" />  
  51.                                             <SortedDescendingHeaderStyle BackColor="#275353" />  
  52.                                         </asp:GridView>  
  53.                                     </td>  
  54.                                 </tr>  
  55.                                 <tr>  
  56.                                     <td colspan="2"></td>  
  57.                                 </tr>  
  58.                             </table>  
  59.   
  60.                         </fieldset>  
  61.                     </div>  
  62.                 </td>  
  63.             </tr>  
  64.         </table>  
  65.   
  66.     </form>  
  67. </body>  
  68. </html> 

Now my aspx.cs code is:

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Configuration;  
  4. using System.Data;  
  5. using System.Data.SqlClient;  
  6. using System.Linq;  
  7. using System.Web;  
  8. using System.Web.UI;  
  9. using System.Web.UI.WebControls;  
  10.   
  11. namespace DropDownGridView  
  12. {  
  13.     public partial class Default : System.Web.UI.Page  
  14.     {  
  15.         SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["EMPCON"].ConnectionString);  
  16.   
  17.         protected void Page_Load(object sender, EventArgs e)  
  18.         {  
  19.             if (!Page.IsPostBack)  
  20.             {  
  21.                 BindAllManagerDropDown();  
  22.             }  
  23.         }  
  24.   
  25.         protected void BindAllManagerDropDown()  
  26.         {  
  27.             SqlCommand cmd = new SqlCommand();  
  28.             SqlDataAdapter da = new SqlDataAdapter();  
  29.             DataTable dt = new DataTable();  
  30.             try  
  31.             {  
  32.                 cmd = new SqlCommand("Select * from EmployeeTeam WHERE IsManager=1", con);  
  33.                 da.SelectCommand = cmd;  
  34.                 da.Fill(dt);  
  35.                 ddlManager.DataSource = dt;  
  36.                 ddlManager.DataTextField = "Name";  
  37.                 ddlManager.DataValueField = "Employee_Id";  
  38.                 ddlManager.DataBind();  
  39.                 ddlManager.Items.Insert(0, "-- Select Manager --");  
  40.                    
  41.             }  
  42.             catch (Exception ex)  
  43.             {  
  44.   
  45.             }  
  46.             finally  
  47.             {  
  48.                 cmd.Dispose();  
  49.                 da.Dispose();  
  50.                 dt.Clear();  
  51.                 dt.Dispose();  
  52.             }  
  53.         }  
  54.         protected void ddlManager_SelectedIndexChanged(object sender, EventArgs e)  
  55.         {  
  56.             try  
  57.             {  
  58.                 int managerID = Convert.ToInt32(ddlManager.SelectedValue);  
  59.                 BindManagerEMPLOYEE(managerID);  
  60.             }  
  61.             catch (Exception ex)  
  62.             {  
  63.   
  64.             }  
  65.         }  
  66.   
  67.         private void BindManagerEMPLOYEE(int managerID)  
  68.         {  
  69.             DataTable dt = new DataTable();  
  70.             SqlDataAdapter adp = new SqlDataAdapter();  
  71.             try  
  72.             {  
  73.                 SqlCommand cmd = new SqlCommand("select *  from EmployeeTeam where Manager_ID=" + managerID + " ", con);  
  74.                 adp.SelectCommand = cmd;  
  75.                 adp.Fill(dt);  
  76.   
  77.                 if (dt.Rows.Count > 0)  
  78.                 {  
  79.                     GridViewEmployee.DataSource = dt;  
  80.                     GridViewEmployee.DataBind();  
  81.                 }  
  82.                 else  
  83.                 {  
  84.                     GridViewEmployee.DataSource = null;  
  85.                     GridViewEmployee.DataBind();  
  86.                 }  
  87.             }  
  88.             catch (Exception ex)  
  89.             {  
  90.   
  91.             }  
  92.             finally  
  93.             {  
  94.                 dt.Clear();  
  95.                 dt.Dispose();  
  96.                 adp.Dispose();  
  97.             }  
  98.         }  
  99.     }  
  100. } 

The following is the the connection string in the Web.config file:

  1. <connectionStrings>  
  2.    <add name="EMPCON" connectionString="Data Source=INDIA\MSSQLServer2k8;Initial Catalog=TestDB;Integrated Security=True"/>  
  3. </connectionStrings> 

connectionStrings
Figure 3

Figures 4 - 9 show the resutls from executing the application.

run your application
Figure 4

select manager name
Figure 5

emp id
Figure 6

select manager
Figure 7

select record
Figure 8

fill gridview
Figure 9

In the next article I will show this functionality using jQuery.


Similar Articles