Systems, Solutions, Software & Stuff
I recently created an intranet web application, that amongst other things, displayed the company news articles. When I started to create this news application, I soon realised that I would need some sort of paging control to limit the number of articles displayed on a single page, not only to improve performance but also to give the audience a clean display of articles without endless scrolling.
There are many solutions available, all with their own benefits and drawbacks, the one I finally implemented makes use of the ADO RecordSet Object and the PageSize, CacheSize, AbsolutePage, and PageCount properties of ADO. This article illustrates the method I used.
Firstly, you must select a number that will indicate the maximum number of records allowed on a single page of a RecordSet Object, in this example, lets get 10 records per page.
rsCustomer.pagesize = 10 rsCustomer.cachesize = 10
Before constructing the RecordSet Object, lets declare all the variables we’re going to use
<%
dim rsCustomer, sqlCustomer, intCustomerCount, arrCustomerData, intCustomerLoop
dim intPageCount, intPageCurrent, intPageStart, intPageStop, intPageLoop
set adoConn = Server.CreateObject("ADODB.Connection")
adoConn.ConnectionString = "Provider=SQLOLEDB; Data Source=myServer; Initial Catalog=NorthWind; UID=xxx; PWD=xxx"
adoConn.openset rsCustomer = server.createobject("adodb.recordset")
sqlCustomer = "SELECT CustomerID, CompanyName, ContactName, ContactTitle FROM NorthWind ORDER BY CompanyName"
rsCustomer.cursorlocation = 3
rsCustomer.pagesize = 10
rsCustomer.cachesize = 10
rsCustomer.open sqlArchive, adoConn
if not (rsCustomer.bof or rsCustomer.eof) then
 intPageCount = rsCustomer.pagecount
 if intPageCurrent > intPageCount then
  intPageCurrent = intPageCount
 end if
 if intPageCurrent < 1 then
  intPageCurrent = 1
 end if
 rsCustomer.absolutepage = intPageCurrent
 arrCustomerData = rsCustomer.getrows(intBlogArticlePaging)
 intCustomerCount = ubound(arrCustomerData, 2) + 1
else
 intCustomerCount = 0
end if
rsCustomer.close
set rsCustomer = nothing
adoConn.close
set adoConn = nothing
%>
The PageCount property returns the number of pages with data in the rsCustomer RecordSet, determined by the PageSize Property. i.e., If we have 23 records in the database, the PageCount would return 3, for 57 records, PageCount would be 6 etc etc..
The intPageCurrent variable identifies what page is currently displayed, and what page number is required from the ADO RecordSet Object. The following code displays the page navigation to the audience, encompassing the intPageCurrent variable.
<%
'display page controls if we have more than one page
if intPageCount > 1 then'---previous page controls
 if intPageCurrent > 1 then
  response.write( "<a href='default.asp?Page="& (intPageCurrent - 1) &"'>Previous</a>")
 end if
'determine page start and stop variables
 if intPageCurrent > 5 then
  intPageStart = (intPageCurrent - 3)
 else
  intPageStart = 1
 end if
 if (intPageCount > 10) and ((intPageCurrent + 3) < intPageCount) then
  intPageStop = (intPageCurrent + 3)
 else
  intPageStop = intPageCount
 end if
 for intPageLoop = intPageStart to intPageStop
  if intPageLoop = intPageCurrent then
   response.write("<b>["& intPageLoop &"]</b> ")
  else
   response.write("<a href='default.asp?Page="& intPageLoop &"'>"& intPageLoop &"</a>")
  end if
 next
'next page controls
 if intPageCurrent < intPageCount then
  response.write("<a href='default.asp?Page="& (intPageCurrent + 1) &"'>Next</a>")
 end if
end if
%>
All that’s left to do now is capture the intPageCurrent query string parameter set in the paging links above. This code is actually required at the beginning, before the recordset as it sets the page number that’s required from the RecordSet Object.
<%
if request.querystring("Page") <> "" and isnumeric(request.querystring("Page")) = true then
 intPageCurrent = int(request.querystring("Page"))
else
 intPageCurrent = 1
end if
%>Â
The paging control of your ASP page is now complete, it may look a bit confusing, but once you’ve implemented the code, you’ll see how relatively simple it is, yet very effective. Here’s the complete code:
<%
dim rsCustomer, sqlCustomer, intCustomerCount, arrCustomerData, intCustomerLoop
dim intPageCount, intPageCurrent, intPageStart, intPageStop, intPageLoop
if request.querystring("Page") <> "" and isnumeric(request.querystring("Page")) = true then
 intPageCurrent = int(request.querystring("Page"))
else
 intPageCurrent = 1
end if
set adoConn = Server.CreateObject("ADODB.Connection")
adoConn.ConnectionString = "Provider=SQLOLEDB; DataSource=myServer; InitialCatalog=NorthWind; UID=xxx;PWD=xxx"
adoConn.open
set rsCustomer=server.createobject("adodb.recordset")
sqlCustomer = "SELECT CustomerID, CompanyName, ContactName, ContactTitle FROM NorthWind ORDERBY CompanyName"
rsCustomer.cursorlocation = 3
rsCustomer.pagesize = 10
rsCustomer.cachesize = 10
rsCustomer.open sqlArchive, adoConn
if not(rsCustomer.bof or rsCustomer.eof) then
 intPageCount = rsCustomer.pagecount
  if intPageCurrent > intPageCount then
   intPageCurrent = intPageCount
  end if
 if int PageCurrent < 1 then
  intPageCurrent = 1
 end if
 rsCustomer.absolutepage = intPageCurrent
 arrCustomerData = rsCustomer.getrows(intBlogArticlePaging)
 intCustomerCount = ubound(arrCustomerData, 2) + 1
else
 intCustomerCount = 0
end if
rsCustomer.close
set rsCustomer = nothing
if intCustomerCount <> 0 then
 intCustomerLoop = 0
 do until intCustomerLoop = intCustomerCount
  response.write("<p>" & (arrArchiveData(0, intArchiveLoop) & "</p>")
  response.write("<p>" & (arrArchiveData(1, intArchiveLoop) & "</p>")
  response.write("<p>" & (arrArchiveData(2, intArchiveLoop) & "</p>")
  response.write("<p>" & (arrArchiveData(3, intArchiveLoop) & "</p>")
  intCustomerLoop = intCustomerLoop + 1
 loop
end if
if intPageCount > 1 then
 if intPageCurrent > 1 then
  response.write("<a href='default.asp?Page="& (intPageCurrent - 1) &"'>Previous</a>")
 end if
 if intPageCurrent > 5 then
  intPageStart = (intPageCurrent - 3)
 else
  intPageStart = 1
 end if
 if (intPageCount > 10) and ((intPageCurrent + 3) < intPageCount) then
  intPageStop = (intPageCurrent + 3)
 else
  intPageStop = intPageCount
 end if
 for intPageLoop = intPageStart to intPageStop
  if intPageLoop = intPageCurrent then
   response.write("<b>["& intPageLoop &"]</b>")
  else
   response.write("<a href='default.asp?Page="& intPageLoop &"'>"& intPageLoop &"</a>")
  end if
 next
 if intPageCurrent < intPageCount then
  response.write("<a href='default.asp?Page="& (intPageCurrent + 1) &"'>Next</a>")
 end if
end if
adoConn.close
set adoConn = nothing
%>