Let's create a Blog and show all the items on a page.
First lets create a simple table and add a few fields:
Let's add a few records to the table.
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.
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.
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!
Now lets create a connection to our data.
'CREATE A CONNECTION TO THE DATABASE
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Provider = "Microsoft.Jet.OLEDB.4.0;"
Set rs = Server.CreateObject("ADODB.RecordSet")
Conn.Open Request.ServerVariables("APPL_PHYSICAL_PATH") & "/blog.mdb"
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>"
'CLEANUP AND CLOSE
Set rs = Nothing
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
intFinish = intStart + (rs.PageSize - 1)
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>"
If rs.EOF Then Exit For
' 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>
' 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>
Let's not forget to close the database connection.
'CLEANUP AND CLOSE
set rs = Nothing
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.
'rs.CursorLocation = adUseClient; '3
'rs.CursorType = adOpenStatic; '3
'rs.LockType = adLockBatchOptimistic; '4
'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)
'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)
'1 = SQL command
'2 = table
'3 = stored procedure
'4 = unknown
'rs.Open SQLStr, Conn, 3
'rs.Open SQLString, Connection, Cursor, Locktype