ASP paging problem

[david]

Hi
I have written code in ASP for paging records from the
database (SQL Server 2000).
The real problem I have around 10,000 records and it
tries to fetch all the records everytime (I'm saying
because its take a lot time to display it). Even though,
it displays all the data correctly and you can also
navigate through links.
Is it possible to set the limit on recordset while it
fetches the data. Lets say page size is 20 records per
page, so it should fetch only twenty records from the
database (depends on page being displayed) rather fetches
all records and displaying twenty records.
I know its possible if we use mysql database so can you
set the limit (its keyword) in the query statement.
Any help would be appreciated.
Thanx in advance...
Dave

[Aaron Bertrand [MVP]]

Re: ASP paging problem

Some alternative approaches here. http://www.aspfaq.com/2120

If you can wait a few days, I'll probably have a much more comprehensive and
totally re-written article ready by Monday.

--
Aaron Bertrand
SQL Server MVP

http://www.aspfaq.com/

"david" <anonymous@disc ussions.microso ft.com> wrote in message
news:08f201c3d5 9b$0074fd20$a10 1280a@phx.gbl.. .[color=blue]
> Hi
> I have written code in ASP for paging records from the
> database (SQL Server 2000).
> The real problem I have around 10,000 records and it
> tries to fetch all the records everytime (I'm saying
> because its take a lot time to display it). Even though,
> it displays all the data correctly and you can also
> navigate through links.
> Is it possible to set the limit on recordset while it
> fetches the data. Lets say page size is 20 records per
> page, so it should fetch only twenty records from the
> database (depends on page being displayed) rather fetches
> all records and displaying twenty records.
> I know its possible if we use mysql database so can you
> set the limit (its keyword) in the query statement.
> Any help would be appreciated.
> Thanx in advance...
> Dave[/color]

[Harag]

Re: ASP paging problem


OP - Go with the DB Stored Proc way of paging... its MUCH MUCH Quicker
:)

Arron, are you rewriting the stored proc version? I'd be interest in
this if its even better :)

Al.

On Wed, 7 Jan 2004 23:13:10 -0500, "Aaron Bertrand [MVP]"
<aaron@TRASHasp faq.com> wrote:
[color=blue]
>Some alternative approaches here. http://www.aspfaq.com/2120
>
>If you can wait a few days, I'll probably have a much more comprehensive and
>totally re-written article ready by Monday.[/color]

[Aaron Bertrand - MVP]

Re: ASP paging problem

There will be a bunch of new stored procedure approaches. Just need to find
time to breathe first.

--
Aaron Bertrand
SQL Server MVP

http://www.aspfaq.com/

"Harag" <harag@REMOVETH ESECAPITALSsoft home.net> wrote in message
news:8chqvvov5d t9hjlt2p1gsmat9 thm726c0f@4ax.c om...[color=blue]
>
> OP - Go with the DB Stored Proc way of paging... its MUCH MUCH Quicker
> :)
>
> Arron, are you rewriting the stored proc version? I'd be interest in
> this if its even better :)
>
> Al.
>
> On Wed, 7 Jan 2004 23:13:10 -0500, "Aaron Bertrand [MVP]"
> <aaron@TRASHasp faq.com> wrote:
>[color=green]
> >Some alternative approaches here. http://www.aspfaq.com/2120
> >
> >If you can wait a few days, I'll probably have a much more comprehensive[/color][/color]
and[color=blue][color=green]
> >totally re-written article ready by Monday.[/color]
>[/color]

[Brynn]

Re: ASP paging problem


Hey Dave,

I hope you get this although the message is starting to age.

Try out my /coolpier_script/DBConn.asp. The paging function I use that
generally works really fast. It is located at ...

http://www.coolpier.com/cp/cp_scripts/script.asp?view=code&file=DBConn.asp

And it has...

cp_TheConnectio nString

Sub cp_DBConn(cp_Co nnAction) '// "open" or "close"

Sub cp_SqlExecute(c p_TheSqlStateme nt) '// one not returning a
recordset

Function cp_SqlArray(cp_ TheSqlStatement ) '//returns recordset as array

Function cp_DBPaging(cp_ TheSQLStatement , cp_ThePageNumbe r,
cp_RecordsPerPa ge) '// returns that pages recordset as array

The 2 that return arrays, you will want to check if any records found
by ...

Your code would look similar to this...

<%
Dim pageNum, perPage
pageNum = CInt(Request.Qu eryString("page "))
perPage = 20

Dim yourArray, yourSQL, totalPagesOfRec ords
cp_TheConnectio nString = "DSN=yourDS N;"
yourSQL = "Select * From yourTable Where this = 'that';"

'// IN & OUT of database connection fast as heck ;)
cp_DBConn("open ")
yourArray = cp_DBPaging(cp_ TheSQLStatement , pageNum, perPage)
totalPagesOfRec ords = cp_TotalPages
cp_DBConn("clos e")


If Not IsArray(yourArr ay) Then
'// No records found code
Else
'// Records found code
End If
%>

I will have more detailed instruction on my website very soon.


On Wed, 7 Jan 2004 19:53:40 -0800, "david"
<anonymous@disc ussions.microso ft.com> wrote:
[color=blue]
>Hi
>I have written code in ASP for paging records from the
>database (SQL Server 2000).
>The real problem I have around 10,000 records and it
>tries to fetch all the records everytime (I'm saying
>because its take a lot time to display it). Even though,
>it displays all the data correctly and you can also
>navigate through links.
>Is it possible to set the limit on recordset while it
>fetches the data. Lets say page size is 20 records per
>page, so it should fetch only twenty records from the
>database (depends on page being displayed) rather fetches
>all records and displaying twenty records.
>I know its possible if we use mysql database so can you
>set the limit (its keyword) in the query statement.
>Any help would be appreciated.
>Thanx in advance...
>Dave[/color]

I participate in the group to help give examples of code. I do not guarantee the effects of any code posted. Test all code before use!

Brynn

https://www.coolpier.com