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).
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?
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.
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.
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)"
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.
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.
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.
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
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
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
Member
66 Points
175 Posts
Issue with Gridview paging and stored procedure
Oct 09, 2020 01:53 PM|georgeakpan233|LINK
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 <asp:Label ID="lblPageIndex" runat="server" Text="Label" /> of <asp:Label ID="lblTotalPage" runat="server" /> (<asp:Label ID="lblTotal" runat="server" /> items) <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 ENDMy 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
All-Star
53091 Points
23659 Posts
Re: Issue with Gridview paging and stored procedure
Oct 09, 2020 02:21 PM|mgebhard|LINK
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.
Member
66 Points
175 Posts
Re: Issue with Gridview paging and stored procedure
Oct 09, 2020 02:37 PM|georgeakpan233|LINK
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
Member
66 Points
175 Posts
Re: Issue with Gridview paging and stored procedure
Oct 09, 2020 02:47 PM|georgeakpan233|LINK
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)"
All-Star
53091 Points
23659 Posts
Re: Issue with Gridview paging and stored procedure
Oct 09, 2020 02:52 PM|mgebhard|LINK
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
Member
66 Points
175 Posts
Re: Issue with Gridview paging and stored procedure
Oct 09, 2020 03:28 PM|georgeakpan233|LINK
This is an eye opener.
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.
All-Star
53091 Points
23659 Posts
Re: Issue with Gridview paging and stored procedure
Oct 09, 2020 03:36 PM|mgebhard|LINK
Right click within the code and select Execute. There's also a tool bar button.
Member
66 Points
175 Posts
Re: Issue with Gridview paging and stored procedure
Oct 09, 2020 06:20 PM|georgeakpan233|LINK
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
All-Star
53091 Points
23659 Posts
Re: Issue with Gridview paging and stored procedure
Oct 09, 2020 06:42 PM|mgebhard|LINK
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
Member
66 Points
175 Posts
Re: Issue with Gridview paging and stored procedure
Oct 09, 2020 10:44 PM|georgeakpan233|LINK
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