asp paging

[adonweb]

How can implement asp paging with sql server 2000? Please post , if u have any code samples.

[yamaharuss]

Using an sproc is a great way to page in ASP

CREATE PROCEDURE Get_Users
(
@Page int,
@RecsPerPage int
)
AS

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


--Create a temporary table
CREATE TABLE #TempItems
(
ID int IDENTITY,
UserID int,
UserFName varchar(25),
UserLName varchar(30),
StreetAddress varchar(50),
City varchar(30),
State varchar(2),
Zip varchar(20),
Email varchar(50),
Phone varchar(20),
SignDate smalldatetime,
LastOnDate smalldatetime,
)


-- Insert the rows from tblItems into the temp. table
INSERT INTO #TempItems (UserID,UserFName,UserLName,StreetAddress,City,State,Zip,Email,Phone,SignDate,LastOnDate)
SELECT UserID,UserFName,UserLName,StreetAddress,City,State,Zip,Email,Phone,SignDate,LastOnDate FROM Users ORDER BY UserLName

-- 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.ID >= @LastRec
)
FROM #TempItems
WHERE ID > @FirstRec AND ID < @LastRec


-- Turn NOCOUNT back OFF
SET NOCOUNT OFF
GO



HERE IS THE PAGE CODE





'How many records per page do we want to show?
Const iRecordsPerPage = 25

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

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

'Show the paged results
strSQL = "Get_Users " & currentPage & "," & iRecordsPerPage
Set objRS = Conn.Execute(strSQL)
'objRS.Open strSQL, objConn

'See if we're on the last page
if Not objRS.EOF then
if CInt(objRS("MoreRecords")) > 0 then
bolLastPage = False
else
bolLastPage = True
end if
arrData = objRS.GetRows()
Set objRS = nothing
end if




arID = 1
arFName = 2
arLName = 3

For i = 0 to Ubound(arrData,2)
If i MOD 2 then bgC = "#EEEEEE" Else bgC = "#FFFFFF"
'PUT THE DATA HERE
Response.Write ("<TD nowrap><font Size='-1'>" & arrData(arID,i) & "&nbsp;</font></TD>")
Next
Conn.Close


'Only show the previous button if we are NOT on the first page
if currentPage > 1 then %>
<INPUT TYPE=BUTTON VALUE="<< Previous <%=iMaxRecords%> Records"
ONCLICK="document.location.href='Users.asp?page=<%=currentPage-1%>'">
&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="Next <%=iMaxRecords%> Records >>"
ONCLICK="document.location.href=Users.asp?page=<%=currentPage+1%>'">
<% end if %>
</CENTER>