Issue with Gridview paging and stored procedure

Question

User-1994446809 posted

Hello Forum,

I have an issue at hand; I recently tried to create a gridview that will show "Page 1 of 42 (830 records) and the numbers", with the current page number being active. As shown in the screenshot.

I made use of a search engine for guidance and discovered that stored procedure is required to execute such. But I want to ask if this cannot be done without stored procedure, as I don’t have stored procedure in my project?

In order to do this I needed to use custom paging and set label for displaying the text, and added Stored Procedure to my project. However, I got an error.

Here is what I did.

I followed through and here is my HTML, CSS, C# and SQL(stored procedure).

HTML:

<div style="width: 80%">
        <br />
        <asp:GridView ID="GridView1" runat="server" HeaderStyle-BackColor="#336699" PageSize="10" AllowPaging="true" GridLines="None"
          AutoGenerateColumns="false" class="table" Width="80%">
            <Columns>
                <asp:BoundField DataField="email" HeaderText="User Email" />
                <asp:BoundField DataField="Name" HeaderText="Name of Organization" />
                <asp:BoundField DataField="CreateDate" HeaderText="Date Created" />
            </Columns>
        </asp:GridView>
        <br />
    <div style="float: left;">
        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" />
        items)&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="Page_Changed" OnClientClick='<%# !Convert.ToBoolean(Eval("Enabled")) ? "return false;" : "" %>'></asp:LinkButton>
                </ItemTemplate>
            </asp:Repeater>
        </div>
    </div>
    </div>

CSS:

<style type="text/css">
    body {
        font-family: Arial;
        font-size: 10pt;
    }
 
    .Item {
        border: 1px solid #ccc;
    }
 
        .Item th {
            background-color: #F7F7F7;
            color: #333;
            font-weight: bold;
        }
 
        .Item th, .Item td {
            padding: 5px;
            border-color: #ccc;
        }
 
    .page_enabled, .page_disabled {
        display: inline-block;
        height: 25px;
        min-width: 30px;
        line-height: 25px;
        text-align: center;
        text-decoration: none;
        border: 1px solid #ccc;
    }
 
    .page_disabled {
        background-color: #007BFF;
        color: white;
    }
 
    .page_enabled {
        background-color: white;
        color: #007BFF !important;
    }
 
    .dvPager {
        border: 1px solid #ccc;
        font-size: 0;
        float: right;
        border-radius: 4px;
    }
 
        .dvPager a {
            font-size: 15px;
        }
</style>

C#:

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class InvoiceRecords : System.Web.UI.Page
{
    SqlCommand cmd = new SqlCommand();
    SqlDataAdapter sda = new SqlDataAdapter();
    DataSet ds = new DataSet();
    DataTable dt = new DataTable();
    SqlConnection con = new SqlConnection("Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\\Dataregister.mdf;Integrated Security=True");

    private int PageSize = 10;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!this.IsPostBack)
        {
            this.GetCustomersPageWise(1);
        }
    }

    private void GetCustomersPageWise(int pageIndex)
    {
        using (SqlConnection con = new SqlConnection("Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\\Dataregister.mdf;Integrated Security=True"))
        {
            using (SqlCommand cmd = new SqlCommand("Dataregister_GetTable", con))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                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 (SqlDataReader sdr = cmd.ExecuteReader())
                {
                    GridView1.DataSource = sdr;
                    GridView1.DataBind();
                    con.Close();
                    int recordCount = Convert.ToInt32(cmd.Parameters["@RecordCount"].Value);
                    this.PopulatePager(recordCount, pageIndex);
                }
            }
        }
    }

    protected void Page_Changed(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<ListItem> pages = new List<ListItem>();
        if (pageCount > 0)
        {
            if (currentPage != 1)
            {
                pages.Add(new ListItem("Prev", (currentPage - 1).ToString()));
            }
            if (pageCount < 4)
            {
                for (int i = 1; i <= pageCount; i++)
                {
                    pages.Add(new ListItem(i.ToString(), i.ToString(), i != currentPage));
                }
            }
            else if (currentPage < 4)
            {
                for (int i = 1; i <= 4; i++)
                {
                    pages.Add(new ListItem(i.ToString(), i.ToString(), i != currentPage));
                }
                pages.Add(new ListItem("...", (currentPage).ToString(), false));
            }
            else if (currentPage > pageCount - 4)
            {
                pages.Add(new ListItem("...", (currentPage).ToString(), false));
                for (int i = currentPage - 1; i <= pageCount; i++)
                {
                    pages.Add(new ListItem(i.ToString(), i.ToString(), i != currentPage));
                }
            }
            else
            {
                pages.Add(new ListItem("...", (currentPage).ToString(), false));
                for (int i = currentPage - 2; i <= currentPage + 2; i++)
                {
                    pages.Add(new ListItem(i.ToString(), i.ToString(), i != currentPage));
                }
                pages.Add(new ListItem("...", (currentPage).ToString(), false));
            }
            if (currentPage != pageCount)
            {
                pages.Add(new 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();
    }
}

SQL (Stored Procedure)

CREATE PROCEDURE Dataregister_GetTable 
    @PageIndex INT 
    ,@PageSize INT 
    ,@RecordCount INT OUT 
AS 
BEGIN 
    SELECT ROW_NUMBER() OVER(ORDER BY email) RowNumber 
            ,email
            ,Name 
            ,CreateDate 
    INTO #Temp 
    FROM Users 
   
    SELECT @RecordCount = COUNT(*) FROM #Temp 
   
    SELECT * FROM #Temp 
    WHERE (RowNumber BETWEEN ((@PageIndex-1) * @PageSize) + 1 AND (@PageIndex * @PageSize)) OR @PageIndex = - 1 
   
    DROP TABLE #Temp  
END

My database name is DataRegister, and I have 3 tables (Users, InvoiceTable, Cardtbl). Please forum, I need help in solving this. And is there no way this can be done without stored procedure?

I need help, please

Answer 1

User475983607 posted

The error clearly states the stored procedure, Dataregister_GetTable, was not found.   Add the stored procedure and/or verify the procedure name matches what's in the database.

Answer 2

User-1994446809 posted

mgebhard

Add the stored procedure

I added the stored procedure. I don't know how to explain further, but I took a screenshot for proper view. I really don't know. In the screenshot, I changed the stored procedure name to match the database name

From these screenshot showing the database name matches the stored procedure name. Please check and tell me what I have done wrong. I will be grateful if you show where i get it all wrong, with that that i will learn.

Thank you

Answer 3

User-1994446809 posted

Hi Mgebhard,

Alternatively, I know how to use paging in the gridview, I just wanted something different. I could use paging in the gridview but I don't know how to make label show current page number and total records in the data table, for example "page 1 of 50 (200 records)"

Answer 4

User475983607 posted

It looks like you did not run the code which will "Create" the stored procedure in the database.  Once you run the code the stored procedure will show up in the Store Procedures folder.

Secondly, the screenshot shows the stored procedure is named Dataregister_log not Dataregister_GetData.

I think you'll be interested in reading the official SQL stored procedure documentation. 

https://docs.microsoft.com/en-us/sql/relational-databases/stored-procedures/create-a-stored-procedure?view=sql-server-ver15

 

Answer 5

User-1994446809 posted

This is an eye opener.

It looks like you did not run the code which will "Create" the stored procedure in the database.  Once you run the code the stored procedure will show up in the Store Procedures folder.

How will I run the code to create stored procedure please?  Because I only created the stored procedure by right-clicking the stored procedure folder and creating the stored procedure.

Answer 6

User475983607 posted

How will I run the code to create stored procedure please?  Because I only created the stored procedure by right-clicking the stored procedure folder and creating the stored procedure.

Right click within the code and select Execute.  There's also a tool bar button.  

Answer 7

User-1994446809 posted

Secondly, the screenshot shows the stored procedure is named Dataregister_log not Dataregister_GetData.

Yes, I actually changed it because I wanted it to have the same name as the database. I actually read that the stored procedure should have the same name as the database; which is why I changed the stored procedure name. the initial name for the stored procedure was Dataregister_GetTable

Answer 8

User475983607 posted

georgeakpan233

Yes, I actually changed it because I wanted it to have the same name as the database. I actually read that the stored procedure should have the same name as the database; which is why I changed the stored procedure name. the initial name for the stored procedure was Dataregister_GetTable

Yeah, that's nonsense.  A stored procedure is similar to a function or method.  A stored procedure should have a descriptive name like GetUserById.

Please read the link in my first post as it covers stored procedures quite well

Answer 9

User-1994446809 posted

mgebhard

Yeah, that's nonsense.

All this, I made a mistake which I later corrected. When creating stored procedure I DID NOT update it, instead i tried to save it. it is my mistake. I really didnt carefully go through the process. I FINALLY GOT TO MAKE IT WORK Mgebhard