Paging

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Members online

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,904
Latest member
HealthyVisionsCBDPrice

Latest Threads

Top