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]
  15. GO
  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" %>
  3. <!DOCTYPE html>
  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 />
  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>
  60. </fieldset>
  61. </div>
  62. </td>
  63. </tr>
  64. </table>
  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;
  11. namespace DropDownGridView
  12. {
  13. public partial class Default : System.Web.UI.Page
  14. {
  15. SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["EMPCON"].ConnectionString);
  17. protected void Page_Load(object sender, EventArgs e)
  18. {
  19. if (!Page.IsPostBack)
  20. {
  21. BindAllManagerDropDown();
  22. }
  23. }
  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 --");
  41. }
  42. catch (Exception ex)
  43. {
  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. {
  64. }
  65. }
  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);
  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. {
  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.