ASPForums.Net RSS Feedhttp://www.aspforums.net/Handlers/RSS.ashxLatest additions to the content that appears on ASPForums.Net(c) 2019 www.aspforums.com. All rights reserved.Populate nested HTML Table with Searching and Paging using jQuery Ajax in ASP.Net<p>Hi&nbsp;<a class="username" rel="SajidHussa">SajidHussa,</a></p> <p>Check this example. Now please take its reference and correct your code.</p> <p><strong><span style="text-decoration: underline;">Database</span></strong></p> <p>For this example I have used of Northwind database that you can download using the link given below.</p> <p><a href="https://www.aspsnippets.com/Articles/Install-Microsoft-Northwind-and-Pubs-Sample-databases-in-SQL-Server-Management-Studio.aspx" target="_blank">Download Northwind Database</a></p> <p><strong><span style="text-decoration: underline;">Procedures</span></strong></p> <pre class="brush: sql">-- GetCustomersPageWise &#39;m&#39;,1,5,100 CREATE PROCEDURE [dbo].[GetCustomersPageWise] @SearchTerm VARCHAR(100) = &#39;&#39; ,@PageIndex INT = 1 ,@PageSize INT = 10 ,@RecordCount INT OUTPUT AS BEGIN SET NOCOUNT ON; SELECT ROW_NUMBER() OVER ( ORDER BY [CustomerID] ASC )AS RowNumber ,[CustomerID] ,[CompanyName] ,[ContactName] ,[ContactTitle] ,[Address] ,[City] ,[Region] ,[PostalCode] ,[Country] ,[Phone] ,[Fax] INTO #Results FROM [Customers] WHERE [ContactName] LIKE @SearchTerm + &#39;%&#39; OR @SearchTerm = &#39;&#39; SELECT @RecordCount = COUNT(*) FROM #Results SELECT * FROM #Results WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1 DROP TABLE #Results END GO -- GetOrdersPageWiseBasedOnCustomerId &#39;TOMSP&#39;,1,5,100 CREATE PROCEDURE [dbo].[GetOrdersPageWiseBasedOnCustomerId] @Id VARCHAR(10) ,@PageIndex INT = 1 ,@PageSize INT = 5 ,@RecordCount INT OUTPUT AS BEGIN SET NOCOUNT ON; SELECT ROW_NUMBER() OVER (ORDER BY [CustomerID] ASC) AS RowNumber,* INTO #Results FROM [Orders] WHERE CustomerID = @Id SELECT @RecordCount = COUNT(*) FROM #Results SELECT * FROM #Results WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1 DROP TABLE #Results END</pre> <p><strong><span style="text-decoration: underline;">HTML</span></strong></p> <pre class="brush: html">&lt;html xmlns=&#34;http://www.w3.org/1999/xhtml&#34;&gt; &lt;head id=&#34;Head1&#34; runat=&#34;server&#34;&gt; &lt;title&gt;Nested Html Table Paging&lt;/title&gt; &lt;script type=&#34;text/javascript&#34; src=&#34;http://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js&#34;&gt;&lt;/script&gt; &lt;script src=&#34;ASPSnippets_Pager.min.js&#34; type=&#34;text/javascript&#34;&gt;&lt;/script&gt; &lt;script type=&#34;text/javascript&#34; src=&#34;http://cdnjs.cloudflare.com/ajax/libs/json2/20130526/json2.min.js&#34;&gt;&lt;/script&gt; &lt;script type=&#34;text/javascript&#34;&gt; $(function () { $(&#34;#txtSearch&#34;).focus(); GetCustomers(1, &#39;&#39;); $(&#34;#txtSearch&#34;).keyup(function () { GetCustomers(1, $(this).val()); }); $(&#34;body&#34;).on(&#34;click&#34;, &#34;[src*=plus]&#34;, function () { $(this).closest(&#34;tr&#34;).after(&#34;&lt;tr&gt;&lt;td&gt;&lt;/td&gt;&lt;td colspan = &#39;999&#39;&gt;&#34; + $(this).next().html() + &#34;&lt;/td&gt;&lt;/tr&gt;&#34;) $(this).attr(&#34;src&#34;, &#34;images/minus.png&#34;); GetOrders(1, $(this).closest(&#34;tr&#34;).find(&#39;td&#39;).eq(1).text(), $(this).closest(&#34;tr&#34;).find(&#39;#dvChild&#39;)); }); $(&#34;body&#34;).on(&#34;click&#34;, &#34;[src*=minus]&#34;, function () { $(this).attr(&#34;src&#34;, &#34;images/plus.png&#34;); $(this).closest(&#34;tr&#34;).next().remove(); $(this).closest(&#34;tr&#34;).find(&#39;#dvChild&#39;).empty(); }); }); $(&#34;.Pager .page&#34;).live(&#34;click&#34;, function () { GetCustomers(parseInt($(this).attr(&#39;page&#39;)), $(&#34;#txtSearch&#34;).val()); }); $(&#34;.PagerChild .page&#34;).live(&#34;click&#34;, function () { var customerId = $(this).closest(&#34;tr&#34;).find(&#39;#hfCustomerId&#39;).val(); GetOrders(parseInt($(this).attr(&#39;page&#39;)), customerId, $(this).closest(&#34;tr&#34;).find(&#39;#dvChild&#39;)); }); var row; function GetCustomers(pageIndex, name) { var obj = {}; obj.pageIndex = $.trim(pageIndex); obj.name = $.trim(name); $.ajax({ type: &#34;POST&#34;, url: &#34;Default.aspx/GetCustomers&#34;, data: JSON.stringify(obj), contentType: &#34;application/json; charset=utf-8&#34;, dataType: &#34;json&#34;, success: function (response) { var xmlDoc = $.parseXML(response.d); var xml = $(xmlDoc); var customers = xml.find(&#34;Customers&#34;); if (row == null) { row = $(&#34;[id*=gvCustomers] tr:last-child&#34;).clone(true); } $(&#34;[id*=gvCustomers] tr&#34;).not($(&#34;[id*=gvCustomers] tr:first-child&#34;)).remove(); $.each(customers, function () { var customer = $(this); $(&#34;td&#34;, row).find(&#39;#hfCustomerId&#39;).val($(this).find(&#34;CustomerID&#34;).text()); $(&#34;td&#34;, row).find(&#39;#id&#39;).html($(this).find(&#34;CustomerID&#34;).text()); $(&#34;td&#34;, row).find(&#39;#name&#39;).html($(this).find(&#34;ContactName&#34;).text()); $(&#34;td&#34;, row).find(&#39;#country&#39;).html($(this).find(&#34;City&#34;).text()); $(&#34;[id*=gvCustomers]&#34;).append(row); row = $(&#34;[id*=gvCustomers] tr:last-child&#34;).clone(true); }); var pager = xml.find(&#34;Pager&#34;); $(&#34;.Pager&#34;).ASPSnippets_Pager({ ActiveCssClass: &#34;current&#34;, PagerCssClass: &#34;pager&#34;, PageIndex: parseInt(pager.find(&#34;PageIndex&#34;).text()), PageSize: parseInt(pager.find(&#34;PageSize&#34;).text()), RecordCount: parseInt(pager.find(&#34;RecordCount&#34;).text()) }); } }); } function GetOrders(pageIndex, id, ele) { $(ele).empty(); var obj = {}; obj.pageIndex = $.trim(pageIndex); obj.id = $.trim(id); $.ajax({ type: &#34;POST&#34;, url: &#34;Default.aspx/GetOrders&#34;, data: JSON.stringify(obj), contentType: &#34;application/json; charset=utf-8&#34;, dataType: &#34;json&#34;, success: function (response) { var xmlDoc = $.parseXML(response.d); var xml = $(xmlDoc); var orders = xml.find(&#34;Customers&#34;); var html = &#39;&lt;table&gt;&lt;tr&gt;&lt;th&gt;Order Id&lt;/th&gt;&lt;th&gt;Freight&lt;/th&gt;&lt;th&gt;Ship City&lt;/th&gt;&lt;/tr&gt;&#39;; $.each(orders, function () { html += &#39;&lt;tr&gt;&lt;td&gt;&#39; + $(this).find(&#34;OrderID&#34;).text() + &#39;&lt;/td&gt;&lt;td&gt;&#39; + $(this).find(&#34;Freight&#34;).text() + &#39;&lt;/td&gt;&lt;td&gt;&#39; + $(this).find(&#34;ShipCity&#34;).text() + &#39;&lt;/td&gt;&lt;/tr&gt;&#39;; }); html += &#39;&lt;/table&gt;&lt;br/&gt;&lt;div class=&#34;PagerChild&#34;&gt;&lt;/div&gt;&#39;; $(ele).append(html); var pager = xml.find(&#34;Pager&#34;); $(&#34;.PagerChild&#34;).ASPSnippets_Pager({ ActiveCssClass: &#34;current&#34;, PagerCssClass: &#34;pager&#34;, PageIndex: parseInt(pager.find(&#34;PageIndex&#34;).text()), PageSize: parseInt(pager.find(&#34;PageSize&#34;).text()), RecordCount: parseInt(pager.find(&#34;RecordCount&#34;).text()) }); } }); } &lt;/script&gt; &lt;/head&gt; &lt;body&gt; &lt;form id=&#34;form1&#34; runat=&#34;server&#34;&gt; &lt;center&gt; Name : &lt;input type=&#34;text&#34; name=&#34;name&#34; id=&#34;txtSearch&#34; /&gt; &lt;table id=&#34;gvCustomers&#34;&gt; &lt;tr&gt; &lt;th&gt;&amp;nbsp;&lt;/th&gt; &lt;th&gt;CustomerID&lt;/th&gt; &lt;th&gt;Contact Name&lt;/th&gt; &lt;th&gt;City&lt;/th&gt; &lt;/tr&gt; &lt;tr&gt; &lt;td valign=&#34;top&#34;&gt; &lt;input type=&#34;hidden&#34; id=&#34;hfCustomerId&#34; /&gt; &lt;img alt=&#34;&#34; style=&#34;cursor: pointer&#34; src=&#34;images/plus.png&#34; /&gt; &lt;div id=&#34;dvChild&#34;&gt; &lt;/div&gt; &lt;/td&gt; &lt;td valign=&#34;top&#34;&gt;&lt;span id=&#34;id&#34;&gt;&lt;/span&gt;&lt;/td&gt; &lt;td valign=&#34;top&#34;&gt;&lt;span id=&#34;name&#34;&gt;&lt;/span&gt;&lt;/td&gt; &lt;td valign=&#34;top&#34;&gt;&lt;span id=&#34;country&#34;&gt;&lt;/span&gt;&lt;/td&gt; &lt;/tr&gt; &lt;/table&gt; &lt;br /&gt; &lt;div class=&#34;Pager&#34;&gt;&lt;/div&gt; &lt;/center&gt; &lt;/form&gt; &lt;/body&gt; &lt;/html&gt;</pre> <p><strong><span style="text-decoration: underline;">Namespaces </span></strong></p> <p><strong>C#</strong></p> <pre class="brush: csharp">using System.Configuration; using System.Data; using System.Data.SqlClient; using System.Web.Services;</pre> <p><strong>VB.Net</strong></p> <pre class="brush: vb">Imports System.Configuration Imports System.Data Imports System.Data.SqlClient Imports System.Web.Services</pre> <p><strong><span style="text-decoration: underline;">Code</span></strong></p> <p><strong>C#</strong></p> <pre class="brush: csharp">private static int PageSize = 5; [WebMethod] public static string GetCustomers(int pageIndex, string name) { // Get Customers PageWise. string query = &#34;[GetCustomersPageWise]&#34;; SqlCommand cmd = new SqlCommand(query); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue(&#34;@SearchTerm&#34;, name); cmd.Parameters.AddWithValue(&#34;@PageIndex&#34;, pageIndex); cmd.Parameters.AddWithValue(&#34;@PageSize&#34;, PageSize); cmd.Parameters.Add(&#34;@RecordCount&#34;, SqlDbType.Int, 4).Direction = ParameterDirection.Output; return GetData(cmd, pageIndex).GetXml(); } [WebMethod] public static string GetOrders(int pageIndex, string id) { // Get Orders PageWise. string query = &#34;[GetOrdersPageWiseBasedOnCustomerId]&#34;; SqlCommand cmd = new SqlCommand(query); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue(&#34;@Id&#34;, id); cmd.Parameters.AddWithValue(&#34;@PageIndex&#34;, pageIndex); cmd.Parameters.AddWithValue(&#34;@PageSize&#34;, PageSize); cmd.Parameters.Add(&#34;@RecordCount&#34;, SqlDbType.Int, 4).Direction = ParameterDirection.Output; return GetData(cmd, pageIndex).GetXml(); } private static DataSet GetData(SqlCommand cmd, int pageIndex) { string strConnString = ConfigurationManager.ConnectionStrings[&#34;conString&#34;].ConnectionString; using (SqlConnection con = new SqlConnection(strConnString)) { using (SqlDataAdapter sda = new SqlDataAdapter()) { cmd.Connection = con; sda.SelectCommand = cmd; using (DataSet ds = new DataSet()) { sda.Fill(ds, &#34;Customers&#34;); DataTable dt = new DataTable(&#34;Pager&#34;); dt.Columns.Add(&#34;PageIndex&#34;); dt.Columns.Add(&#34;PageSize&#34;); dt.Columns.Add(&#34;RecordCount&#34;); dt.Rows.Add(); dt.Rows[0][&#34;PageIndex&#34;] = pageIndex; dt.Rows[0][&#34;PageSize&#34;] = PageSize; dt.Rows[0][&#34;RecordCount&#34;] = cmd.Parameters[&#34;@RecordCount&#34;].Value; ds.Tables.Add(dt); return ds; } } } }</pre> <p><strong>VB.Net</strong></p> <pre class="brush: vb">Private Shared PageSize As Integer = 5 &lt;WebMethod&gt; Public Shared Function GetCustomers(ByVal pageIndex As Integer, ByVal name As String) As String Dim query As String = &#34;[GetCustomersPageWise]&#34; Dim cmd As SqlCommand = New SqlCommand(query) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.AddWithValue(&#34;@SearchTerm&#34;, name) cmd.Parameters.AddWithValue(&#34;@PageIndex&#34;, pageIndex) cmd.Parameters.AddWithValue(&#34;@PageSize&#34;, PageSize) cmd.Parameters.Add(&#34;@RecordCount&#34;, SqlDbType.Int, 4).Direction = ParameterDirection.Output Return GetData(cmd, pageIndex).GetXml() End Function &lt;WebMethod&gt; Public Shared Function GetOrders(ByVal pageIndex As Integer, ByVal id As String) As String Dim query As String = &#34;[GetOrdersPageWiseBasedOnCustomerId]&#34; Dim cmd As SqlCommand = New SqlCommand(query) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.AddWithValue(&#34;@Id&#34;, id) cmd.Parameters.AddWithValue(&#34;@PageIndex&#34;, pageIndex) cmd.Parameters.AddWithValue(&#34;@PageSize&#34;, PageSize) cmd.Parameters.Add(&#34;@RecordCount&#34;, SqlDbType.Int, 4).Direction = ParameterDirection.Output Return GetData(cmd, pageIndex).GetXml() End Function Private Shared Function GetData(ByVal cmd As SqlCommand, ByVal pageIndex As Integer) As DataSet Dim strConnString As String = ConfigurationManager.ConnectionStrings(&#34;conString&#34;).ConnectionString Using con As SqlConnection = New SqlConnection(strConnString) Using sda As SqlDataAdapter = New SqlDataAdapter() cmd.Connection = con sda.SelectCommand = cmd Using ds As DataSet = New DataSet() sda.Fill(ds, &#34;Customers&#34;) Dim dt As DataTable = New DataTable(&#34;Pager&#34;) dt.Columns.Add(&#34;PageIndex&#34;) dt.Columns.Add(&#34;PageSize&#34;) dt.Columns.Add(&#34;RecordCount&#34;) dt.Rows.Add() dt.Rows(0)(&#34;PageIndex&#34;) = pageIndex dt.Rows(0)(&#34;PageSize&#34;) = PageSize dt.Rows(0)(&#34;RecordCount&#34;) = cmd.Parameters(&#34;@RecordCount&#34;).Value ds.Tables.Add(dt) Return ds End Using End Using End Using End Function</pre> <p><strong><span style="text-decoration: underline;">Screenshot</span></strong></p> <p><strong><span style="text-decoration: underline;"><img src="https://i.imgur.com/9blEZbA.gif" alt="" width="472" height="456" /></span></strong></p>https://www.aspforums.net:443/Threads/816978/Populate-nested-HTML-Table-with-Searching-and-Paging-using-jQuery-Ajax-in-ASPNet/https://www.aspforums.net:443/Threads/816978/Populate-nested-HTML-Table-with-Searching-and-Paging-using-jQuery-Ajax-in-ASPNet/Wed, 24 Jul 2019 08:29:43 GMT