E
Eugene Anthony
I have created a table is ms sql:
create table categories
(
CategoryID int IDENTITY,
CategoryDescription varchar(30),
ParentCategoryID int
);
I have also created a stored procedure in ms sql:
I have created an asp file with the following code to perform paging:
<!--#include file="database_Function.asp"-->
<%create procedure sp_PagedCategories
@Page int,
@RecsPerPage int
AS SET NOCOUNT ON
CREATE TABLE #TempItems
(
CategoryID int IDENTITY,
CategoryDescription varchar(30),
ParentCategoryID int
)
INSERT INTO #TempItems (CategoryDescription, ParentCategoryID) SELECT
CategoryDescription, ParentCategoryID FROM Categories
Declare @FirstRecord int, @LastRecord int
Select @FirstRecord = (@Page - 1) * @RecsPerPage
Select @LastRecord = (@Page * @RecsPerPage + 1)
SELECT *,MoreRecords = (SELECT COUNT(*) FROM #TempItems TI WHERE
TI.CategoryID >= @LastRecord) FROM #TempItems WHERE CategoryID >
@FirstRecord AND CategoryID < @LastRecord
SET NOCOUNT OFF
Return
GO
Const iRecordsPerPage = 10
Dim currentPage
Dim bolLastPage
if Len(Request.QueryString("Page")) = 0 then
currentPage = 1
else
currentPage = CInt(Request.QueryString("Page"))
end if
openDB()
Set rs = Server.CreateObject("ADODB.Recordset")
objConn.sp_PagedCategories currentPage, iRecordsPerPage, rs
if not rs.EOF then
if CInt(rs("MoreRecords"))>0 then
bolLastPage = false
else
bolLastPage = true
end if
end if
%>
<TABLE>
<% do while not rs.eof %>
<TR><TD><a
href="modifyCategories.asp?CategoryID=<%=rs("CategoryID")%>"><%=rs("Cate
goryDescription")%></a></TD></TR>
<%
rs.movenext
loop
%>
</TABLE>
<% if currentPage > 1 then %>
<a href="listCategories.asp?Page=<%=currentPage-1%>">Previous</a>
<% end if %>
<% if not bolLastPage then %>
<a href="listCategories.asp?Page=<%=currentPage+1%>">Next</a>
<% end if %><%
closeRS()
closeDB()
%>
Problem:
Everything works fine. However when I change my stored procedure as in:
create procedure sp_PagedCategories
@Page int,
@RecsPerPage int
AS SET NOCOUNT ON
CREATE TABLE #TempItems
(
CategoryID int,
CategoryDescription varchar(30),
ParentCategoryID int
)
INSERT INTO #TempItems (CategoryID, CategoryDescription,
ParentCategoryID) SELECT CategoryID, CategoryDescription,
ParentCategoryID FROM Categories
Declare @FirstRecord int, @LastRecord int
Select @FirstRecord = (@Page - 1) * @RecsPerPage
Select @LastRecord = (@Page * @RecsPerPage + 1)
SELECT *,MoreRecords = (SELECT COUNT(*) FROM #TempItems TI WHERE
TI.CategoryID >= @LastRecord) FROM #TempItems WHERE CategoryID >
@FirstRecord AND CategoryID < @LastRecord
SET NOCOUNT OFF
Return
GO
The result is different. The listing is incorrect.
Your help is kindly appreciated.
Eugene Anthony
create table categories
(
CategoryID int IDENTITY,
CategoryDescription varchar(30),
ParentCategoryID int
);
I have also created a stored procedure in ms sql:
I have created an asp file with the following code to perform paging:
<!--#include file="database_Function.asp"-->
<%create procedure sp_PagedCategories
@Page int,
@RecsPerPage int
AS SET NOCOUNT ON
CREATE TABLE #TempItems
(
CategoryID int IDENTITY,
CategoryDescription varchar(30),
ParentCategoryID int
)
INSERT INTO #TempItems (CategoryDescription, ParentCategoryID) SELECT
CategoryDescription, ParentCategoryID FROM Categories
Declare @FirstRecord int, @LastRecord int
Select @FirstRecord = (@Page - 1) * @RecsPerPage
Select @LastRecord = (@Page * @RecsPerPage + 1)
SELECT *,MoreRecords = (SELECT COUNT(*) FROM #TempItems TI WHERE
TI.CategoryID >= @LastRecord) FROM #TempItems WHERE CategoryID >
@FirstRecord AND CategoryID < @LastRecord
SET NOCOUNT OFF
Return
GO
Const iRecordsPerPage = 10
Dim currentPage
Dim bolLastPage
if Len(Request.QueryString("Page")) = 0 then
currentPage = 1
else
currentPage = CInt(Request.QueryString("Page"))
end if
openDB()
Set rs = Server.CreateObject("ADODB.Recordset")
objConn.sp_PagedCategories currentPage, iRecordsPerPage, rs
if not rs.EOF then
if CInt(rs("MoreRecords"))>0 then
bolLastPage = false
else
bolLastPage = true
end if
end if
%>
<TABLE>
<% do while not rs.eof %>
<TR><TD><a
href="modifyCategories.asp?CategoryID=<%=rs("CategoryID")%>"><%=rs("Cate
goryDescription")%></a></TD></TR>
<%
rs.movenext
loop
%>
</TABLE>
<% if currentPage > 1 then %>
<a href="listCategories.asp?Page=<%=currentPage-1%>">Previous</a>
<% end if %>
<% if not bolLastPage then %>
<a href="listCategories.asp?Page=<%=currentPage+1%>">Next</a>
<% end if %><%
closeRS()
closeDB()
%>
Problem:
Everything works fine. However when I change my stored procedure as in:
create procedure sp_PagedCategories
@Page int,
@RecsPerPage int
AS SET NOCOUNT ON
CREATE TABLE #TempItems
(
CategoryID int,
CategoryDescription varchar(30),
ParentCategoryID int
)
INSERT INTO #TempItems (CategoryID, CategoryDescription,
ParentCategoryID) SELECT CategoryID, CategoryDescription,
ParentCategoryID FROM Categories
Declare @FirstRecord int, @LastRecord int
Select @FirstRecord = (@Page - 1) * @RecsPerPage
Select @LastRecord = (@Page * @RecsPerPage + 1)
SELECT *,MoreRecords = (SELECT COUNT(*) FROM #TempItems TI WHERE
TI.CategoryID >= @LastRecord) FROM #TempItems WHERE CategoryID >
@FirstRecord AND CategoryID < @LastRecord
SET NOCOUNT OFF
Return
GO
The result is different. The listing is incorrect.
Your help is kindly appreciated.
Eugene Anthony