Help with Paging Recordsets????!!!!

Results 1 to 3 of 3

Thread: Help with Paging Recordsets????!!!!

  1. #1
    Join Date
    Dec 1969
    Posts
    1,912

    Default Help with Paging Recordsets????!!!!

    Hi!<BR><BR>I was wondering if anyone knew of a good way to page recordsets. At the moment, I am creating a recordset based on a form query. I&#039m setting a page size and using absolutepage to display the page. If the query creates a recordset of, say, 15 records and the page size is five, I submit all of the variables for the query to the asp page one more and the query is performed on the database again, this time choosing the next page number in the set.<BR><BR>I realize that this isn&#039t the best way of doing things. Can anyone suggest anything better?<BR><BR>Thanks!<BR>Dave

  2. #2
    Join Date
    Dec 1969
    Posts
    308

    Default RE: Help with Paging Recordsets????!!!!

    Here is some old code i had laying around. You may have to make a few adjustments but it should get you started.<BR><BR>Good luck!<BR><BR><BR><BR>&#060;!--#include file="../../includes/adovbs.inc"--><BR>&#060;%<BR> <BR> &#039Set how many records per page we want<BR> Const NumPerPage = 15<BR><BR> &#039Retrieve what page we&#039re currently on<BR> Dim CurPage<BR> If Request.QueryString("CurPage") = "" then<BR> CurPage = 1 &#039We&#039re on the first page<BR> Else<BR> CurPage = Request.QueryString("CurPage")<BR> End If<BR><BR> Dim conn<BR> Set conn = Server.CreateObject("ADODB.Connection")<BR> conn.Open Application("dsn")<BR><BR> &#039Explicitly Create a recordset object<BR> Dim rs<BR> Set rs = Server.CreateObject("ADODB.Recordset")<BR><BR> &#039Set the cursor location property<BR> rs.CursorLocation = adUseClient<BR><BR> &#039Set the cache size = to the # of records/page<BR> rs.CacheSize = NumPerPage<BR><BR> &#039Open our recordset<BR> Dim sql<BR> sql ="Select * from client_manager"<BR> rs.Open SQL, Conn<BR> <BR> if rs.eof then<BR> response.write "&#060;p>&nbsp;&#060;/p>&#060;div align=center>&#060;font face=Arial size=2 color=red>No records in the database&#060;/font>&#060;/div>"<BR> else<BR><BR> rs.MoveFirst<BR> rs.PageSize = NumPerPage<BR><BR> &#039Get the max number of pages<BR> Dim TotalPages<BR> TotalPages = rs.PageCount<BR><BR> &#039Set the absolute page<BR> rs.AbsolutePage = CurPage<BR><BR> &#039Counting variable for our recordset<BR> Dim count<BR>&#037;&#062;<BR><BR>&#060;HTML><BR>&#060; head><BR>&#060;title>What ever&#060;/title><BR>&#060;/head><BR>&#060;BODY><BR>&#060;% &#039Print out the current page # / total pages<BR> Response.Write("&#060;font face=arial size=2>Page " & CurPage & " of " & TotalPages & "&#060;P>") &#037;&#062;&#060;/font>&#060;/div><BR>&#060;table cellspacing=0 cellpadding=0 border=0 width=502 align="center"><BR>&#060;tr>&#060;td bgcolor="black"><BR>&#060;table cellspacing=1 cellpadding=3 border=0 width=500 align="center"><BR> &#060;tr bgcolor="Black"><BR> &#060;td>&#060;font face="Arial" size="2" color="white">Company Name&#060;/font>&#060;/td><BR> &#060;td>&#060;font face="Arial" size="2" color="white">ClientID&#060;/font>&#060;/td><BR> &#060;td align="center">&#060;font face="Arial" size="2" color="white">Menu&#060;/font>&#060;/td> <BR> &#060;/tr><BR>&#060;%<BR> &#039Set Count equal to zero<BR> Count = 0<BR> Do While Not rs.EOF And Count &#060; rs.PageSize<BR> response.write("&#060;tr bgcolor=White>")<BR> Response.Write("&#060;td>&#060;font face=Arial size=2>" & rs("company") & "&#060;/font>&#060;/td>")<BR> Response.write("&#060;td>&#060;font face=Arial size=2>" & rs("clientID") & "&#060;/font>&#060;/td>")<BR> response.write("&#060;td align=center>&#060;font face=Arial size=2>&#060;a href=showmore.asp?ID=" & rs("clientID") & ">Show More&#060;/a>&#060;/font>&#060;/td>")<BR> response.write("&#060;/tr>")<BR> Count = Count + 1<BR> rs.MoveNext<BR> Loop<BR>&#037;&#062;<BR><BR> &#060;/table><BR>&#060;/td>&#060;/tr>&#060;/table><BR>&#060;div align="center"><BR>&#060;form><BR><BR>&#060;% <BR><BR> &#039Display Next / Prev buttons<BR> if CurPage > 1 then<BR> &#039We are not at the beginning, show the prev button<BR> Response.Write("&#060;INPUT TYPE=BUTTON VALUE=PREV ONCLICK=""document.location.href=&#039show.asp?cur page=" & curpage - 1 & "&#039;"">")<BR> End If<BR><BR> if CInt(CurPage) &#060;> CInt(TotalPages) then<BR> &#039We are not at the end, show a next button<BR> Response.Write("&#060;INPUT TYPE=BUTTON VALUE=NEXT ONCLICK=""document.location.href=&#039show.asp?cur page=" & curpage + 1 & "&#039;"">")<BR> End If &#037;&#062;<BR><BR>&#060;/div><BR>&#060;/form><BR><BR>&#060;/BODY><BR>&#060;/HTML><BR>&#060;% End If &#037;&#062;<BR>

  3. #3
    Join Date
    Dec 1969
    Posts
    1,912

    Default RE: Help with Paging Recordsets????!!!!

    Thank you for your help, Brad. I&#039ve got the paging going, but I don&#039t think I was very clear on the problem. It seems from your code that for each page, even though the cache is storing 15 records from the recordset, you still have to perform a query each time the user clicks to go to the next page. Is this correct?<BR><BR>If so, do you know of a way of paging without querying each time?<BR><BR>Thanks!<BR>Dave

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •