Only showing X records at a time 1

[PatHastings]

Hi,<br>I need to be able to retrieve records from database in an ASP page - easy, no problem, can do that.<br><br>Then I need to be able to limit the number of records displayed on the page. Can do that too.<br><br>&nbsp;Then i need to put a button in to display the next X records. Bit unclear on how this should be done.<br>I could call the same asp again passing the id of the last record displayed and use this ID as the starting point of the sql search for the new page.<br><br>The problem with this though is that it assumes all the id's are sequential. They normaly would be except where you are doing a keyword search. Is there any other way of doing it? Ideally is it possible to use some sort of recordset persitance so that the first page builds the record set and the rest of the pages work from it?<br><br>Any help or pointers to articles about this subject would be greatly appreciated<br><br>Pat

 

[DMG2000]

Have you tried putting the query for getting the records in it's own .inc file, and including the code in the asp? This works well on the e-commerence solution I am working as far as getting product info time and time again, instead, it calls in the include and I refer to the objects accordingly.<br><br>Dunno if that helps any.

 

[PatHastings]

putting the query in an include file would certainly make the code more readable and i should do that.<br><br>what i am looking for though is more of a technical soloution for:<br><br>Taking tek tips as an example - if all the posts were displayed on the same page then the page would never stop loading. <br>Solution each page displays between 10 and 20 threads per page depending on the user choice.<br><br>Assuming that each thread is assigned a sequentialID, and you wish the threads to be displayed in date order you could<br>use the following sql:<br>select top 20 * from table<br>the asp could then display the records and generate the url for the 'Next' button as self.asp?lastRecord=x<br><br>this time and all subsequent times then the SQL would look like :<br><br>Select top 20 * from table where id &gt; x <br><br>This solution works well but is very dependant on the records being stored in the order that you wish to view them. <br><br>I was wondering if there was any other way of doing it like keeping the original recordset alive somehow and just traversing through that.<br>&nbsp;

 

[palbano]

Dear Pat,<br><br>You want to look at the ADO.Recordset reference. Specifically look at these properties<br><br>PageSize<br>PageCount<br>AbsolutePage<br><br>In a short description set the 'PageSize' to say 20 then write a hidden form variable into the page storing the 'AbsolutePage' value into the form<br><br>&lt;Form name=&quot;form1&quot; method=&quot;post&quot;&gt;<br>&lt;input type=&quot;hidden&quot; name=&quot;absPage&quot; value=&quot;&lt;%=rs.AbsolutePage%&gt;&quot;&gt;<br>...<br><br>&lt;/form&gt;<br><br>Then have your 'Next/Previous' buttons or links set another hidden form variable value using client side script to indicate the direction and then submit the form.<br><br>The Server code will use the forms request variables to determine how to set the rs.AbsolutePage property to position the cursor prior to writing the data to the Response object.<br><br>Hope this helps<br>-pete

 

[jfriestman]

This question gets asked so many times here, I decided to put together a VBScript Class that will allow people to page a recordset. Here's the code and a page that will do it, just copy paste it into your favorite editor and start experimenting. You can definitely make the table prettier and should probably add some error handling. Anyway, happy coding!

[tt]
<%@ Language=VBScript %>
<% Option Explicit %>

<%
'include a reference to the ADO type library
'so we can use the ADO constants
%>
<!-- METADATA
TYPE=&quot;typelib&quot;
FILE=&quot;C:\Program Files\Common Files\System\ADO\msado20.tlb&quot;
-->

<%
Response.Buffer = True
Dim iCurrentPage
iCurrentPage = Request.QueryString (&quot;pg&quot
If iCurrentPage = &quot;&quot; Then iCurrentPage = 1

Dim oPg
Set oPg = New DbPager

'Set public properties of the Pager Class

With oPg
[tab][tab].DataSource = &quot;Provider=SQLOLEDB; Data_Source=(local); Initial Catalog=Northwind; User Id=sa; Password=;&quot;
[tab][tab].RecsPerPage = 10
[tab][tab].SQL = &quot;SELECT * FROM Customers ORDER BY CompanyName&quot;
[tab][tab].ShowPageNumbers = True
[tab][tab].ShowPage (iCurrentPage)
End With


%>


<%
Class DbPager

[tab][tab]'Set a few module level variables
[tab][tab]Private m_sDSN
[tab][tab]Private m_sSQL
[tab][tab]Private m_iRecsPerPage
[tab][tab]Private m_bShowPageNumbers

[tab][tab]'datasource for the Data Page
[tab][tab]Public Property Let DataSource(NewValue)[tab]'As String
[tab][tab][tab]m_sDSN = NewValue
[tab][tab]End Property

[tab][tab]'sql statement to generate the page
[tab][tab]Public Property Let SQL(NewValue)[tab]'As String
[tab][tab][tab]m_sSQL = NewValue
[tab][tab]End Property

[tab][tab]'number of records you want per page
[tab][tab]Public Property Let RecsPerPage (NewValue)[tab]'As Integer
[tab][tab][tab]m_iRecsPerPage = NewValue
[tab][tab]End Property

[tab][tab]'Boolean to indicate whether or not to show all the page numbers
[tab][tab]Public Property Let ShowPageNumbers(NewValue)[tab]'As Boolean
[tab][tab][tab]m_bShowPageNumbers = CBool(NewValue)
[tab][tab]End Property


[tab][tab]'set default values
[tab][tab]Private Sub Class_Initialize()
[tab][tab][tab]ShowPageNumbers = True
[tab][tab][tab]m_iRecsPerPage = 10
[tab][tab]End Sub


[tab][tab]'INPUT: Page you want to show
[tab][tab]'OUTPUT: Html
[tab][tab]Public Sub ShowPage (iCurrentPage)


[tab][tab][tab]Dim Rs
[tab][tab][tab]Set Rs = Server.CreateObject (&quot;ADODB.Recordset&quot

[tab][tab][tab]With Rs
[tab][tab][tab][tab].CacheSize = m_iRecsPerPage
[tab][tab][tab][tab].PageSize = m_iRecsPerPage[tab]'# of records / page
[tab][tab][tab][tab].CursorLocation = adUseClient
[tab][tab][tab][tab]'Absolute Page requires Keyset or Static cursor
[tab][tab][tab][tab].CursorType = adOpenKeyset
[tab][tab][tab][tab].Open m_sSQL, m_sDsn
[tab][tab][tab][tab].AbsolutePage = iCurrentPage[tab]'Set the page
[tab][tab][tab]End With

[tab][tab][tab]'Number of fields, number of pages
[tab][tab][tab]Dim iNumFields, iNumPages
[tab][tab][tab]iNumFields = Rs.Fields.Count
[tab][tab][tab]iNumPages = Rs.PageCount

[tab][tab][tab]Dim i, iFld, iRec

[tab][tab][tab]Response.Write &quot;<TABLE Border=1 Width='100%'>&quot;

[tab][tab][tab]'a routine to write the page navigation
[tab][tab][tab]WriteNavigation iCurrentPage, iNumPages, iNumFields


[tab][tab][tab]'Write all the field names
[tab][tab][tab]'as column headings
[tab][tab][tab]Response.Write &quot;<TR>&quot;
[tab][tab][tab]For iFld = 0 To iNumFields - 1
[tab][tab][tab][tab]Response.Write &quot;<TD>&quot; & Rs.Fields(iFld).Name & &quot;</TD>&quot;
[tab][tab][tab]Next
[tab][tab][tab]Response.Write &quot;<TR>&quot;


[tab][tab][tab]'Write the cell values
[tab][tab][tab]For i = 1 To m_iRecsPerPage
[tab][tab][tab][tab]If Rs.EOF Then Exit For
[tab][tab][tab][tab]Response.Write &quot;<TR>&quot;
[tab][tab][tab][tab]For iFld = 0 To iNumFields - 1
[tab][tab][tab][tab][tab]Response.Write &quot;<TD>&quot; & Rs.Fields(iFld).Value & &quot;</TD>&quot;
[tab][tab][tab][tab]Next
[tab][tab][tab][tab]Response.Write &quot;</TR>&quot;
[tab][tab][tab][tab]Rs.MoveNext
[tab][tab][tab]Next

[tab][tab][tab]Response.Write &quot;</TABLE>&quot;

[tab][tab]End Sub



[tab][tab]'INPUT: Current Page, Number of total pages, number of fields
[tab][tab]'OUTPUT: Html for navigating the recordset
[tab][tab]Private Sub WriteNavigation(iCurrentPage, iNumPages, iNumFields)

[tab][tab][tab]Dim i, sScriptName

[tab][tab][tab]sScriptName = Request.ServerVariables (&quot;SCRIPT_NAME&quot

[tab][tab][tab]iCurrentPage = CInt(iCurrentPage)

[tab][tab][tab]Dim iPreviousPage, iNextPage
[tab][tab][tab]'at the first page so set the previous page to the last page
[tab][tab][tab]If iCurrentPage = 1 Then
[tab][tab][tab][tab]iPreviousPage = iNumPages
[tab][tab][tab]Else
[tab][tab][tab][tab]iPreviousPage = iCurrentPage - 1
[tab][tab][tab]End If

[tab][tab][tab]'at the last page so set the next page to the first page
[tab][tab][tab]If iCurrentPage = iNumPages Then
[tab][tab][tab][tab]iNextPage = 1
[tab][tab][tab]Else
[tab][tab][tab][tab]iNextPage = iCurrentPage + 1
[tab][tab][tab]End If

[tab][tab][tab]Response.Write &quot;<TR>&quot;
[tab][tab][tab]Response.Write &quot;<TD Colspan='&quot; & iNumFields & &quot;' Align=Center>&quot;

[tab][tab][tab]'previous page
[tab][tab][tab]Response.Write &quot;<A HREF='&quot; & sScriptName & &quot;?pg=&quot; & iPreviousPage & &quot;'>Prev</A> &quot;

[tab][tab][tab]'if show all page numbers (clickable)
[tab][tab][tab]'this can get out of hand with big recordsets
[tab][tab][tab]If m_bShowPageNumbers Then
[tab][tab][tab][tab]For i = 1 To iNumPages
[tab][tab][tab][tab][tab]If i = iCurrentPage Then
[tab][tab][tab][tab][tab][tab]Response.Write &quot;<B>&quot; & i & &quot;</B>&quot;
[tab][tab][tab][tab][tab]Else
[tab][tab][tab][tab][tab][tab]Response.Write &quot;<A HREF='&quot; & Request.Servervariables(&quot;SCRIPT_NAME&quot & &quot;?pg=&quot; & i & &quot;'>&quot;
[tab][tab][tab][tab][tab][tab]Response.Write i & &quot;</A>&quot;
[tab][tab][tab][tab][tab]End If
[tab][tab][tab][tab][tab]Response.Write &quot; &quot;
[tab][tab][tab][tab]Next
[tab][tab][tab]End If

[tab][tab][tab]'next page navigation
[tab][tab][tab]Response.Write &quot; <A HREF='&quot; & sScriptName & &quot;?pg=&quot; & iNextPage & &quot;'>Next</A>&quot;
[tab][tab][tab]Response.Write &quot;</TD>&quot;
[tab][tab][tab]Response.Write &quot;</TR>&quot;
[tab][tab]End Sub


End Class
%>
[/tt]

 

[jfriestman]

Ooops, forgot to clean up after myself. At the bottom of the ShowPage routine, add the lines of code:

[tt]
Rs.Close
Set Rs = Nothing
[/tt]

I turned this into an FAQ, you can see the better code in the FAQ section.

 

[PatHastings]

Thanks Jeff, thanks Pete.

The code works like a dream. I now need to go through it and make sure i understand it all.

cheers for the help

Pat