ASP Page Control
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
%>
No comments