Computer Learning Zone CLZ Access Excel Word Windows

If you are born poor it's not your mistake, but if you die poor it's your mistake.

-Bill Gates
 
Home   Courses   Seminars   Templates   TechHelp   Fast Tips   Forums   Help   Contact   Join   Order   Logon  
 
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

 

 

Learn
 
Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Seminars
More...
Customers
 
Login
My Account
My Courses
Lost Password
Memberships
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Help
 
Customer Support
Web Site Tour
FAQs
TechHelp
Consulting Services
About
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
Memberships
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Contact
 
Contact Info
Support Policy
Email Richard
Mailing Address
Phone Number
Fax Number
Course Survey
Blog RSS Feed    Twitter

YouTube Channel    LinkedIn