If you have used Dreamweaver to create dynamic pages, you are probably familiar with the Repeat Region server behavior and the concept of Next/Previous records. You are probably familiar with the Master/Detail pageset concept: the Master page contains a table listing your records with links that allow you to drill down into a Details page. The Details page gives you the details about a particular record. Unfortunately, the Details page does not allow you to create the Previous/Next links easily. This is often desirable in a site, such as in a photo album, real estate listing, or other type of list where the user might want to remain on a Detail page and simply cycle through the records rather than go back to the Master page again. This tutorial will show how to create a SQL stored procedure that will supply Previous/Next links to any ColdFusion or classic ASP page.

Setting Up

First, you'll need a sample database table to use. I'll use a simple blog table named blog_items that was also used in the series on creating a simple blog. To create the table, run the following script in SQL Server Query Analyzer. The table can be placed in any database, or a blank database. (A full script for the table and stored procedure is available in the download at the end of the tutorial.)

CREATE TABLE blog_items (
blog_item_id int IDENTITY (1, 1) NOT NULL ,
blog_item varchar(1000) NULL ,
blog_item_datetime datetime NULL ,
blog_item_title varchar (255) NULL
)

And you'll need some sample data:

INSERT blog_items (blog_item, blog_item_datetime, blog_item_title)
VALUES ('There is a storm coming!','1/2/2002 3:45:00 PM','Bad weather today')
INSERT blog_items (blog_item, blog_item_datetime, blog_item_title)
VALUES ('Help! Snow is on the way!','1/3/2002 3:45:00 PM','Storm on the horizon')
INSERT blog_items (blog_item, blog_item_datetime, blog_item_title)
VALUES ('Thankfully the storm has passed us by!','1/4/2002 3:45:00 PM','The storm has passed')
INSERT blog_items (blog_item, blog_item_datetime, blog_item_title)
VALUES ('There is a another storm coming!','1/2/2002 3:45:00 PM','Another storm coming!')
INSERT blog_items (blog_item, blog_item_datetime, blog_item_title)
VALUES ('The storm is here! Snow is falling and we are cold.','1/3/2002 3:45:00 PM','It is here!')
INSERT blog_items (blog_item, blog_item_datetime, blog_item_title)
VALUES ('The storm is over. Now we have to dig out of the snow.','1/4/2002 3:45:00 PM','Buried in the snow')

The tutorial assumes you have used Master Detail Page Sets before, but I'll run through the steps to create the basic page so that you can follow the rest of the tutorial. Sample master/detail pages are included in this article's support files.

1. Create a blank page named master.asp (or master.cfm). Save it.
2. Put a recordset/query on the page named rsBlog using the Recordset dialog box (Insert > Application Objects > Recordset) and save the page again. The SQL to paste in the box should be:

SELECT * FROM dbo.blog_items ORDER BY blog_item_datetime DESC

3. Choose Insert > Application Objects > Master Detail Page Set (or click it on Application tab of the Insert bar).
4. Set up the Master page to show the blog_item_datetime and blog_item_title fields. Set up the detail.asp (or detail.cfm) page to show blog_item_title, blog_item_datetime, and blog_item fields. The dialog box should look something like Figure 1. When it does, click OK to apply the server behavior.
   

   
   Figure 1  The Master Detail Page Set dialog box

5. The Master Detail Page Set process leaves a few extraneous items in the SQL that I like to remove. First, on the Master page, remove the asterisk (*) and replace it with the actual field names needed so that the SQL looks like this:

SELECT blog_item_id, blog_item_datetime, blog_item_title
FROM dbo.blog_items
ORDER BY blog_item_datetime DESC

6. Next, remove the ORDER BY clause in the Detail page, as it is not needed there.

If you browse the Master page now, you should be able to drill down to the Detail page.

The Concept

When you have a repeat region, you can cycle through records easily using the Recordset Navigation Bar. When you have a Details page, it is not so easy because your recordset only contains one record: the SQL statement uses a WHERE clause to pull only one record. You cannot get the previous or next record easily, because the primary key may or may not be an integer, and it may or may not be sequential. For example, if a record is deleted, even if the primary key is a sequential integer there will be a hole in the list of records. The only way to accurately retrieve the previous and next link is to run two separate queries: one to retrieve the primary key closest to the current primary key but smaller, and one to retrieve the primary key closest to the current primary key but larger. The following stored procedure will do that, and return the two results as parameters.

The Stored Procedure

The stored procedure we'll use, spGetNextAndPreviousItems, has one input parameter (the current primary key) and two output parameters (the previous primary key and the next primary key). The stored procedure can be created in Query Analyzer or SQL Server Enterprise Manager:

CREATE PROCEDURE spGetNextAndPreviousItems
@id int,
@prev int output,
@next int output
AS

SELECT top 1 @prev = blog_item_id
FROM blog_items
WHERE blog_item_id< @id
ORDER BY blog_item_id DESC

SELECT top 1 @next = blog_item_id
FROM blog_items
WHERE blog_item_id> @id
ORDER BY blog_item_id

One point worth mentioning is that if the current record (the @id parameter) is the first record in the table, the resulting output parameter (@prev) will be null. Similarly, if the current record is the last record in the table, the resulting output parameter (@next) will be null. This allows you to put conditional regions around your previous and next links on the page.

Note: The stored procedure uses output parameters for convenience, but could have also been written to use a SELECT statement which would have returned a recordset to your calling page.

You can test the stored procedure in the Query Analyzer as well, by using some code like the following:

DECLARE @MyOutputPrev int
DECLARE @MyOutputNext int

EXEC spGetNextAndPreviousItems
@id = 3,
@prev = @MyOutputPrev OUTPUT,
@next = @MyOutputNext OUTPUT

SELECT @MyOutputPrev AS Prev_Item, @myOutputNext AS Next_Item

Change the value of the @id variable to see the different previous and next items in the table.

Calling the Stored Procedure

To call the stored procedure, simply pass the current record id to the procedure. Dreamweaver has a built-in server behavior in the Bindings panel that allows you to automate the process. The parameters are a little different for each server model, so I'll do ColdFusion first:

1. Open the Bindings panel (Window > Bindings) and choose Stored Procedure.
2. Choose your data source and the stored procedure name from the drop-down lists.
3. For the incoming parameter, set it up as a URL variable named recordID. Make sure your master.cfm page passes the recordID variable. (The ColdFusion version of the Master Detail Page Set object will use the name recordID by default.) The completed dialog box looks like Figure 2.

   
   Figure 2  The ColdFusion version of the Stored Procedure dialog box

For ASP, follow these steps:

1. Open the Bindings panel (Window > Bindings) and choose Command (Stored Procedure).
2. Choose Stored Procedure from the Type dropdown list.
3. At the bottom, in the Database Items area, expand Stored Procedures and select sp_GetNextAndPreviousItems.
4. Click the Procedure button.
5. For the incoming parameter, set the type as Integer, size as 4, default value as 0, and run-time value as Request("blog_item_id"). The box should resemble Figure 3.

   
   Figure 3  The ASP version of the Stored Procedure

6. When you're finished, click OK to apply the server behavior.

To create the links, complete the following steps for the Details page for both ASP and ColdFusion:

1. Type the words Previous and Next on the page below the HTML table.
2. Select the word Previous and open the Property inspector (Window > Properties).
3. Click the folder icon next to the Link text box and type in your page name (detail.cfm or detail.asp).
4. Click Parameters to add the Request parameter blog_item_id for ASP or the URL parameter recordID for ColdFusion, as shown in Figure 4.

   
   Figure 4  Adding a parameter to the link

5. Click the lightning bolt icon in the Value column. This launches the Dynamic Data dialog box (see Figure 5).

   
   Figure 5  Adding the dynamic variable for the link

6. Select the out_prev variable to add it to the link.
7. Click OK and repeat the steps for the Next link.

The final step is to hide the links if you are on the first or last record. The code is different for ASP and ColdFusion, as shown below:

ASP

Around the Previous link:

<%If Command1.Parameters.Item("@prev").Value <> "" Then %>link<%End If%>

Around the Next link:

<%If Command1.Parameters.Item("@next").Value <> "" Then %>link<%End If%>

ColdFusion

Around the Previous link:

<cfif out_prev NEQ "">link</cfif>

Around the Next link:

<cfif out_next NEQ "">link</cfif>

Now, if you browse the master page, you can drill down to the detail page and be able to navigate directly from the detail page using the Previous and Next links. This stored procedure can be easily modified to work with any database table.

Conclusion

Dreamweaver has some navigation code built into it for repeat regions, but nothing for navigating a details page. This tutorial has shown one way to do it with a stored procedure that is easy to implement and versatile enough to use with any database table.