There may be times when you need to display a large number of records on your web page. Generally, the best way to do this is to limit the number of records displayed on your page and let the user scroll through the records by page. For example, let’s say you had a customer database you wanted to display on your web page.
The first thing you need to do is create a database called MyCustomers. Then create a table called tblCustomerInfo with these fields:
ID – autonumber
Email – text field
Name – text field
Then, enter some test data. For this example, let’s say we have a database containing 30 customers and we only want to display 10 customers per page.
Next, create a page called /DisplayCustomers.asp and copy the below code into your page:
<!--#INCLUDE VIRTUAL="/includes/connection.asp" -->
<table>
<%
DIM mySQL, objRS
mySQL = "SELECT * FROM tblCustomerInfo"
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.CursorType = 1
objRS.Open mySQL, objConn
DIM intPageRecords, intRecords, intRecordCount, intCurrentPage
DIM intNumberOfPages, intDisplayPage
intPageRecords = Request.Querystring("page")
IF intPageRecords = "" THEN intPageRecords = 1 : intRecords = 1
intRecords = intPageRecords
intPageRecords = ((intPageRecords - 1) * 10) +1
intRecordCount = 0
IF NOT objRS.EOF THEN
objRS.Move (intPageRecords - 1)
DO WHILE intRecordCount < 10 and NOT objRS.EOF
%>
<tr><td><%=objRS("Email")%> - <%=objRS("Name")%></td></tr>
<%
objRS.MoveNext
intRecordCount = intRecordCount +1
Loop
END IF
%>
</table>
<%=intPageRecords%> - <%=intPageRecords+(intRecordCount-1)%> of <%=(objRS.RecordCount)%> customers
<p>Scroll Through More Customers</p>
<%
intCurrentPage = Request.Querystring("page")
IF intCurrentPage = "" THEN intCurrentPage = 1
intNumberOfPages = int(objRS.RecordCount \ 10)
IF objRS.RecordCount MOD 10<> 0 THEN intNumberOfPages = intNumberOfPages + 1
Response.Write("Pages: [")
FOR intDisplayPage = 1 TO intNumberOfPages
IF Cint(intDisplayPage) = Cint(intCurrentPage) THEN
Response.Write " <b>" & intDisplayPage & "</b> "
ELSE
Response.Write " <a href=""news.asp?page=" & intDisplayPage & """>" & intDisplayPage &_
"</a> "
END IF
NEXT
Response.Write ("]")
%>
Finished! You now have your very own scrollable customer list.