GridView Pagination Issue

Question

There seem to be an issue with my GridView pagination, each time I click on the next page number or the "Next" button, it is either the GridView page skips or it returns back to GridView page "1".

Example: If I am on page 1, and I click on "Next" or I click on "2", it will show page "2" of the GridView. But when I continue and click on button "3" or "4", it will go back to page "1"

My GridView Pagechange

<div style="float: right; font-size: 10pt; margin-right: 1%;">
                                    Page&nbsp;
        <asp:Label ID="lblPageIndex" runat="server" Text="Label" />
                                    &nbsp;of&nbsp;
        <asp:Label ID="lblTotalPage" runat="server" />
                                    (<asp:Label ID="lblTotal" runat="server" />
                                    Records)&nbsp;&nbsp;
        <div class="dvPager">
            <asp:Repeater ID="rptPager" runat="server">
                <ItemTemplate>
                    <asp:LinkButton ID="lnkPage" runat="server" Text='<%#Eval("Text") %>' CommandArgument='<%# Eval("Value") %>'
                        CssClass='<%# Convert.ToBoolean(Eval("Enabled")) ? "page_enabled" : "page_disabled" %>'
                        OnClick="Change_Page" OnClientClick='<%# !Convert.ToBoolean(Eval("Enabled")) ? "return false;" : "" %>'></asp:LinkButton>
                </ItemTemplate>
            </asp:Repeater>
        </div>
                                </div>

protected void Change_Page(object sender, EventArgs e)
        {
            int pageIndex = int.Parse((sender as LinkButton).CommandArgument);
            this.GetCustomersPageWise(pageIndex);
        }
        private void PopulatePager(int recordCount, int currentPage)
        {
            double dblPageCount = (double)((decimal)recordCount / (decimal)PageSize);
            int pageCount = (int)Math.Ceiling(dblPageCount);
            List<System.Web.UI.WebControls.ListItem> pages = new List<System.Web.UI.WebControls.ListItem>();
            if (pageCount > 0)
            {
                if (currentPage != 1)
                {
                    pages.Add(new System.Web.UI.WebControls.ListItem("Prev", (currentPage - 1).ToString()));
                }
                if (pageCount < 4)
                {
                    for (int i = 1; i <= pageCount; i++)
                    {
                        pages.Add(new System.Web.UI.WebControls.ListItem(i.ToString(), i.ToString(), i != currentPage));
                    }
                }
                else if (currentPage < 4)
                {
                    for (int i = 1; i <= 4; i++)
                    {
                        pages.Add(new System.Web.UI.WebControls.ListItem(i.ToString(), i.ToString(), i != currentPage));
                    }
                    pages.Add(new System.Web.UI.WebControls.ListItem("...", (currentPage).ToString(), false));
                }
                else if (currentPage > pageCount - 4)
                {
                    pages.Add(new System.Web.UI.WebControls.ListItem("...", (currentPage).ToString(), false));
                    for (int i = currentPage - 1; i <= pageCount; i++)
                    {
                        pages.Add(new System.Web.UI.WebControls.ListItem(i.ToString(), i.ToString(), i != currentPage));
                    }
                }
                else
                {
                    pages.Add(new System.Web.UI.WebControls.ListItem("...", (currentPage).ToString(), false));
                    for (int i = currentPage - 2; i <= currentPage + 2; i++)
                    {
                        pages.Add(new System.Web.UI.WebControls.ListItem(i.ToString(), i.ToString(), i != currentPage));
                    }
                    pages.Add(new System.Web.UI.WebControls.ListItem("...", (currentPage).ToString(), false));
                }
                if (currentPage != pageCount)
                {
                    pages.Add(new System.Web.UI.WebControls.ListItem("Next", (currentPage + 1).ToString()));
                }
            }
            rptPager.DataSource = pages;
            rptPager.DataBind();

            lblPageIndex.Text = currentPage.ToString();
            lblTotalPage.Text = ((recordCount / PageSize) + ((recordCount % PageSize) > 0 ? 1 : 0)).ToString();
            lblTotal.Text = recordCount.ToString();
        }
        protected void OnPageIndexChanging(object sender, GridViewPageEventArgs e)
        {
            GridView1.PageIndex = e.NewPageIndex;
            this.GetCustomersPageWise(1);
        }
        private void GetCustomersPageWise(int pageIndex)
        {
            try
            {
                using (SqlConnection con = new SqlConnection())
                {
                    con.ConnectionString = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
                    using (SqlCommand cmd = new SqlCommand("DataTable", con))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.AddWithValue("@CreatedBy", createby.Text.Trim());
                        cmd.Parameters.AddWithValue("@PageIndex", pageIndex);
                        cmd.Parameters.AddWithValue("@PageSize", PageSize);
                        cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4);
                        cmd.Parameters["@RecordCount"].Direction = ParameterDirection.Output;
                        con.Open();
                        using (SqlDataAdapter sda = new SqlDataAdapter())
                        {
                            cmd.Connection = con;
                            sda.SelectCommand = cmd;
                            using (DataTable dt = new DataTable())
                            {
                                sda.Fill(dt);
                                using (SqlDataReader dr = cmd.ExecuteReader())
                                if (dr.HasRows)
                                    {
                                        Template.Visible = true;
                                        GridView1.DataSource = dt;
                                        GridView1.DataBind();
                                    }
                                    else
                                    {
                                        Template.Visible = false;
                                    }
                                int recordCount = Convert.ToInt32(cmd.Parameters["@RecordCount"].Value);
                                this.PopulatePager(recordCount, pageIndex);
                            }
                        }
                        con.Close();
                    }
                }
            }
            catch (SqlException ex)
            {
                string msg = "Error:";
                msg += ex.Message;
                throw new Exception(msg);
            }
        }

Answer 1

Hi @Donald Symmons,

Your code works. I don't know if it's a problem with your Stored Procedure.

I used the recipient Stored Procedure you provided earlier for testing. I've included the code I tested and the results below so you can take a look.

CREATE PROCEDURE [dbo].[Recipients]
	 @CreatedBy VARCHAR(50)
	,@PageIndex INT
	,@PageSize INT
	,@RecordCount INT OUT

AS
	BEGIN
    SELECT ROW_NUMBER() OVER(ORDER BY Id DESC) RowNumber
	        ,Id
			,Recipient
			,RecEmail
			,DocumentData		
			,AwardDate
			,CreatedBy
    INTO #Temp 
    FROM DpcumentTable
	WHERE CreatedBy = @CreatedBy ORDER BY Id DESC

	SELECT @RecordCount = COUNT(*) FROM #Temp

	SELECT * FROM #Temp
    WHERE (RowNumber BETWEEN ((@PageIndex-1) * @PageSize) + 1 AND (@PageIndex * @PageSize)) OR @PageIndex = - 1 ORDER BY Id DESC
   
    DROP TABLE #Temp 

	END

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
       <asp:ScriptManager ID="ScriptManager1" runat="server"></asp:ScriptManager>
        <div class="col-sm-11" style="width: 100%; margin: 0 auto; padding: 10px; margin-right: auto; margin-left: auto;">
            <asp:Label ID="createby" runat="server" Text="4"></asp:Label>
            <asp:UpdatePanel ID="panel" runat="server" ChildrenAsTriggers="true">
                <ContentTemplate>
                    <div class="container-fluid p-3 mb5 bg-white rounded" id="card" style="margin: 0 auto; padding: 10px; border: 1.3px solid #e4e7e8;">
                        <asp:GridView ID="GridView1" runat="server" GridLines="None" DataKeyNames="Id" AllowPaging="true" HeaderStyle-BackColor="#fdfdfd" HeaderStyle-Font-Bold="false" HeaderStyle-ForeColor="#05214d" HeaderStyle-Font-Size="10pt" Font-Size="9pt"
                            AutoGenerateColumns="false" HeaderStyle-HorizontalAlign="left" RowStyle-HorizontalAlign="Left" OnPageIndexChanging="OnPageIndexChanging" class="table" Width="100%">
                            <EmptyDataTemplate>
                                <div style="text-align: center; font-weight: 500; margin-top: 2%;">
                                    <i class="fal fa-file-times" style="margin: 0 auto; font-size: 30pt; color: #145c7c;"></i>
                                    <p id="P1" runat="server" style="font-size: 11pt; font-weight: 400;">No Recipient</p>
                                </div>
                            </EmptyDataTemplate>
                            <Columns>
                                <asp:TemplateField>
                                    <ItemTemplate>
                                        <asp:CheckBox ID="CheckSelected" runat="server" />
                                    </ItemTemplate>
                                </asp:TemplateField>
                                <asp:BoundField DataField="Id" HeaderText="ID" HeaderStyle-Font-Bold="false" />
                                <asp:BoundField DataField="Recipient" HeaderText="Recipients" HeaderStyle-Font-Bold="false" />

                                <asp:TemplateField HeaderText="Email" HeaderStyle-Font-Bold="false">
                                    <ItemTemplate>
                                        <asp:HyperLink ID="emailLink" runat="server" Text='<%# Eval("RecEmail") %>' NavigateUrl='<%# Eval("RecEmail", "mailto:{0}") %>' />
                                    </ItemTemplate>
                                </asp:TemplateField>
                                <asp:TemplateField>
                                    <ItemTemplate>
                                        <asp:HiddenField ID="hfSeq" Value='<%# Eval("DocumentData") != System.DBNull.Value ? Convert.ToBase64String((byte[])Eval("DocumentData")) : string.Empty %>' runat="server" />
                                      
                                    </ItemTemplate>
                                </asp:TemplateField>

                                <asp:BoundField DataField="AwardDate" HeaderText="Date" HeaderStyle-Font-Bold="false" />

                            </Columns>
                        </asp:GridView>

                    <div style="float: right; font-size: 10pt; margin-right: 1%;">
                                    Page&nbsp;
        <asp:Label ID="lblPageIndex" runat="server" Text="Label" />
                                    &nbsp;of&nbsp;
        <asp:Label ID="lblTotalPage" runat="server" />
                                    (<asp:Label ID="lblTotal" runat="server" />
                                    Records)&nbsp;&nbsp;
        <div class="dvPager">
            <asp:Repeater ID="rptPager" runat="server">
                <ItemTemplate>
                    <asp:LinkButton ID="lnkPage" runat="server" Text='<%#Eval("Text") %>' CommandArgument='<%# Eval("Value") %>'
                        CssClass='<%# Convert.ToBoolean(Eval("Enabled")) ? "page_enabled" : "page_disabled" %>'
                        OnClick="Change_Page" OnClientClick='<%# !Convert.ToBoolean(Eval("Enabled")) ? "return false;" : "" %>'></asp:LinkButton>
                </ItemTemplate>
            </asp:Repeater>
        </div>
                                </div>
                        <br />
                        <br />
                    </div>
                </ContentTemplate>
            </asp:UpdatePanel>
        </div>
  
    </form>
</body>
</html>

 protected void Page_Load(object sender, EventArgs e)
        {
            if (!this.IsPostBack)
            {
                string constr = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
                using (SqlConnection con = new SqlConnection(constr))
                {
                    using (SqlCommand cmd = new SqlCommand("SELECT * FROM DpcumentTable"))
                    {
                        using (SqlDataAdapter sda = new SqlDataAdapter())
                        {

                            cmd.Connection = con;
                            sda.SelectCommand = cmd;
                            using (DataTable dt = new DataTable())
                            {
                                sda.Fill(dt);

                                GridView1.DataSource = dt;
                                GridView1.DataBind();
                            }
                        }
                    }
                }
                this.GetCustomersPageWise(1);
            }
        }
        int PageSize = 2;

        protected void Change_Page(object sender, EventArgs e)
        {
            int pageIndex = int.Parse((sender as LinkButton).CommandArgument);
            this.GetCustomersPageWise(pageIndex);
        }
        private void PopulatePager(int recordCount, int currentPage)
        {
            double dblPageCount = (double)((decimal)recordCount / (decimal)PageSize);
            int pageCount = (int)Math.Ceiling(dblPageCount);
            List<System.Web.UI.WebControls.ListItem> pages = new List<System.Web.UI.WebControls.ListItem>();
            if (pageCount > 0)
            {
                if (currentPage != 1)
                {
                    pages.Add(new System.Web.UI.WebControls.ListItem("Prev", (currentPage - 1).ToString()));
                }
                if (pageCount < 4)
                {
                    for (int i = 1; i <= pageCount; i++)
                    {
                        pages.Add(new System.Web.UI.WebControls.ListItem(i.ToString(), i.ToString(), i != currentPage));
                    }
                }
                else if (currentPage < 4)
                {
                    for (int i = 1; i <= 4; i++)
                    {
                        pages.Add(new System.Web.UI.WebControls.ListItem(i.ToString(), i.ToString(), i != currentPage));
                    }
                    pages.Add(new System.Web.UI.WebControls.ListItem("...", (currentPage).ToString(), false));
                }
                else if (currentPage > pageCount - 4)
                {
                    pages.Add(new System.Web.UI.WebControls.ListItem("...", (currentPage).ToString(), false));
                    for (int i = currentPage - 1; i <= pageCount; i++)
                    {
                        pages.Add(new System.Web.UI.WebControls.ListItem(i.ToString(), i.ToString(), i != currentPage));
                    }
                }
                else
                {
                    pages.Add(new System.Web.UI.WebControls.ListItem("...", (currentPage).ToString(), false));
                    for (int i = currentPage - 2; i <= currentPage + 2; i++)
                    {
                        pages.Add(new System.Web.UI.WebControls.ListItem(i.ToString(), i.ToString(), i != currentPage));
                    }
                    pages.Add(new System.Web.UI.WebControls.ListItem("...", (currentPage).ToString(), false));
                }
                if (currentPage != pageCount)
                {
                    pages.Add(new System.Web.UI.WebControls.ListItem("Next", (currentPage + 1).ToString()));
                }
            }
            rptPager.DataSource = pages;
            rptPager.DataBind();

            lblPageIndex.Text = currentPage.ToString();
            lblTotalPage.Text = ((recordCount / PageSize) + ((recordCount % PageSize) > 0 ? 1 : 0)).ToString();
            lblTotal.Text = recordCount.ToString();
        }
        protected void OnPageIndexChanging(object sender, GridViewPageEventArgs e)
        {
            GridView1.PageIndex = e.NewPageIndex;
            this.GetCustomersPageWise(1);
        }
        private void GetCustomersPageWise(int pageIndex)
        {
            try
            {
                using (SqlConnection con = new SqlConnection())
                {
                    con.ConnectionString = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
                    using (SqlCommand cmd = new SqlCommand("Recipients", con))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.AddWithValue("@CreatedBy", createby.Text.Trim());
                        cmd.Parameters.AddWithValue("@PageIndex", pageIndex);
                        cmd.Parameters.AddWithValue("@PageSize", PageSize);
                        cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4);
                        cmd.Parameters["@RecordCount"].Direction = ParameterDirection.Output;
                        con.Open();
                        using (SqlDataAdapter sda = new SqlDataAdapter())
                        {
                            cmd.Connection = con;
                            sda.SelectCommand = cmd;
                            using (DataTable dt = new DataTable())
                            {
                                sda.Fill(dt);
                                using (SqlDataReader dr = cmd.ExecuteReader())
                                    if (dr.HasRows)
                                    {
                                        //Template.Visible = true;
                                        GridView1.DataSource = dt;
                                        GridView1.DataBind();
                                    }
                                    else
                                    {
                                        //Template.Visible = false;
                                    }
                                int recordCount = Convert.ToInt32(cmd.Parameters["@RecordCount"].Value);
                                this.PopulatePager(recordCount, pageIndex);
                            }
                        }
                        con.Close();
                    }
                }
            }
            catch (SqlException ex)
            {
                string msg = "Error:";
                msg += ex.Message;
                throw new Exception(msg);
            }
        }

Best regards,
Lan Huang

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.