ASP Paging DB Results

Ways of paging without being too server intensive

[chris_f]

Hi Guys and Gals,

ASP.net has great abilities when I comes to paging results from a database. However, these abilities are not in classic ASP. I was wondering how other people page db results in classic ASP. The only way I can think of is very server intensive. For example:

First Page: Select top 10
Second Page: Select top 20 - Select top 10
Third Page: Select top 30 - Select top 20

Chris

[wardbekker]

chris_f,

You could use a stored procedure like this:

CREATE PROCEDURE sp_PagedItems
(
@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,
Name varchar(50),
Price currency
)

-- Insert the rows from tblItems into the temp. table
INSERT INTO #TempItems (Name, Price)
SELECT Name,Price FROM tblItem ORDER BY Price

-- 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

[chris_f]

In short ... WOW.

Is that they way everyone else does it? I might just persuade the client to upgrade his hosting to .net.

Chris

[wardbekker]

chris_f,

This stored procedure resides on the sql server, and is independend of the application language (asp script or .net code).

[Woz]

chris_f,

are you working with SQLserver or Access?

Onya
Woz

[chris_f]

In this instance I'm working with ASP and Access. I usually use .Net and SQL which is SO much less painless.

Chris