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
By: 
