paging through recordset

[Tom B]

[url]http://www.google.ca/search?q=asp+recordset+paging+tutorial&ie=UTF-8&oe=UTF-8&hl=en&meta=[/url]

[url]http://www.asp101.com/articles/recordsetpaging/index.asp[/url]

[url]http://www.aspfaq.com/show.asp?id=2120[/url]
[url]http://www.aspfaq.com/show.asp?id=2193[/url]

"Jeff" <fisher.jeff@navicamls.net> wrote in message
news:ewnZWhIUDHA.2252@TK2MSFTNGP12.phx.gbl...

> Anyone know of a good thread, or tutorial on a an EEFICIENT way of paging
> through a recordset using asp and sqlserver.
>
> My tables are about 20,000each
>
> Things needed.
> 1,_Previous Record Link
> 2.-List of the number of pages of recordsets returned (in a link

format)--to

> where if the user wanted to click on the page 10 link, that the page would
> take them to the tenth page.
> 3. List of how many pages and records the query string returned
> 4.- Next Record Link
>
> Thanks in advance
>
>

[Jeff]

Anyone know of a good thread, or tutorial on a an EEFICIENT way of paging
through a recordset using asp and sqlserver.

My tables are about 20,000each

Things needed.
1,_Previous Record Link
2.-List of the number of pages of recordsets returned (in a link format)--to
where if the user wanted to click on the page 10 link, that the page would
take them to the tenth page.
3. List of how many pages and records the query string returned
4.- Next Record Link

Thanks in advance

[Loic]

Hi,

I hope someone can help. Any good developer will know how to correct
this but I am just a beginner.

The code below is for paging through my recordset. In the sql query,
the parameters in the 'where' clause are passed from my index page.
The code shows the first page of my recordset alright, but when I hit
the 2nd page or Next link, then it seems to return all records from my
recordset. It looks like the original parameters defined in my query
string are not passed any more.

Any help welcome. Thanks in advance.

Loic

<%
'Get the current page the user is on, if its the first time they
'visit and the pageNo is empty, then variable 'CurrPage' gets set to 1
'Else the current page variable 'CurrPage' is set to the one requested
by the user
If isEmpty(request.querystring("PageNo")) then
CurrPage = 1
else
CurrPage = Cint(request.querystring("PageNo"))
end if

'the two functions below get the next 10 and prev 10 page number
function getNext10(num)
pageLen = len(num)
if pageLen = 1 then
next10 = 10
else if pageLen = 2 then
pageRem = 10
pageTen = right(num, 1)
next10 = num + pageRem - pageTen
end if
end if
getNext10 = next10
end function

function getPrev10(num)
pageLen = len(num)
if pageLen = 1 then
prev10 = 1
else if pageLen = 2 then
firstDig = left(num, 1)
secondDig = right(num, 1)
prev10 = num - secondDig - 10
end if
end if
if prev10 = 0 then
prev10 = 1
end if
getPrev10 = prev10
end function

dim objconn, objRS, strQuery
dim strConnection

dbPath = Server.MapPath("./actrim.mdb")
Set objConn = Server.CreateObject("ADODB.Connection")

strConnection = "DRIVER={Microsoft Access Driver (*.mdb)};" & " DBQ="
& dbPath
objConn.Open "DRIVER={Microsoft Access Driver (*.mdb)};" & " DBQ=" &
dbPath

set objRS= Server.CreateObject("ADODB.Recordset")

strQuery="select * from proprietes pr inner join Agences ag on
ag.id=pr.contact "
strQuery= strQuery & " where type like '%" & Request.Form("slt_Type")
& "%'"
strQuery= strQuery & " and Roomtag like '%" &
Request.Form("slt_Pieces") & "%'"
strQuery= strQuery & " and Departement like '%" &
Request.Form("slt_Dept") & "%'"
strQuery= strQuery & " and isnull(DateSold) order by Prix"


'Next set the location of the recordset to the client side
objRS.CursorLocation = 3

'Execute the SQL and return our recordset
objRS.open strQuery, strConnection

' the pagesize method is used to set the number of records that will
be
' displayed on each page. For our purposes 10 records is what we want.
objRS.PageSize = 4

'this moves the record pointer to the first record of the current page
objRS.AbsolutePage = CurrPage
%>

<%
next10 = getNext10(CurrPage)
prev10 = getPrev10(CurrPage)

'the below loop will loop until all the records of the current page
have been
'displayed or it has reached the end of the recordset
Do Until objRS.AbsolutePage <> CurrPage OR objRS.Eof

response.write "Ref: " & objRS ("Reference") & "<br>"
response.write "Description: " & objRS ("DescriptionCourteFR") &
"<br>"
response.write "Prix: " & objRS ("Prix") & "<br><br>"
objRS.MoveNext
Loop

RSPrevPage = CurrPage -1
RSNextPage = CurrPage + 1


if Next10 > objRS.PageCount then
next10 = objRS.PageCount
end if

if prev10 = 1 AND next10 - 1 < 10 then
start = 1
else
start = Next10 - 10
if right(start, 1) > 0 then
start = replace(start, right(start, 1), "0")
start = start + 10
end if
end if

'This checks to make sure that there is more than one page of results
If objRS.PageCount > 1 then
'this checks to make sure that current page isn't the only one, and if
not,
'it prints the previous link
if next10 > 10 then
response.write("<a href=""FR_Results.asp?PageNo=" & Prev10 &
"""><<</a> ")
end if
if not RSPrevPage = 0 then
response.write("<a href=""FR_Results.asp?PageNo=" & RSPrevPage &
"""><</a> ")
end if

for P = start to Next10

if not P = CurrPage then
response.write("<a href=""FR_Results.asp?PageNo=" & P & """>" & P &
"</a> ")
else
response.write(" <b>" & P & " </b>")
end if
Next
'this does the same as the "previous" link, but for the "next" link
if not RSNextPage > objRS.PageCount then
response.write("<a href=""FR_Results.asp?PageNo=" & RSNextPage &
""">></a> ")
end if

if not Next10 = objRS.PageCount then
response.write(" <a href=""FR_Results.asp?PageNo=" & Next10 &
""">>></a>")
end if
end if

' Close the recordset and connection object
objRS.Close
Set objRS = Nothing
objConn.Close
Set objRS =Nothing
%>

[Aaron Bertrand [MVP]]

Some cleaner options, IMHO, available at [url]http://www.aspfaq.com/2120[/url]

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]


"Loic" <loic_nospam@yahoo.com> wrote in message
news:d4a964bf.0405231316.6c1cb6de@posting.google.c om...

> Hi,
>
> I hope someone can help. Any good developer will know how to correct
> this but I am just a beginner.
>
> The code below is for paging through my recordset. In the sql query,
> the parameters in the 'where' clause are passed from my index page.
> The code shows the first page of my recordset alright, but when I hit
> the 2nd page or Next link, then it seems to return all records from my
> recordset. It looks like the original parameters defined in my query
> string are not passed any more.
>
> Any help welcome. Thanks in advance.
>
> Loic
>
> <%
> 'Get the current page the user is on, if its the first time they
> 'visit and the pageNo is empty, then variable 'CurrPage' gets set to 1
> 'Else the current page variable 'CurrPage' is set to the one requested
> by the user
> If isEmpty(request.querystring("PageNo")) then
> CurrPage = 1
> else
> CurrPage = Cint(request.querystring("PageNo"))
> end if
>
> 'the two functions below get the next 10 and prev 10 page number
> function getNext10(num)
> pageLen = len(num)
> if pageLen = 1 then
> next10 = 10
> else if pageLen = 2 then
> pageRem = 10
> pageTen = right(num, 1)
> next10 = num + pageRem - pageTen
> end if
> end if
> getNext10 = next10
> end function
>
> function getPrev10(num)
> pageLen = len(num)
> if pageLen = 1 then
> prev10 = 1
> else if pageLen = 2 then
> firstDig = left(num, 1)
> secondDig = right(num, 1)
> prev10 = num - secondDig - 10
> end if
> end if
> if prev10 = 0 then
> prev10 = 1
> end if
> getPrev10 = prev10
> end function
>
> dim objconn, objRS, strQuery
> dim strConnection
>
> dbPath = Server.MapPath("./actrim.mdb")
> Set objConn = Server.CreateObject("ADODB.Connection")
>
> strConnection = "DRIVER={Microsoft Access Driver (*.mdb)};" & " DBQ="
> & dbPath
> objConn.Open "DRIVER={Microsoft Access Driver (*.mdb)};" & " DBQ=" &
> dbPath
>
> set objRS= Server.CreateObject("ADODB.Recordset")
>
> strQuery="select * from proprietes pr inner join Agences ag on
> ag.id=pr.contact "
> strQuery= strQuery & " where type like '%" & Request.Form("slt_Type")
> & "%'"
> strQuery= strQuery & " and Roomtag like '%" &
> Request.Form("slt_Pieces") & "%'"
> strQuery= strQuery & " and Departement like '%" &
> Request.Form("slt_Dept") & "%'"
> strQuery= strQuery & " and isnull(DateSold) order by Prix"
>
>
> 'Next set the location of the recordset to the client side
> objRS.CursorLocation = 3
>
> 'Execute the SQL and return our recordset
> objRS.open strQuery, strConnection
>
> ' the pagesize method is used to set the number of records that will
> be
> ' displayed on each page. For our purposes 10 records is what we want.
> objRS.PageSize = 4
>
> 'this moves the record pointer to the first record of the current page
> objRS.AbsolutePage = CurrPage
> %>
>
> <%
> next10 = getNext10(CurrPage)
> prev10 = getPrev10(CurrPage)
>
> 'the below loop will loop until all the records of the current page
> have been
> 'displayed or it has reached the end of the recordset
> Do Until objRS.AbsolutePage <> CurrPage OR objRS.Eof
>
> response.write "Ref: " & objRS ("Reference") & "<br>"
> response.write "Description: " & objRS ("DescriptionCourteFR") &
> "<br>"
> response.write "Prix: " & objRS ("Prix") & "<br><br>"
> objRS.MoveNext
> Loop
>
> RSPrevPage = CurrPage -1
> RSNextPage = CurrPage + 1
>
>
> if Next10 > objRS.PageCount then
> next10 = objRS.PageCount
> end if
>
> if prev10 = 1 AND next10 - 1 < 10 then
> start = 1
> else
> start = Next10 - 10
> if right(start, 1) > 0 then
> start = replace(start, right(start, 1), "0")
> start = start + 10
> end if
> end if
>
> 'This checks to make sure that there is more than one page of results
> If objRS.PageCount > 1 then
> 'this checks to make sure that current page isn't the only one, and if
> not,
> 'it prints the previous link
> if next10 > 10 then
> response.write("<a href=""FR_Results.asp?PageNo=" & Prev10 &
> """><<</a> ")
> end if
> if not RSPrevPage = 0 then
> response.write("<a href=""FR_Results.asp?PageNo=" & RSPrevPage &
> """><</a> ")
> end if
>
> for P = start to Next10
>
> if not P = CurrPage then
> response.write("<a href=""FR_Results.asp?PageNo=" & P & """>" & P &
> "</a> ")
> else
> response.write(" <b>" & P & " </b>")
> end if
> Next
> 'this does the same as the "previous" link, but for the "next" link
> if not RSNextPage > objRS.PageCount then
> response.write("<a href=""FR_Results.asp?PageNo=" & RSNextPage &
> """>></a> ")
> end if
>
> if not Next10 = objRS.PageCount then
> response.write(" <a href=""FR_Results.asp?PageNo=" & Next10 &
> """>>></a>")
> end if
> end if
>
> ' Close the recordset and connection object
> objRS.Close
> Set objRS = Nothing
> objConn.Close
> Set objRS =Nothing
> %>

[Loic]

Ok, thanks for that. It does look neater.
But I still have a problem. How do I get my form to reload the field
values ("txtPriceFrom" and txtPriceTo") from the search form?

Thanks is advance

Loic

Code below:

<!--#include file=inc.asp-->
<!--#include file=topRS.asp-->
<%

dataSQL = "SELECT reference, DescriptionCourteFR, Prix from
proprietes "
dataSQL= dataSQL & " where Prix between " &
request.Form("txtPriceFrom") & " and " & request.Form("txtPriceFrom")

set objRS = objConn.execute(dataSQL)

if not objRS.eof then

Dim gr
rstop = PerPage * PageNum
rstart = rstop - (PerPage - 1)
objRS.move(rstart-1)

if rstop > RowCnt - 1 then PerPage = (RowCnt - 1) - rstart
gr = objRS.GetRows(PerPage)

response.write "<table border=0 cellpadding=5>"

for i = 0 to perpage-1

artist = gr(0, i)
title = gr(1, i)

if artist <> prevArtist then
prevArtist = artist
response.write "<tr><td class=n>"
response.write artist & "</td>"
response.write "<td class=n>"
else
response.write "<tr><td> </td><td>"
end if

response.write title & "</td></tr>"

next

response.write "</table>"
else
response.write "No rows found."
response.end
end if

%>
<!--#include file=foot.asp-->

[Aaron Bertrand - MVP]

You need to put the <input type=hidden> fields back in, so they will be
carried from page to page.

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]




"Loic" <loic_nospam@yahoo.com> wrote in message
news:d4a964bf.0405240912.36f22243@posting.google.c om...

> Ok, thanks for that. It does look neater.
> But I still have a problem. How do I get my form to reload the field
> values ("txtPriceFrom" and txtPriceTo") from the search form?
>
> Thanks is advance
>
> Loic
>
> Code below:
>
> <!--#include file=inc.asp-->
> <!--#include file=topRS.asp-->
> <%
>
> dataSQL = "SELECT reference, DescriptionCourteFR, Prix from
> proprietes "
> dataSQL= dataSQL & " where Prix between " &
> request.Form("txtPriceFrom") & " and " & request.Form("txtPriceFrom")
>
> set objRS = objConn.execute(dataSQL)
>
> if not objRS.eof then
>
> Dim gr
> rstop = PerPage * PageNum
> rstart = rstop - (PerPage - 1)
> objRS.move(rstart-1)
>
> if rstop > RowCnt - 1 then PerPage = (RowCnt - 1) - rstart
> gr = objRS.GetRows(PerPage)
>
> response.write "<table border=0 cellpadding=5>"
>
> for i = 0 to perpage-1
>
> artist = gr(0, i)
> title = gr(1, i)
>
> if artist <> prevArtist then
> prevArtist = artist
> response.write "<tr><td class=n>"
> response.write artist & "</td>"
> response.write "<td class=n>"
> else
> response.write "<tr><td> </td><td>"
> end if
>
> response.write title & "</td></tr>"
>
> next
>
> response.write "</table>"
> else
> response.write "No rows found."
> response.end
> end if
>
> %>
> <!--#include file=foot.asp-->

[Loic]

Thanks for your help.