If you can't explain it simply,
you don't understand it well enough.
-Albert Einstein
 
Home   News   Courses   Tips   Templates   Forums   Help   Order   Contact   Logon  
 
NEW: Excel Expert 11 - Learn XLOOKUP    dismiss
Tips & Tricks

New Tips Added Weekly!
Click here to get on our Mailing List

   
 
ASP

Paging

How to create paging in your lists.

In this tutorial you will learn how to add paging to your lists.

Lets start by creating a basic html page.

	<!DOCTYPE html>
	<html>

	<head>   
		<title>ASP Paging</title>
		<meta charset="utf-8">
		<link href="/css/style.css" rel="stylesheet">
		<script src="/scripts/script.js"></script>
	</head>

	<body>
	</body>

	</html>
	

Let's create a Blog and show all the items on a page.

First lets create a simple table and add a few fields:

Field Name Data Type
BlogID AutoNumber
BlogTitle Text
BlogMessage Memo
DateAdded Date/Time

Let's add a few records to the table.

BlogT
BlogID BlogTitle BlogMessage DateAdded
1 My First Post Hi All, I've decided to start a blog, yes I could have added one by another company but thought I’d create my own for training/learning principles etc. 08/11/2010 16:44:19
2 RSS Feeds I've added RSS Feeds for this blog, the Music list and the film list
They are all separate so you add only the ones you wish, if you want to.
08/11/2010 11:20:48
3 CSS3 Just seen a video by Think Vitamin and they showed a cool example of the border-radius and transitions (Only in Chrome or Safari)
Also cool text
Check this out in Safari!
09/11/2010 05:10:39

Now lets create a connection to our data.

	<%
		'CREATE A CONNECTION TO THE DATABASE
		Dim Conn
		Set Conn = Server.CreateObject("ADODB.Connection")
		Conn.Provider = "Microsoft.Jet.OLEDB.4.0;"
		
		'CREATE RECORDSET
		Dim rs
		Set rs = Server.CreateObject("ADODB.RecordSet")

		Conn.Open Request.ServerVariables("APPL_PHYSICAL_PATH") & "/blog.mdb"
		
		Dim SQLStr
		SQLStr = "SELECT * FROM BlogT ORDER BY DateAdded DESC"
		
		rs.Open SQLStr, Conn, 3

		While NOT rs.EOF
			Response.Write "<p> ID:" & rs("BlogID") & " " & rs("BlogTitle") & "</p>"
			rs.MoveNext
		Wend

		'CLEANUP AND CLOSE
		rs.Close
		Set rs = Nothing
		Conn.Close
		Set Conn = Nothing
	%>
	

Now say this list expands each day, if we were to show every record on the page it would go on forever. The page would take a while to load and this would be hard to search through.

Lets add some code to make some paging possible.

We need a few variables to keep track of some information.

		Dim intPageCount		' The number of pages in the recordset.
		Dim intRecordCount		' The number of records in the recordset.
		Dim intPage			    ' The current page that we are on.
		Dim intRecord			' Counter used to iterate through the recordset.
		Dim intStart			' The record that we are starting on.
		Dim intFinish			' The record that we are finishing on.
	

So theses a pretty obvious what we are using them for.

Now just after we open the recordset we shall set a few properties.

		rs.PageSize = 5                     'Amend to an amount you wish to use.
		intPageCount = rs.PageCount
		intRecordCount = rs.RecordCount
	

Yes you should be careful about using the RecordCount property but we still shall.

Now you must double check to make sure that you are not before the start or beyond end of the recordset. If you are beyond the end, set the current page equal to the last page of the recordset. If you are before the start, set the current page equal to the start of the recordset.

		If Cint(intPage) > Cint(intPageCount) Then intPage = intPageCount
		If Cint(intPage) <= 0 Then intPage = 1
	

Lets set some of the counters.

		If intRecordCount > 0 Then
			rs.AbsolutePage = intPage
			intStart = rs.AbsolutePosition
			If Cint(intPage) = Cint(intPageCount) Then
				intFinish = intRecordCount
			Else
				intFinish = intStart + (rs.PageSize - 1)
			End if
		End If
	

You can add a line to tell the user which records they are viewing.

	<h4>You are now viewing records <%=intStart%> through <%=intFinish%>.</h4>
	
	<%
		For intRecord = 1 to rs.PageSize
			Response.Write "<div> ID:" & rs("BlogID") & " " & rs("BlogTitle") & "</div>"
			rs.MoveNext
			If rs.EOF Then Exit For
		Next

			' Check to see if the current page is greater than the first page
			' in the recordset.  If it is, then add a "Previous" link.
			If cInt(intPage) > 1 Then
	%>
		   <a href="/blog/test.asp?page=<%=intPage - 1%>"><< Prev</a>
	<%
		End If
		' Check to see if the current page is less than the last page
		' in the recordset.  If it is, then add a "Next" link.
		If cInt(intPage) < cInt(intPageCount) Then
	%>
		   <a href="/blog/test.asp?page=<%=intPage + 1%>">Next >></a>
	<%
		End If
	%>
	

Let's not forget to close the database connection.

		<%	
			'CLEANUP AND CLOSE
			rs.Close
			set rs = Nothing
			Conn.Close
			Set Conn = Nothing
		%>
	

Now we have a fulling working page example.

All that's left is to pretty up the page but that is another tutorial in it's self.

Notes

	'rs.CursorLocation = adUseClient; '3
	'rs.CursorType = adOpenStatic; '3
	'rs.LockType = adLockBatchOptimistic; '4

	'Cursors:
	'0 - adForwardOnly (can only scroll through recordset one at a time, in a forward direction)
	'1 - adOpenKeyset (can scroll through forward or backwards)
	'2 - adOpenDynamic (real-time changes, highest resource requirements)
	'3 - adOpenStatic (most often used if scrolling needed)

	'Lock Types:
	'1 - adLockReadOnly (can only read data, no updates)
	'2 - adLockPessimistic (others can't change while locked)
	'3 - adLockOptimistic (others can access data while in use)
	'4 - adLockBatchOptimistic (Allows multiple-user updates)

	'Options
	'1 = SQL command
	'2 = table
	'3 = stored procedure
	'4 = unknown

	'rs.Open SQLStr, Conn, 3
	'rs.Open SQLString, Connection, Cursor, Locktype
	

 

Alex Hedley (Avatar) By: Alex Hedley


Click here to sign up for more FREE tips

 

You may want to read these articles from the 599CD News:

 

Learn
 
Accessindex
Excelindex
Wordindex
Windowsindex
PowerPointindex
Photoshopindex
Visual Basicindex
ASPindex
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Free Upgrades
Insider Circle
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Articles
Search The Site
Waiting List
Production Schedule
Collapse Menus
Help
 
Live Chat
Customer Support
WalkThru Tutorials
Troubleshooting
FAQs
TechHelp
Consulting Services
About
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Gift CDs
Contact
 
Live Chat
General Info
Support Policy
Contact Form
Email Richard
Mailing Address
Phone Number
Fax Number
Course Survey
Facebook    Twitter

YouTube Channel    LinkedIn

Blog RSS Feed