ASPNL - The premier Dutch resource on ASP

Thursday, November 20, 2008

<< previous | index

Data on multiple pages

It's very common to get a lot of records back from a query. In that case it's often a good solution to spread the data over more pages, so that the user can loop through the data. With the GetRows method we can state how many records we want to have. A recordset (type Static or Keyset) has the possibility to start with a certain record. We do this by changing the AbsolutePosition into the position where we want to start. The sample below uses this method to offer data in more pages.

pagedgetrows.asp
<%
Option Explicit

'Declare constant(s)
Const RecordsPerPage = 10

'Declare variable(s)
Dim objConn
Dim objRs
Dim arrRs
Dim strSQL
Dim strPage
Dim lngFields
Dim lngRecords
Dim lngNumberOfRecords
Dim lngNumberOfPages
Dim lngPage
Dim lngStart
Dim i, j

'SQL statement
strSQL = "SELECT CompanyName, Address, PostalCode, City, Country FROM Customers"

'Retrieve present page from QueryString and calculate startingpoint
strPagina = Request.QueryString("pag")
If IsNumeric(strPage) And strPage <> "" Then
   lngPagina = CLng(strPagina)
Else
   lngPage = 1
End If
lngStart = RecordsPerPage * (lngPage - 1) + 1

'Open database
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open Application("ConnString")

'Open recordset
Set objRs = Server.CreateObject("ADODB.Recordset")
objRs.Open strSQL, objConn, adOpenStatic, adLockReadOnly

'Request total number of records
IngNumberOfRecords = objRs.RecordCount

If IngNumberOfRecords >= lngStart Then
   'Set starting position
   objRs.AbsolutePosition = lngStart

   'Put records in array and count the fields and records
   arrRs = objRs.GetRows(RecordsPerPagina, adBookmarkCurrent)
   lngFields = UBound(arrRs)
   lngRecords = UBound(arrRs, 2)
ElseIf lngNumberOfRecords > 0 Then
   lngRecords = -2
Else
   lngRecords = -1
End If

'Close Recordset
objRs.Close
Set objRs = Nothing

'Close database
objConn.Close
Set objConn = Nothing
%>
<HTML>
<BODY>
<TABLE BORDER="1">
<%
'Calculate number of pages
lngAantalPaginas = lngNumberofRecords \ RecordsPerPage
If lngNumberOfRecords Mod RecordsPerPage > 0 Then
   lngNumberOfPages = lngNumberOfPages + 1
End If

Select Case lngRecords
   Case -1
      Response.Write "There are no record to be displayed"
   Case -2
      Response.Write "The requested page does not excist"
   Case Else
      'Display reference to previous page
      If lngPage > 1 Then
         Response.Write "<a href=""pagedgetrows.asp?pag=" & lngPage - 1 & """>"
         Response.Write "<< back</a>  "
      End If

      'Display Number of pages
      Response.Write "page " & lngPage & " of " & lngNumberOfPages

      'Display reference to next page
      If lngPage < lngNumberOfPages Then
         Response.Write "  <a href=""pagedgetrows.asp?pag=" & lngPage + 1 & """>"
         Response.Write "next >></a>"
      End If

      'Show table with values
      For i = 0 To lngRecords
         Response.Write "<TR>"
         For j = 0 To lngFields
            Response.Write "<TD>" & arrRs(j, i) & "</TD>"
         Next
         Response.Write "</TR>"
      Next
End Select
%>
</TABLE>
</BODY>
</HTML>

<< previous | ^ to top | index