|
 |
asp_databases thread: 50 records a time
Message #1 by "Xu Gary" <Gary.Xu@p...> on Wed, 24 Jan 2001 14:31:57 -0800
|
|
Hi,
It is common that in web site when query returns a lot of rows, only top 50
are show, and links are provided for next 50 rows of record or any 50 rows
of records, such as 501-550, 551-600 kind of links in the bottom of the web
page. What is the best way to code this? Can someone please provide core
codes both in ASP and SQP side? Thanks a lot!
Message #2 by "James H. Fries" <jhf@w...> on Thu, 25 Jan 2001 01:19:30 -0000
|
|
Hi Gary,
I'm facing a very similiar problem, although it is a bit different in
nature. I've been using in the past paging in ADO. To do this you use the
recordset object's pageSize, pageCount, absolutePage, and absolutePosition
properties. You also have to use a client side cursor.
By setting the pageSize property you can set how many records you want to
see at a time, absolutePage tells you which page you are on,
absolutePosition tells you what position your cursor is on in the
recordset. By using links and a little bit of code you can page back and
forth pretty easily.
The key to your question though, is what is the best way to do this, and
that is where I'm struggling. Using the ADO method works very nice, but it
can become a bottleneck, especially if your result set contains thousands
of records. The reason why is because you get all of the records at one
time, then they are sitting there in memory on the webserver, using
resources. I'm referring to using a disconnected recordset, where the
active connection to the dB = nothing.
There are other ways to do this, such as using "SELECT TOP 50", and then
using some techniques to requery the dB, based on a PK and previous
record.
I feel that using the "SELECT TOP" is a better method because your result
set is only 50 (or whatever size you set) long, which means you are
conserving resources and still provide a paging methoid that works nicely.
The only problem with that though is if you wish to OrderBY something
other than your primary key, which shoots everything in the foot, becuase
your record status is maintained by your PK.
Check out these articles from ASPToday:
http://www.asptoday.com/articles/20000605.htm
and
http://www.asptoday.com/articles/20000823.htm
They can explain it much better than I can. I hope this helps you out.
Message #3 by Imar Spaanjaars <Imar@S...> on Thu, 25 Jan 2001 08:31:53 +0100
|
|
What you are after is called paging. Check out a basic example at
http://www.asp101.com/articles/recordsetpaging/index.asp
There are lots more articles available on this subject, but this one shows
you how to do the basics.
HtH
Imar
At 02:31 PM 1/24/2001 -0800, you wrote:
>Hi,
>
>It is common that in web site when query returns a lot of rows, only top 50
>are show, and links are provided for next 50 rows of record or any 50 rows
>of records, such as 501-550, 551-600 kind of links in the bottom of the web
>page. What is the best way to code this? Can someone please provide core
>codes both in ASP and SQP side? Thanks a lot!
Message #4 by "Walter Burrough" <lists@c...> on Thu, 25 Jan 2001 11:06:16 -0000
|
|
Hi, I looked at the RDS paging and decided it seem to do what I wanted. If users want to page through
records, why make them
download the whole recordset and THEN split it up. They still have to wait - they just don't get to see what they've waited for.
I lifted this straight out of Professional Active Server Pages 3.0 and it's ideal. Just send the page number and pagesize as
parameters from your script and it works.
walter
---------------------------------------------
CREATE PROCEDURE ssp_paged
@iPage int,
@iPageSize int
AS
SET NOCOUNT ON
CREATE TABLE #tempTable
(
id int IDENTITY,
... fieldlist of recordset that will be returned
)
INSERT INTO
#tempTable
(
list the fields
)
SELECT
put your query here
ORDER BY
and order by something reliable so that the order will always be the same
/* now do the paging */
DECLARE @iStart int
DECLARE @iEnd int
DECLARE @iPageCount int
/* count the pages */
SELECT @iPageCount
COUNT(*)
FROM
#tempTable
SELECT @iPageCount = CEILING(@iPageCount / @iPageSize) + 1
/* check the page number */
IF @iPage < 1
SELECT @iPage = 1
IF @iPage > @iPageCount
SELECT @iPage = @iPageCount
/* calculate start and end of page */
SELECT @iStart = (@iPage -1) * @iPageSize
SELECT @iEnd = @iStart + @iPageSize + 1
/* return the page */
SELECT
fieldlist
FROM
#tempTable
WHERE
id > @iStart
AND
id < @iEnd
ORDER BY
id
-----Original Message-----
From: Xu Gary [mailto:Gary.Xu@p...]
Sent: 24 January 2001 22:32
To: ASP Databases
Subject: [asp_databases] 50 records a time
Hi,
It is common that in web site when query returns a lot of rows, only top 50
are show, and links are provided for next 50 rows of record or any 50 rows
of records, such as 501-550, 551-600 kind of links in the bottom of the web
page. What is the best way to code this? Can someone please provide core
codes both in ASP and SQP side? Thanks a lot!
Message #5 by "Xu Gary" <Gary.Xu@p...> on Thu, 25 Jan 2001 08:36:05 -0800
|
|
Thanks for the help. That is real clever. But I do have a concern: every
request from IIS will generate a good size temp table, would it be too much
to the SQL server? Thanks again.
-----Original Message-----
From: Walter Burrough [mailto:lists@c...]
Sent: Thursday, January 25, 2001 3:06 AM
To: ASP Databases
Subject: [asp_databases] RE: 50 records a time
Hi, I looked at the RDS paging and decided it seem to do what I wanted. If
users want to page through records, why make them
download the whole recordset and THEN split it up. They still have to wait -
they just don't get to see what they've waited for.
I lifted this straight out of Professional Active Server Pages 3.0 and it's
ideal. Just send the page number and pagesize as
parameters from your script and it works.
walter
---------------------------------------------
CREATE PROCEDURE ssp_paged
@iPage int,
@iPageSize int
AS
SET NOCOUNT ON
CREATE TABLE #tempTable
(
id int IDENTITY,
... fieldlist of recordset that will be returned
)
INSERT INTO
#tempTable
(
list the fields
)
SELECT
put your query here
ORDER BY
and order by something reliable so that the order will always be the
same
/* now do the paging */
DECLARE @iStart int
DECLARE @iEnd int
DECLARE @iPageCount int
/* count the pages */
SELECT @iPageCount
COUNT(*)
FROM
#tempTable
SELECT @iPageCount = CEILING(@iPageCount / @iPageSize) + 1
/* check the page number */
IF @iPage < 1
SELECT @iPage = 1
IF @iPage > @iPageCount
SELECT @iPage = @iPageCount
/* calculate start and end of page */
SELECT @iStart = (@iPage -1) * @iPageSize
SELECT @iEnd = @iStart + @iPageSize + 1
/* return the page */
SELECT
fieldlist
FROM
#tempTable
WHERE
id > @iStart
AND
id < @iEnd
ORDER BY
id
-----Original Message-----
From: Xu Gary [mailto:Gary.Xu@p...]
Sent: 24 January 2001 22:32
To: ASP Databases
Subject: [asp_databases] 50 records a time
Hi,
It is common that in web site when query returns a lot of rows, only top 50
are show, and links are provided for next 50 rows of record or any 50 rows
of records, such as 501-550, 551-600 kind of links in the bottom of the web
page. What is the best way to code this? Can someone please provide core
codes both in ASP and SQP side? Thanks a lot!
Message #6 by "Walter Burrough" <lists@c...> on Thu, 25 Jan 2001 17:22:55 -0000
|
|
Hmm, good point. Don't know - I'd like to know though!
walter
-----Original Message-----
From: Xu Gary [mailto:Gary.Xu@p...]
Sent: 25 January 2001 16:36
To: ASP Databases
Subject: [asp_databases] RE: 50 records a time
Thanks for the help. That is real clever. But I do have a concern: every
request from IIS will generate a good size temp table, would it be too much
to the SQL server? Thanks again.
-----Original Message-----
From: Walter Burrough [mailto:lists@c...]
Sent: Thursday, January 25, 2001 3:06 AM
To: ASP Databases
Subject: [asp_databases] RE: 50 records a time
Hi, I looked at the RDS paging and decided it seem to do what I wanted. If
users want to page through records, why make them
download the whole recordset and THEN split it up. They still have to wait -
they just don't get to see what they've waited for.
I lifted this straight out of Professional Active Server Pages 3.0 and it's
ideal. Just send the page number and pagesize as
parameters from your script and it works.
walter
---------------------------------------------
CREATE PROCEDURE ssp_paged
@iPage int,
@iPageSize int
AS
SET NOCOUNT ON
CREATE TABLE #tempTable
(
id int IDENTITY,
... fieldlist of recordset that will be returned
)
INSERT INTO
#tempTable
(
list the fields
)
SELECT
put your query here
ORDER BY
and order by something reliable so that the order will always be the
same
/* now do the paging */
DECLARE @iStart int
DECLARE @iEnd int
DECLARE @iPageCount int
/* count the pages */
SELECT @iPageCount
COUNT(*)
FROM
#tempTable
SELECT @iPageCount = CEILING(@iPageCount / @iPageSize) + 1
/* check the page number */
IF @iPage < 1
SELECT @iPage = 1
IF @iPage > @iPageCount
SELECT @iPage = @iPageCount
/* calculate start and end of page */
SELECT @iStart = (@iPage -1) * @iPageSize
SELECT @iEnd = @iStart + @iPageSize + 1
/* return the page */
SELECT
fieldlist
FROM
#tempTable
WHERE
id > @iStart
AND
id < @iEnd
ORDER BY
id
-----Original Message-----
From: Xu Gary [mailto:Gary.Xu@p...]
Sent: 24 January 2001 22:32
To: ASP Databases
Subject: [asp_databases] 50 records a time
Hi,
It is common that in web site when query returns a lot of rows, only top 50
are show, and links are provided for next 50 rows of record or any 50 rows
of records, such as 501-550, 551-600 kind of links in the bottom of the web
page. What is the best way to code this? Can someone please provide core
codes both in ASP and SQP side? Thanks a lot!
|
|
|
View Cart
