Recordset Paging with Page Numbers

First I didn't write this but found it on a site that no longer exists and I wanted to put it up because many people have this question and most people answer it by telling them to buy an extension. You should not have to spend $40 in order to do this in my opinion.

INTRODUCTION:

I had a request a while back from a guy who wanted to display page numbers (ala Google) at the bottom of his repeat region. Now, I must confess that I had never done this before. However, I did have an old chunk of code knocking around from my "UltraDev" days, so I thought I would give it a shot and see if it still worked with Dreamweaver MX 2004.

I take no credit for this code. I can't even remember where I found it. It's just one of those things that you jot down in a little black book just in case it should come in useful one day.

 

The Database:

The database for this exercise is a very simple affair with a single table containing three fields.

    numID = Autonumber - Primary Key
    numFirstName = Text
    numLastName = Text

I just populated the database with 50 names in order to give us something to work with. A copy of the database that I used is included in the download files which are available from the link above.

The Page:

1. Set up a new site, create a default.asp page, create a DSN for the database and create the appropriate connection files.

2. Create a table on the default.asp page and label two columns First Name and Last Name as shown below.

Once you have created the table, create a basic Recordset

    * Name = rs1
    * Connection = whatever you have called your connection
    * Table = There is only 1 table in the database so that's what will be shown.
    * Columns = All
    * Filter = None
    * Sort = None

The only important thing here is the name of the Recordset "rs1". This is the name that I shall be referring to when we do our bit of hand coding down at step 17. If you change the name of the Recordset here, you will also have to change the references to it in step 17.

Just keep in mind that it is ALWAYS better to get any tutorial working as it was written BEFORE you start messing around changing the names of Recordsets and variables etc.

 

3. Open the Bindings panel and bind the numFirstName and numLastName to the appropriate cells of the table.

 

4. Select the table row containing the data tags, select Repeat Region from the Server Behaviors panel and apply a repeat region that shows just 5 records at a time.

 

5. In order to display recordset page numbers we need to grab some information from the standard blocks of code that Dreamweaver generates for use with it's inbuilt record counting and recordset paging routines. In order to get Dreamweaver to create these blocks of code we first have to insert the features in Design View. If we don't want these features permanently displayed as part of our page design it is a simple matter to go into the code and "Comment Out" the unwanted displays at a later stage. I will talk about this at step 20.

OK, go take a look at your table and add the following text.

    record xx to xx of xx (in the top row)
    <<Prev      Next>> (in the bottom row)

I have stuck some non breaking spaces (&nbsp;) between the Prev and Next text in order to give myself space to insert some code at a latter stage.

 

6. With the Bindings panel open select the first xx (after the word record) then select [first record index] from the Bindings panel and hit the Insert button.

 

7. You will see that xx has been replaced by {rs1_first}. This dynamically displays the number of the first record in the recordset.

 

8. Repeat the process binding [last record index] and [total records] as shown in the picture below.

 

9. Now highlight the text <<Prev and select Recordset Paging > Move To Previous Record from the Server Behaviors panel.

 

10. Hit OK when the Move To Previous Record window pops up.

 

11. Now highlight the text Next>> and select Recordset Paging > Move To Next Record from the Server Behaviors panel.

 

12. Hit OK when the Move To Next Record window pops up.

 

13. Your table should now look like this.

 

14. Hit the Live Data View button and you should see the first 5 records on the database displayed.

 

15. Place your cursor mid way between the <<Prev and Next>> text.

 

16. Switch to Code View and you will find that your cursor is in the middle of those non breaking spaces I was talking about earlier. Hit the Return key a two or three times in order to give yourself a few lines of space to insert the code that creates the page numbers.

 

17. The code that writes the page numbers is as follows .......

    <%
    Pnum_counter = 0
    For i = 1 to rs1_total Step MM_size
    Pnum_counter = Pnum_counter + 1
    Pnum_PageEndCount = i + MM_size - 1
    if Pnum_PageEndCount > rs1_total Then Pnum_PageEndCount = rs1_total
    if i <> MM_offset + 1 then
    Response.Write("<a href=""" & Request.ServerVariables("URL") & "?" & MM_keepMove & "offset=" & i-1 & """>")
    Response.Write(Pnum_counter & "</a>")
    else
    Response.Write("<b>" & Pnum_counter & "</b>")
    End if
    if(Pnum_PageEndCount <> rs1_total) then Response.Write("|")
    next
    %>

Note that I have highlighted the references to our Recordset name (rs1) in red. As I said in step 2, if you changed the name of the recordset you would also have to change the references to it in this block of code.

Also, as I said in the introduction to this tutorial, I did not write this code. I found it knocking around somewhere back in the days when I was playing with UltraDev. As such, if I were to attempt to fully explain the logic behind the code, I would probably get it wrong ;-). However, I can tell you that it looks in the code blocks that DMX writes and uses the variable MM_size to work out how many page numbers are going to be required. Then it creates a loop to create those numbers. It then uses the MM_keepMove variable from the " next, and previous links" block of code to help create the required links.

The Response.Write("|") bit of code places the vertical bar between each page link. You could replace the "|" with a dash "-" or whatever.

Once you have your code pasted into the page, it should look like this .....

 

18. Your table should now look like this. If you can't see the little gold shield with ASP written on it go check your invisible elements preferences.

19. Hit Live Data View again and you will see the page numbers displayed in their full glory.

20. As I mentioned earlier, if you don't want the total records etc. included as a feature of your page design you can simply comment the appropriate bits of display code out with a ' like so ....

record <%'=(rs1_first)%> to <%'=(rs1_last)%> of <%'=(rs1_total)%>

or MANUALLY delete the code. If you try to delete the code in Design View, Dreamweaver goes into a world of it's own and try's to delete the code blocks needed to make the page numbers work. You have been warned!

 

This definitely works and with it as a start you can further manipulate it to your liking.