Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

paging using stored procedures

Status
Not open for further replies.
gavray

gavray

Programmer
Jul 17, 2000
65
GB
Hi, Using the code I got from an article at Called Paging using stored procedures. It works fine, but does not as I see presently have the ability to display the total number of records because it creates a temporary table counts the record from that. I've tried ors.recordcount, pagecount, absolute page

Just having next page is not helpfull enough maybe if I could display how many pages there are or records thre are.

Please can anyone help whose solved this problem in the past or fancies the challenge? I've been onto it for a few days :/

here is the stored procedure :

CREATE PROCEDURE sp_PagedItems
(
@Page int,
@RecsPerPage int
--@iPageCount int
)
AS

-- We don't want to return the # of rows inserted
-- into our temporary table, so turn NOCOUNT ON
SET NOCOUNT ON

-- Find out where we will start our records from
DECLARE @RecCount int
SELECT @RecCount = @RecsPerPage * @Page + 1


--Create a temporary table
CREATE TABLE #TempItems
(

pID int IDENTITY,
Region varchar(50),
hours varchar(50),
Archived int,
Company varchar(50),
VID int,
CompanyID varchar(50),
Jobtitle varchar(50),
DatepostedUK varchar(50),
Dateposted smalldatetime,
ID int,
industry varchar(50),


)


-- Insert the rows from tblItems into the temp. table
INSERT INTO #TempItems (Region, hours, Archived, Company,VID,CompanyID,Jobtitle,DatepostedUK,Dateposted,ID,industry)
SELECT TBL_Jobtable.Region, TBL_Jobtable.hours,TBL_Jobtable.Archived , TBL_Company2.Company, TBL_Company2.VID, TBL_Jobtable.CompanyID,TBL_Jobtable.Jobtitle, TBL_Jobtable.DatepostedUK,TBL_Jobtable.Dateposted, TBL_Jobtable.ID, TBL_Jobtable.industry FROM TBL_Jobtable
RIGHT JOIN TBL_Company2
ON TBL_Company2.VID = TBL_JobTable.CompanyID
WHERE Archived='0'




-- Find out the first and last record we want
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@Page - 1) * @RecsPerPage
SELECT @LastRec = (@Page * @RecsPerPage + 1)

-- Now, return the set of paged records, plus, an indiciation of we
-- have more records or not!
SELECT *,
MoreRecords =
(
SELECT COUNT(*)
FROM #TempItems TI
WHERE TI.pID > @LastRec
)
FROM #TempItems
WHERE pID > @FirstRec AND pID < @LastRec

--RETURN @iPageCount
-- Turn NOCOUNT back OFF
SET NOCOUNT OFF
GO


here is the asp code:<html>
<head></head>
<body>
<!-- This assumes you already have a recordset object
explicitly created name objRS -->

<%
set myConn = Server.CreateObject(&quot;ADODB.Connection&quot;)
myConn.Open Application(&quot;fiftyon_ConnectionString&quot;)
Set objRS=Server.CreateObject(&quot;adodb.Recordset&quot;)
'How many records per page do we want to show?
Const iRecordsPerPage = 10

Dim currentPage 'what page are we on??
Dim bolLastPage 'are we on the last page?

if len(Request.QueryString(&quot;page&quot;)) = 0 then
currentPage = 1
else
currentPage = CInt(Request.QueryString(&quot;page&quot;))
end if

'Show the paged results
strSQL = &quot;sp_PagedItems &quot; & currentPage & &quot;,&quot; & iRecordsPerPage
objRS.Open strSQL, myConn


'See if we're on the last page
if Not objRS.EOF then
if CInt(objRS(&quot;MoreRecords&quot;)) > 0 then
bolLastPage = False
else
bolLastPage = True
end if
end if
%>
<P>

<TABLE BORDER=0 CELLSPACING=1 CELLPADDING=4 ALIGN=CENTER>
<TR><TH COLSPAN=2 BGCOLOR=NAVY>
<FONT SIZE=+1 COLOR=WHITE>
List of Items
</FONT>
</TH></TR>
<%
Do While Not objRS.EOF %>
<TR><TD ALIGN=LEFT BGCOLOR=GRAY>

</TD><TD ALIGN=CENTER BGCOLOR=GRAY>
<%=(objRS(&quot;Hours&quot;))%>
</TD></TR>
</TD><TD ALIGN=CENTER BGCOLOR=GRAY>
<%=(objRS(&quot;Region&quot;))%>
</TD></TR>
</TD><TD ALIGN=CENTER BGCOLOR=GRAY>
<%=(objRS(&quot;Company&quot;))%>
</TD></TR>
<% objRS.MoveNext
Loop %>
</TABLE>
<P>
<CENTER>
<%
'Only show the previous button if we are NOT on the first page
if currentPage > 1 then %>
<INPUT TYPE=BUTTON VALUE=&quot;<< Previous <%=iMaxRecords%> Records&quot;
ONCLICK=&quot;document.location.href='pagingmsjones.asp?page=<%=currentPage-1%>'&quot;>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<% end if


'Only show the next button if we are NOT on the last page
if Not bolLastPage then %>
<INPUT TYPE=BUTTON VALUE=&quot;Next <%=iMaxRecords%> Records >>&quot;
ONCLICK=&quot;document.location.href='pagingmsjones.asp?page=<%=currentPage+1%>'&quot;>
<% end if %>
</CENTER>

</body>
</html>
 
Sort by date Sort by votes
I assume you would want something like the pages at the bottom of TEK (1 2 3 4 >>), so the way I would do it is by capturing the @@ROWCOUNT after the INSERT statement, then including that with the returned recordset. As long as you don't access that column, you should be able to figure out how many pages you have with some simple division.

ex:

(in SP)
...

-- Insert the rows from tblItems into the temp. table
INSERT INTO #TempItems (Region, hours, Archived, Company,VID,CompanyID,Jobtitle,DatepostedUK,Dateposted,ID,industry)
SELECT TBL_Jobtable.Region, TBL_Jobtable.hours,TBL_Jobtable.Archived , TBL_Company2.Company, TBL_Company2.VID, TBL_Jobtable.CompanyID,TBL_Jobtable.Jobtitle, TBL_Jobtable.DatepostedUK,TBL_Jobtable.Dateposted, TBL_Jobtable.ID, TBL_Jobtable.industry FROM TBL_Jobtable
RIGHT JOIN TBL_Company2
ON TBL_Company2.VID = TBL_JobTable.CompanyID
WHERE Archived='0'

--needs to be here so you capture the number
--of records just inserted
DECLARE @myCnt int
SET @myCnt = @@ROWCOUNT

-- Find out the first and last record we want
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@Page - 1) * @RecsPerPage
SELECT @LastRec = (@Page * @RecsPerPage + 1)

-- Now, return the set of paged records, plus, an indiciation of we
-- have more records or not!
SELECT *,
MoreRecords =
(
SELECT COUNT(*)
FROM #TempItems TI
WHERE TI.pID > @LastRec
) , TotalRecords = @myCnt
FROM #TempItems
WHERE pID > @FirstRec AND pID < @LastRec

(in ASP)
...
<%
'this assumes that you want 15 records per page.
Dim totalRec
totalRec = int(oRS(&quot;totalRecords&quot;))
Response.write round(totalRec / 15) & &quot; Pages total.<br>go to page:&quot;
for i = 1 to totalRec
if totalRec mod i = 0 then
Response.write totalRec mod i & &quot; &quot;
next
%>

while I haven't gotten a chance to test this, this should output out
10 Pages total.
go to page: 1 2 3 4 5 6 7 8 9 10

modify it as you wish.

FYI - the SP uses an inefficient way of getting more records, a better way is like this:

SELECT *,a.MoreRecords,TotalRecords = @myCnt
FROM #TempItems, (
SELECT COUNT(*) as MoreRecords
FROM #TempItems TI
WHERE TI.pID > @LastRec
) a
WHERE pID > @FirstRec AND pID < @LastRec

This is because, in the previous way it needed to scan the #TempItems TI table each time a new row was retrieved. By doing it this way, the #TempItems TI table only needs to be scanned once, saving CPU usage.

hope this helps
leo
 
Upvote 0 Downvote
Thanks Leo,

The one glich I noticed is what if u have 291 records, it will only display 290 and leave the last 291 record unretrieved.


Thanks again,

Gavin
 
Upvote 0 Downvote
gavray here,

I just found out put, >= as below

SELECT *,a.MoreRecords,TotalRecords = @myCnt
FROM #TempItems, (
SELECT COUNT(*) as MoreRecords
FROM #TempItems TI
WHERE TI.pID >= @LastRec
) a
t

 
Upvote 0 Downvote
So how about if i'm calling a Stored Procedure which returns the rows, how do i handle that?

Delton.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

leifoet
  • Locked
  • Question
Using date picker gives an error message, only at application start
Replies
1
Views
385
MarkSweetland
MarkSweetland
Papa Bear
  • Locked
  • Question
Sending emails using CDOSYS
Replies
0
Views
155
Papa Bear
Papa Bear
krabban
  • Locked
  • Question
Using application(&quot;something&quot;) along with regular expressions
Replies
0
Views
141
krabban
krabban
jasonhuibers
  • Locked
  • Question
parse text between two words using vbscript 1
Replies
1
Views
253
ChrisHirst
ChrisHirst
axLW
  • Locked
  • Question
Using Google Recaptcha
Replies
2
Views
189
axLW
axLW

Part and Inventory Search

Sponsor

Back
Top