Display record from multiple tables with custom paging in GridView using jQuery AJAX in ASP.Net

lingers
 
on Aug 02, 2021 05:35 AM
1161 Views

I want to display record from 4 columns in 4 tables into a column in grid view. But it does display the actual number of records. So i tried only a column from one table. The number i got is higher than the 4 columns combined which is not supposed to be so 

So the numbers does not add up, i just noticed it doesn't display the actual number of the 4 column combined. i guess something is wrong with the procedure.

Please help

My code

<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
<script type="text/javascript" src=" https://code.jquery.com/jquery-3.5.1.js"></script>
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.7/css/jquery.dataTables.min.css" />
<script type="text/javascript" src="https://cdn.datatables.net/1.10.7/js/jquery.dataTables.min.js"></script>
<link rel="stylesheet" href="https://code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css" />
<script type="text/javascript" src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>
<script src="ASPSnippets_Pager.min.js" type="text/javascript"></script>
<script type="text/javascript">
    $(function () {
        GetCustomers(1);
        var checked = [];
        $('[id*=chkSelect]').on('click', function () {
            if ($(this).is(":checked")) {
                checked.push($(this).closest('tr').find('td').eq(1).html());
            } else {
                checked.pop($(this).closest('tr').find('td').eq(1).html());
            }

            $('[id*=hfCheckedIds]').val(checked.join(','));
        });
    });

    $(document).on("click", '.Pager .page', function () {
        GetCustomers(parseInt($(this).attr('page')));
    });

    $(document).on('click', '.view', function () {
        $('[id*=hfId]').val($(this).closest('tr').find('td').eq(1).html());
    });
    var i = 0;
    function GetCustomers(pageIndex) {
        $.ajax({
            type: "POST",
            url: "R.aspx/GetCustomers",
            data: '{pageIndex: ' + pageIndex + '}',
            contentType: "application/json; charset=utf-8",
            dataType: "json",
          success: function (response) {
    $('[id$=gvDetails]').prepend($("<thead></thead>").append($('[id$=gvDetails]').find("tr:first"))).DataTable().destroy();
    var xmlDoc = $.parseXML(response.d);
    var xml = $(xmlDoc);
    var customers = xml.find("orders");
    alert(customers.length);
    var row = $("[id$=gvDetails] tbody tr:last-child").eq(0).clone(true);
    $("[id$=gvDetails] tbody tr").not($("[id$=gvDetails] tbody tr:first-child")).remove();
    $.each(customers, function () {
 
        $("td", row).eq(1).html($(this).find("orderid").text());
        $("td", row).eq(2).html($(this).find("posino").text());
        $("td", row).eq(3).html($(this).find("producttype").text());
        $("td", row).eq(4).html($(this).find("pid").text());
   
 
        $("[id$=gvDetails]").append(row);
        row = $("[id$=gvDetails] tbody tr:last-child").eq(0).clone(true);
    });
    $("[id$=gvDetails] tbody tr:first-child").remove();
    if (i != 0) {
        $('[id$=gvDetails]').DataTable({
            "paging": false,
            "info": false
        });
    } else {
        $('[id$=gvDetails]')
            .prepend($("<thead></thead>").append($('[id$=gvDetails]').find("tr:first")))
            .DataTable({
                "paging": false,
                "info": false
            });
    }
    i++;
    var pager = xml.find("Pager");
    $(".Pager").ASPSnippets_Pager({
        ActiveCssClass: "current",
        PagerCssClass: "pager",
        PageIndex: parseInt(pager.find("PageIndex").text()),
        PageSize: parseInt(pager.find("PageSize").text()),
        RecordCount: parseInt(pager.find("RecordCount").text())
    });
},
            failure: function (response) {
                alert(response.responseText);
            },
            error: function (response) {
                alert(response.responseText);
            }
        });
    }
</script>

 

CREATE TABLE [dbo].[job](
    [id] [int] NULL,
    [pid] [nvarchar](50) NULL,
    [embid] [nvarchar](50) NULL,
    [diereferneceno] [nvarchar](50) NULL,
    [gietzdieref] [nvarchar](50) NULL,
    [description] [nvarchar](50) NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[job_cylinder]    Script Date: 8/3/2021 12:35:48 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[job_cylinder](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [pid] [int] NOT NULL,
    [ppid] [nvarchar](251) NULL,
    [posino] [nvarchar](251) NULL,
    [colour] [nvarchar](250) NULL,
    [screen] [nvarchar](251) NULL,
    [stylusangle] [nvarchar](121) NULL,
    [depth] [nvarchar](251) NULL,
    [cellwallvolume] [nvarchar](161) NULL,
    [xdiagonal] [nvarchar](101) NULL,
    [stretchfactor] [nvarchar](141) NULL,
    [engravingtype] [nvarchar](141) NULL,
    [channel] [nvarchar](100) NULL,
    [cylinderdiameter] [nvarchar](100) NULL,
    [circumference] [nvarchar](100) NULL,
    [printwidth] [nvarchar](100) NULL,
    [referencecolour] [nvarchar](100) NULL,
    [cylinderfacewidth] [nvarchar](100) NULL,
    [roughness] [nvarchar](100) NULL,
    [chromelayerthickness] [nvarchar](100) NULL,
    [unit] [int] NOT NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[job_die]    Script Date: 8/3/2021 12:35:48 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[job_die](
    [id] [int] NULL,
    [dieno] [nvarchar](50) NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[job_emboss]    Script Date: 8/3/2021 12:35:48 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[job_emboss](
    [id] [int] NULL,
    [embid] [nvarchar](50) NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[orders]    Script Date: 8/3/2021 12:35:48 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[orders](
    [orderid] [int] NULL,
    [quantity] [int] NOT NULL,
    [dateordered] [nvarchar](25) NOT NULL,
    [dateexpected] [nvarchar](25) NULL,
    [deliverydate] [nvarchar](25) NULL,
    [productcode] [nvarchar](25) NOT NULL,
    [producttype] [int] NOT NULL,
    [posino] [nvarchar](40) NOT NULL,
    [qtyaccepted] [int] NULL,
    [qtyrejected] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[job] ([id], [pid], [embid], [diereferneceno], [gietzdieref], [description]) VALUES (11, N'Test33', N'453', N'570', N'Git1', N'Round1')
GO
INSERT [dbo].[job] ([id], [pid], [embid], [diereferneceno], [gietzdieref], [description]) VALUES (22, N'Test44', N'455', N'567', N'Git2', N'Round1')
GO
INSERT [dbo].[job] ([id], [pid], [embid], [diereferneceno], [gietzdieref], [description]) VALUES (33, N'Test55', N'478', N'589', N'Git3', N'Round1')
GO
SET IDENTITY_INSERT [dbo].[job_cylinder] ON
GO
INSERT [dbo].[job_cylinder] ([id], [pid], [ppid], [posino], [colour], [screen], [stylusangle], [depth], [cellwallvolume], [xdiagonal], [stretchfactor], [engravingtype], [channel], [cylinderdiameter], [circumference], [printwidth], [referencecolour], [cylinderfacewidth], [roughness], [chromelayerthickness], [unit]) VALUES (136, 16, NULL, N'4512082-02-1A', N'BLUE', N'', N'', N'', N'', N'', N'', N'', N'', N'518.623', N'786.823', N'917.71', N'BLUE', N'900', N'', N'', 6)
GO
INSERT [dbo].[job_cylinder] ([id], [pid], [ppid], [posino], [colour], [screen], [stylusangle], [depth], [cellwallvolume], [xdiagonal], [stretchfactor], [engravingtype], [channel], [cylinderdiameter], [circumference], [printwidth], [referencecolour], [cylinderfacewidth], [roughness], [chromelayerthickness], [unit]) VALUES (137, 16, NULL, N'4612082-02-2A', N'RED', N'', N'', N'', N'', N'', N'', N'', N'', N'518.623', N'786.823', N'917.71', N'', N'900', N'', N'', 7)
GO
INSERT [dbo].[job_cylinder] ([id], [pid], [ppid], [posino], [colour], [screen], [stylusangle], [depth], [cellwallvolume], [xdiagonal], [stretchfactor], [engravingtype], [channel], [cylinderdiameter], [circumference], [printwidth], [referencecolour], [cylinderfacewidth], [roughness], [chromelayerthickness], [unit]) VALUES (138, 16, NULL, N'4712082-02-3A', N'GREEN', N'', N'', N'', N'', N'', N'', N'', N'', N'518.623', N'786.823', N'917.71', N'', N'900', N'', N'', 8)
GO
INSERT [dbo].[job_cylinder] ([id], [pid], [ppid], [posino], [colour], [screen], [stylusangle], [depth], [cellwallvolume], [xdiagonal], [stretchfactor], [engravingtype], [channel], [cylinderdiameter], [circumference], [printwidth], [referencecolour], [cylinderfacewidth], [roughness], [chromelayerthickness], [unit]) VALUES (139, 16, NULL, N'4812082-02-4A', N'BLACK', N'', N'', N'', N'', N'', N'', N'', N'', N'518.623', N'786.823', N'917.71', N'', N'900', N'', N'', 9)
GO
SET IDENTITY_INSERT [dbo].[job_cylinder] OFF
GO
INSERT [dbo].[job_die] ([id], [dieno]) VALUES (1, N'Die101')
GO
INSERT [dbo].[job_die] ([id], [dieno]) VALUES (2, N'Die202')
GO
INSERT [dbo].[job_die] ([id], [dieno]) VALUES (3, N'Die303')
GO
INSERT [dbo].[job_die] ([id], [dieno]) VALUES (4, N'Die404')
GO
INSERT [dbo].[job_emboss] ([id], [embid]) VALUES (453, N'filmno1')
GO
INSERT [dbo].[job_emboss] ([id], [embid]) VALUES (454, N'filmno2')
GO
INSERT [dbo].[job_emboss] ([id], [embid]) VALUES (455, N'filmno3')
GO
INSERT [dbo].[orders] ([orderid], [quantity], [dateordered], [dateexpected], [deliverydate], [productcode], [producttype], [posino], [qtyaccepted], [qtyrejected]) VALUES (1, 1, N'09/05/2014', N'09/05/2014', N'09/05/2014', N'286', 3, N'136', 2, 1)
GO
INSERT [dbo].[orders] ([orderid], [quantity], [dateordered], [dateexpected], [deliverydate], [productcode], [producttype], [posino], [qtyaccepted], [qtyrejected]) VALUES (2, 1, N'09/05/2014', N'09/05/2014', N'09/05/2014', N'286', 3, N'137', 2, 1)
GO
INSERT [dbo].[orders] ([orderid], [quantity], [dateordered], [dateexpected], [deliverydate], [productcode], [producttype], [posino], [qtyaccepted], [qtyrejected]) VALUES (3, 1, N'09/05/2014', N'09/05/2014', N'09/05/2014', N'286', 3, N'138', 2, 1)
GO
INSERT [dbo].[orders] ([orderid], [quantity], [dateordered], [dateexpected], [deliverydate], [productcode], [producttype], [posino], [qtyaccepted], [qtyrejected]) VALUES (4, 1, N'09/05/2014', N'09/05/2014', N'09/05/2014', N'33', 3, N'139', 2, 1)
GO
INSERT [dbo].[orders] ([orderid], [quantity], [dateordered], [dateexpected], [deliverydate], [productcode], [producttype], [posino], [qtyaccepted], [qtyrejected]) VALUES (5, 1, N'09/05/2014', N'09/05/2014', N'09/05/2014', N'331', 6, N'453', 2, 1)
GO
INSERT [dbo].[orders] ([orderid], [quantity], [dateordered], [dateexpected], [deliverydate], [productcode], [producttype], [posino], [qtyaccepted], [qtyrejected]) VALUES (6, 1, N'09/05/2014', N'09/05/2014', N'09/05/2014', N'286', 6, N'454', 2, 1)
GO
INSERT [dbo].[orders] ([orderid], [quantity], [dateordered], [dateexpected], [deliverydate], [productcode], [producttype], [posino], [qtyaccepted], [qtyrejected]) VALUES (7, 1, N'09/05/2014', N'09/05/2014', N'09/05/2014', N'22', 6, N'455', 2, 1)
GO
INSERT [dbo].[orders] ([orderid], [quantity], [dateordered], [dateexpected], [deliverydate], [productcode], [producttype], [posino], [qtyaccepted], [qtyrejected]) VALUES (8, 1, N'09/05/2014', N'09/05/2014', N'09/05/2014', N'11', 4, N'1', 2, 1)
GO
INSERT [dbo].[orders] ([orderid], [quantity], [dateordered], [dateexpected], [deliverydate], [productcode], [producttype], [posino], [qtyaccepted], [qtyrejected]) VALUES (9, 1, N'09/05/2014', N'09/05/2014', N'09/05/2014', N'286', 4, N'2', 2, 1)
GO
INSERT [dbo].[orders] ([orderid], [quantity], [dateordered], [dateexpected], [deliverydate], [productcode], [producttype], [posino], [qtyaccepted], [qtyrejected]) VALUES (10, 1, N'09/05/2014', N'09/05/2014', N'09/05/2014', N'286', 4, N'3', 2, 1)
GO
INSERT [dbo].[orders] ([orderid], [quantity], [dateordered], [dateexpected], [deliverydate], [productcode], [producttype], [posino], [qtyaccepted], [qtyrejected]) VALUES (10, 1, N'09/05/2014', N'09/05/2014', N'09/05/2014', N'286', 4, N'4', 2, 1)
GO
/****** Object:  StoredProcedure [dbo].[koko]    Script Date: 8/3/2021 12:35:49 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[koko]
      @PageIndex INT = 1
      ,@PageSize INT = 15
      ,@RecordCount INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT DISTINCT  DENSE_RANK() OVER (ORDER BY o.[orderid] desc) AS RowNumber,
        o.orderid,
    CASE WHEN o.producttype = '3' THEN c.posino
    WHEN o.producttype = '4' THEN d.dieno
         WHEN o.producttype = '6' THEN e.embid
         WHEN o.producttype = '1' THEN j.gietzdieref
    END 'posino_dieno_embid_gietzdieref',
    o.producttype,j.pid
    INTO #Results
    FROM orders o
    INNER JOIN job_cylinder c ON c.id = o.posino
    INNER JOIN job_emboss e ON  e.id  = o.posino
         INNER JOIN job_die d ON  d.id  = o.posino
         INNER JOIN job j ON  j.id  = o.productcode
 
    SELECT @RecordCount = COUNT(*)
    FROM #Results
 
    SELECT * FROM #Results
    WHERE RowNumber between (@PageIndex-1)*@PageSize + 1
    AND (((@PageIndex-1)*@PageSize + 1) + @PageSize)-1 OR @PageIndex = -1
 
    DROP TABLE #Results
END
GO

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Aug 07, 2021 02:17 AM

Hi lingers,

Check this example. Now please take its reference and correct your code.

Database

For this example I have used of Northwind database that you can download using the link given below.

Download Northwind Database

SQL

CREATE PROCEDURE GetCustomerOrderPageWise
      @PageIndex INT = 1
      ,@PageSize INT = 10
      ,@RecordCount INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT ROW_NUMBER() OVER
    (
        ORDER BY c.CustomerID ASC
    )AS RowNumber
    ,c.[CustomerID]
    ,c.[ContactName]
	,c.[City]
    ,c.[Country]
    ,e.[FirstName] 'EmployeeName'
    ,CAST(o.[OrderDate] AS VARCHAR(10)) 'OrderDate'
    ,o.[ShipName]
    ,o.[ShipAddress]
	INTO #Results
    FROM Customers c
	INNER JOIN Orders O ON C.CustomerID = O.CustomerID
	INNER JOIN Employees e ON e.EmployeeID = O.EmployeeID
      
    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

HTML

<asp:HiddenField ID="hfId" runat="server" />
<asp:GridView ID="gvDetails" runat="server" AutoGenerateColumns="False"
    BackColor="White" BorderColor="#3366CC" BorderStyle="None"
    Font-Names="Century Gothic" Font-Size="XX-Small"
    Width="100%" Style="margin-bottom: 0px" CssClass="grid">
    <Columns>
        <asp:TemplateField>
            <ItemTemplate>
                <asp:CheckBox ID="chkSelect" runat="server" />
            </ItemTemplate>
        </asp:TemplateField>
        <asp:BoundField DataField="CustomerID" HeaderText="ID" />
        <asp:BoundField DataField="ContactName" HeaderText="Name" />
        <asp:BoundField DataField="City" HeaderText="City" />
        <asp:BoundField DataField="Country" HeaderText="Country" />
        <asp:BoundField DataField="EmployeeName" HeaderText="EmployeeName" />
        <asp:BoundField DataField="OrderDate" HeaderText="OrderDate" />
        <asp:BoundField DataField="ShipName" HeaderText="ShipName" />
        <asp:BoundField DataField="ShipAddress" HeaderText="ShipAddress" />
        <asp:TemplateField>
            <ItemTemplate>
                <asp:Button CssClass="view" Text="Accept" ID="Inkview" runat="server"
                    OnClick="Inkview_Click" ForeColor="white" BackColor="#FF6600" />
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField>
            <ItemTemplate>
                <asp:Button CssClass="view" Text="Reject" ID="lnkView1" runat="server"
                    OnClick="lnkView1_Click" ForeColor="white" BackColor="#FF6600" />
            </ItemTemplate>
        </asp:TemplateField>
    </Columns>
    <HeaderStyle BackColor="green" Font-Bold="True" ForeColor="#CCCCFF" />
    <PagerStyle BackColor="#99CCCC" ForeColor="#003399" HorizontalAlign="Left" />
    <RowStyle BackColor="White" ForeColor="#003399" />
    <SelectedRowStyle BackColor="#009999" Font-Bold="True" ForeColor="#CCFF99" />
    <SortedAscendingCellStyle BackColor="#EDF6F6" />
    <SortedAscendingHeaderStyle BackColor="#0D4AC4" />
    <SortedDescendingCellStyle BackColor="#D6DFDF" />
    <SortedDescendingHeaderStyle BackColor="#002876" />
</asp:GridView>
<br />
<div class="Pager" style="width: 100%"></div>
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.7/css/jquery.dataTables.min.css" />
<script type="text/javascript" src="https://cdn.datatables.net/1.10.7/js/jquery.dataTables.min.js"></script>
<link rel="stylesheet" href="https://code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css" />
<script type="text/javascript" src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>
<script src="ASPSnippets_Pager.min.js" type="text/javascript"></script>
<script type="text/javascript">
    $(function () {
        GetCustomers(1);
    });
    $(document).on("click", '.Pager .page', function () {
        GetCustomers(parseInt($(this).attr('page')));
    });

    $(document).on('click', '.view', function () {
        $('[id*=hfId]').val($(this).closest('tr').find('td').eq(0).html());
    });
    var i = 0;
    function GetCustomers(pageIndex) {
        $.ajax({
            type: "POST",
            url: "Default.aspx/GetCustomers",
            data: '{pageIndex: ' + pageIndex + '}',
            contentType: "application/json; charset=utf-8",
            dataType: "json",
            success: function (response) {
                $('[id$=gvDetails]').prepend($("<thead></thead>").append($('[id$=gvDetails]').find("tr:first"))).DataTable().destroy();
                var xmlDoc = $.parseXML(response.d);
                var xml = $(xmlDoc);
                var customers = xml.find("job_cylinder");
                var row = $("[id$=gvDetails] tbody tr:last-child").eq(0).clone(true);
                $("[id$=gvDetails] tbody tr").not($("[id$=gvDetails] tbody tr:first-child")).remove();
                $.each(customers, function () {
                    $("td", row).eq(1).html($(this).find("CustomerID").text());
                    $("td", row).eq(2).html($(this).find("ContactName").text());
                    $("td", row).eq(3).html($(this).find("City").text());
                    $("td", row).eq(4).html($(this).find("Country").text());
                    $("td", row).eq(5).html($(this).find("EmployeeName").text());
                    $("td", row).eq(6).html($(this).find("OrderDate").text());
                    $("td", row).eq(7).html($(this).find("ShipName").text());
                    $("td", row).eq(8).html($(this).find("ShipAddress").text());
                    $("[id$=gvDetails]").append(row);
                    row = $("[id$=gvDetails] tbody tr:last-child").eq(0).clone(true);
                });
                $("[id$=gvDetails] tbody tr:first-child").remove();
                if (i != 0) {
                    $('[id$=gvDetails]').DataTable({
                        "paging": false,
                        "info": false
                    });
                } else {
                    $('[id$=gvDetails]')
                        .prepend($("<thead></thead>").append($('[id$=gvDetails]').find("tr:first")))
                        .DataTable({
                            "paging": false,
                            "info": false
                        });
                }
                i++;
                var pager = xml.find("Pager");
                $(".Pager").ASPSnippets_Pager({
                    ActiveCssClass: "current",
                    PagerCssClass: "pager",
                    PageIndex: parseInt(pager.find("PageIndex").text()),
                    PageSize: parseInt(pager.find("PageSize").text()),
                    RecordCount: parseInt(pager.find("RecordCount").text())
                });
            },
            failure: function (response) {
                alert(response.responseText);
            },
            error: function (response) {
                alert(response.responseText);
            }
        });
    }
</script>

Namespaces

using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.Services;

Code

private static int PageSize = 5;
protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        BindDummyRow();
    }
}

private void BindDummyRow()
{
    DataTable dummy = new DataTable();
    dummy.Columns.Add("CustomerID");
    dummy.Columns.Add("ContactName");
    dummy.Columns.Add("City");
    dummy.Columns.Add("Country");
    dummy.Columns.Add("EmployeeName");
    dummy.Columns.Add("OrderDate");
    dummy.Columns.Add("ShipName");
    dummy.Columns.Add("ShipAddress");
    dummy.Rows.Add();
    gvDetails.DataSource = dummy;
    gvDetails.DataBind();
}

[WebMethod]
public static string GetCustomers(int pageIndex)
{
    string query = "[GetCustomerOrderPageWise]";
    SqlCommand cmd = new SqlCommand(query);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@PageIndex", pageIndex);
    cmd.Parameters.AddWithValue("@PageSize", PageSize);
    cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4).Direction = ParameterDirection.Output;
    return GetData(cmd, pageIndex).GetXml();
}

private static DataSet GetData(SqlCommand cmd, int pageIndex)
{
    string strConnString = ConfigurationManager.ConnectionStrings["conString"].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, "job_cylinder");
                DataTable dt = new DataTable("Pager");
                dt.Columns.Add("PageIndex");
                dt.Columns.Add("PageSize");
                dt.Columns.Add("RecordCount");
                dt.Rows.Add();
                dt.Rows[0]["PageIndex"] = pageIndex;
                dt.Rows[0]["PageSize"] = PageSize;
                dt.Rows[0]["RecordCount"] = cmd.Parameters["@RecordCount"].Value;
                ds.Tables.Add(dt);
                return ds;
            }
        }
    }
}

protected void Inkview_Click(object sender, EventArgs e)
{

}

protected void lnkView1_Click(object sender, EventArgs e)
{

}

Screenshot

dharmendr
 
on Aug 09, 2021 07:52 AM
on Aug 10, 2021 04:19 AM

Your procedure is not returning any record.

Modify your query with below.

ALTER PROCEDURE [dbo].[koko]
      @PageIndex INT = 1
      ,@PageSize INT = 15
      ,@RecordCount INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT DISTINCT  DENSE_RANK() OVER (ORDER BY o.[orderid] desc) AS RowNumber,
        o.orderid,
    CASE WHEN o.producttype = '3' THEN c.posino
    WHEN o.producttype = '4' THEN d.dieno
         WHEN o.producttype = '6' THEN e.embid
         WHEN o.producttype = '1' THEN j.gietzdieref
    END 'posino_dieno_embid_gietzdieref',
    o.producttype,j.pid
    INTO #Results
    FROM orders o
    INNER JOIN job_cylinder c ON c.id = o.posino
    LEFT JOIN job_emboss e ON  e.id  = o.posino
    LEFT JOIN job_die d ON  d.id  = o.posino
    LEFT JOIN job j ON  j.id  = o.productcode
   
    SELECT @RecordCount = COUNT(*)
    FROM #Results
   
    SELECT * FROM #Results
    WHERE RowNumber between (@PageIndex-1)*@PageSize + 1
    AND (((@PageIndex-1)*@PageSize + 1) + @PageSize)-1 OR @PageIndex = -1
   
    DROP TABLE #Results
END
GO