Post
Ask Question

JQuery: Fill DropDown and Show Records in GridView Format in ASP.Net

The following is the table in design mode.

table design
Figure 1

The following is the 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  
The following is the data in my table:

Data in My Table
Figure 2.

Here in this you can see I have employee records with its Manager Id. So in the drop down I will see only Manage and on selecting Manager from the drop down I will show their team information in the GridView.

Now create a Visual Studio solution as in the following:

create a Visual Studio Solution
Figure 3

Now add a jQuery reference. For that, right-click on the project in Solution Explorer and click Manage NuGet Packages.

manage nuGet Packages
Figure 4

jQuery install
Figure 5

install
Figure 6

jQuery package
Figure 7

Now add a new class to your project's EmployeeDetails.cs with the following code:
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5.   
  6. namespace jQueryDropDownGridViewDemo  
  7. {  
  8.     public class EmployeeDetails  
  9.     {  
  10.         public int Employee_ID { get; set; }  
  11.         public string Name { get; set; }  
  12.         public int Manager_ID { get; set; }  
  13.         public string Email { get; set; }  
  14.         public string Mobile { get; set; }  
  15.         public string Country { get; set; }  
  16.     }  
  17. }  
EmployeeDetails
Figure 8

The following is my aspx:
  1. <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="jQueryDropDownGridViewDemo.Default" %>  
  2.   
  3. <!DOCTYPE html>  
  4.   
  5. <html xmlns="http://www.w3.org/1999/xhtml">  
  6. <head runat="server">  
  7.     <title></title>  
  8.     <script src="Scripts/jquery-2.1.4.min.js"></script>  
  9.     <script type="text/javascript">  
  10.         $(document).ready(function () {  
  11.             $.ajax({  
  12.                 type: "POST",  
  13.                 contentType: "application/json; charset=utf-8",  
  14.                 url: "Default.aspx/BindAllManager",  
  15.                 data: "{}",  
  16.                 dataType: "json",  
  17.                 success: function (data) {  
  18.                     $("#ddlManager").append($("<option></option>").val('0').html("-- Select Manager --"));  
  19.                     $.each(data.d, function (key, value) {  
  20.                         $("#ddlManager").append($("<option></option>").val(value.Employee_ID).html(value.Name));  
  21.                     });  
  22.                 },  
  23.                 error: function (result) {  
  24.                     alert("Error");  
  25.                 }  
  26.             });  
  27.   
  28.             //Capturing Selection Index change of Manager Drop Down List  
  29.             $('#ddlManager').change(function () {  
  30.                 var SelectedText = $(this).find(":selected").text();  
  31.                 var SelectedValue = $(this).val();  
  32.   
  33.                 if (SelectedValue == "0")  
  34.                 {  
  35.                     $('#dvRecords').empty();  
  36.                     alert("Please Select Manager");  
  37.                     return false;  
  38.                 }  
  39.                 $('#dvRecords').empty();  
  40.   
  41.                 var JSONObject = { "ManagerID": SelectedValue };  
  42.                 var jsonData = JSON.stringify(JSONObject);  
  43.   
  44.                 //Filling Grid View  
  45.                 $.ajax({  
  46.                     type: 'POST',  
  47.                     contentType: "application/json; charset=utf-8",  
  48.                     url: 'Default.aspx/BindManagerEmployee',  
  49.                     data: jsonData,  
  50.                     dataType: 'JSON',  
  51.                     success: function (response) {  
  52.                         $('#dvRecords').append("<table style='width:100%;'><tr><td></td></tr><tr style='background-color:orange; color:white;'><th style='width:100px;  text-align:center;'>Employee ID </th><th style='width:160px;  text-align:center;'>Name </th><th style='width:160px; text-align:center;'>Email </th><th style='width:50px; text-align:right; padding-right:70px;'>Mobile </th><th style='width:130px; text-align:left;'>Country </th></tr>")  
  53.                         for (var i = 0; i < response.d.length; i++) {  
  54.                             $('#dvRecords').append("<tr style='background-color:yellow; font-family:verdana; font-size:12pt;'><td style='width:140px;'>" + response.d[i].Employee_ID + "</td><td style='width:200px;'>" + response.d[i].Name + "</td><td style='width:220px;'>" + response.d[i].Email + "</td><td style='width:140px; text-align:left;'>" + response.d[i].Mobile + "</td><td style='width:120px; text-align:left;'>" + response.d[i].Country + "</td></tr>")  
  55.                         }; $('#dvRecords').append("</table>")  
  56.   
  57.                     },  
  58.                     error: function () {  
  59.                         alert("Error");  
  60.                     }  
  61.                 });  
  62.             });  
  63.             return false;  
  64.         });  
  65.   
  66.     </script>  
  67. </head>  
  68. <body>  
  69.     <form id="form1" runat="server">  
  70.   
  71.         <table style="width: 100%; background-color: skyblue; border: solid 10px Red; padding: 10px;">  
  72.             <tr>  
  73.                 <td colspan="2" style="height: 40px; background-color: red; color: white; font-family: Verdana; font-size: 17pt; font-weight: bold; text-align: center;">jQuery: Showing Records On Selecting Value From Drop Down List  
  74.                 </td>  
  75.             </tr>  
  76.             <tr style="height: 40px; background-color: greenyellow; color: blue; font-family: Verdana; font-size: 14pt; text-align: center;">  
  77.                 <td>  
  78.                     <asp:Label ID="lnlManager" runat="server" Text="Select Manager => "></asp:Label></td>  
  79.                 <td>  
  80.                     <asp:DropDownList ID="ddlManager" runat="server" Font-Bold="true" Width="200px" Height="30px"></asp:DropDownList>  
  81.                 </td>  
  82.   
  83.             </tr>  
  84.             <tr>  
  85.                 <td></td>  
  86.             </tr>  
  87.             <tr>  
  88.                 <td colspan="2">  
  89.                     <div id="dvRecords" runat="server"></div>  
  90.                 </td>  
  91.             </tr>  
  92.         </table>  
  93.   
  94.   
  95.   
  96.     </form>  
  97. </body>  
  98. </html>  
Here is the aspx.cs code:
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Data;  
  4. using System.Linq;  
  5. using System.Web;  
  6. using System.Web.Services;  
  7. using System.Web.UI;  
  8. using System.Web.UI.WebControls;  
  9. using System.Data.SqlClient;  
  10. using System.Configuration;  
  11. namespace jQueryDropDownGridViewDemo  
  12. {  
  13.     public partial class Default : System.Web.UI.Page  
  14.     {  
  15.         protected void Page_Load(object sender, EventArgs e)  
  16.         {  
  17.   
  18.         }  
  19.   
  20.         [WebMethod]  
  21.         public static EmployeeDetails[] BindAllManager()  
  22.         {  
  23.             List<EmployeeDetails> details = new List<EmployeeDetails>();  
  24.             DataTable dtManager = new DataTable();  
  25.   
  26.   
  27.             using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["EMPCON"].ConnectionString))  
  28.             {  
  29.                 SqlCommand cmd = new SqlCommand();  
  30.                 SqlDataAdapter da = new SqlDataAdapter();  
  31.                 cmd = new SqlCommand("Select * from EmployeeTeam WHERE IsManager=1", con);  
  32.                 da.SelectCommand = cmd;  
  33.                 da.Fill(dtManager);  
  34.             }  
  35.   
  36.             foreach (DataRow dtrow in dtManager.Rows)  
  37.             {  
  38.                 EmployeeDetails logs = new EmployeeDetails();  
  39.                 logs.Employee_ID = Convert.ToInt32(dtrow["Employee_ID"].ToString());  
  40.                 logs.Name = dtrow["Name"].ToString();  
  41.                 details.Add(logs);  
  42.             }  
  43.             return details.ToArray();  
  44.         }  
  45.   
  46.         [WebMethod]  
  47.         public static List<EmployeeDetails> BindManagerEmployee(int ManagerID)  
  48.         {  
  49.             List<EmployeeDetails> details = new List<EmployeeDetails>();  
  50.             DataTable dtManager = new DataTable();  
  51.   
  52.   
  53.             using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["EMPCON"].ConnectionString))  
  54.             {  
  55.                 SqlCommand cmd = new SqlCommand();  
  56.                 SqlDataAdapter da = new SqlDataAdapter();  
  57.                 cmd = new SqlCommand("Select * from EmployeeTeam WHERE Manager_ID='" + ManagerID + "'", con);  
  58.                 da.SelectCommand = cmd;  
  59.                 da.Fill(dtManager);  
  60.             }  
  61.   
  62.             foreach (DataRow dtrow in dtManager.Rows)  
  63.             {  
  64.                 EmployeeDetails logs = new EmployeeDetails();  
  65.                 logs.Employee_ID = Convert.ToInt32(dtrow["Employee_ID"].ToString());  
  66.                 logs.Name = dtrow["Name"].ToString();  
  67.                 logs.Email = dtrow["Email"].ToString();  
  68.                 logs.Mobile = dtrow["Mobile"].ToString();  
  69.                 logs.Country = dtrow["Country"].ToString();  
  70.                 details.Add(logs);  
  71.             }  
  72.             return details;  
  73.         }  
  74.     }  
  75. }  
I defined my connection string in the web.config file as in the following:
  1. <?xml version="1.0"?>  
  2. <!--  
  3.   For more information on how to configure your ASP.NET application, please visit  
  4.   http://go.microsoft.com/fwlink/?LinkId=169433  
  5.   -->  
  6. <configuration>  
  7.     <system.web>  
  8.       <compilation debug="true" targetFramework="4.5" />  
  9.       <httpRuntime targetFramework="4.5" />  
  10.     </system.web>  
  11.   <connectionStrings>  
  12.     <add name="EMPCON" connectionString="Data Source=INDIA\MSSQLServer2k8;Initial Catalog=TestDB;Integrated Security=True"/>  
  13.   </connectionStrings>  
  14. </configuration>  
code
Figure 9

Now run your application:

select value
Figure 10

select manager name
Figure 11

showingt record
Figure 12

select manager
Figure 13

emp id
Figure 14

jQuery
Figure 15

 


Similar Articles