Rick Curtis - Tutorial 1

Passing a Parameter Value from one Recordset to Another on the Same Page

Having more than one recordset on a page is a critical requirement for many Web database applications. In many cases you need to be able to relate the information in the two recordsets so that when one changes, the other does as well. I was initially able to do this by passing a hidden field value to the Details page, however, I also wanted users to be able to navigate through the details records from the search but found that the default code written by UltraDev does not easily allow you to do this. However, with just a few modifications you can easily link your two recordsets together. Below I'll explain how to do this by passing parameters to Stored Procedures in SQL 7.

[The following example is in VBScript and was created on NT 4.0 Workstation, PWS and SQL 7.0.]

My example comes from a Jobs database. Users can search for various Jobs using parameters like Job Region, Job Type and Job Category. Each job can include a number of categories that a user may want to search for. Here is the basic database design.

So what we have here is a typical parent-child relationship (one to many) between Jobs and JobCategory. Then the actual categories for JobCategory are stored in the separate Category table

The initial Search Page is a form-based page that passes form values as parameters onto a Job Posting page which displays a summary of the Job Postings in a repeat region. From the repeat region table the user can click on the Job Title to go to the Job Detail page. There are two recordsets used in this application.

• rsJobSearch - is a recordset based on a stored procedure that accepts the search criteria as parameters for the stored procedure and returns the appropriate records.
• rsJobCategories - is a recordset that accepts a JobID as a parameter and passes it to stored procedure to return all of the categories for that JobID.

Job Details Page

First create the rsJobSearch recordset on the page using the stored procedure (sample below). You will need to use the advanced dialog box to link to the stored procedure. For the rsJobSearch set the following values for the recordset. These correspond to the form fields being passed from the Search page and the names are the same as the parameter names in the stored procedure (except for the @ which UltraDev disallows). You will have to create a series of Request variables that correspond to the Run-time values in the table below. Now you can plop down all the recordset values you want to see on the Job Details page. Add the necessary server behaviors for Move to First Record, Move to Next Record, Move to Previous Record, Move to Last Record.

Now create the rsJobCategories recordset using the stored procedure (sample below). You will need to use the advanced dialog box to link to the stored procedure. First you need to edit the values for the rsJobCategories recordset. What you want to do is set the default value of rsJobCategories to the value of the JobID field from the rsJobSearch. Set the Recordset variables to the values below. 

Your ASP code defining the variable for the recordset should look like this: 

<%
Dim rsJobCategories__JobID
rsJobCategories__JobID = "1"
if(Request("JobID") <> "") then rsJobCategories__JobID = (rsJobSearch.Fields.Item("JobID").Value)
%>

Now create a table on the Job Details page and add the Categories value from the rsJobCategories. Set this table as a repeat region and to show all the rows from the recordset.

Tweak the Code

There are a few basic code tweaks that you will have to add to make this work.

1. You will need to move some of the code around. By design UltraDev creates all of the variables at the top of the page before the recordsets are created. However, the value of the JobID is not available to the page until the rsJobSearch recordset has been created. So we have to move some pieces of code. The first piece of code to move is the variable that we just modified above (the code in red). Cut this code and go to the very end of the code statement section that occurs right above the <HTML> tag. Paste the code here.
   
   
2. Now find the code that creates the rsJobCatgories database connection. Right below this code should be the code for the repeat region for the Job Categories. Cut both of these sections of code and paste them directly below the code you just moved in step 2.

New Code at the end of the VBScript Section

<%
Dim rsJobCategories__JobID
rsJobCategories__JobID = "1"
if(Request("JobID") <> "") then rsJobCategories__JobID = (rsJobSearch.Fields.Item("JobID").Value)
%>

<%
set rsJobCategories = Server.CreateObject("ADODB.Recordset")
rsJobCategories.ActiveConnection = "dsn=Jobs;"
rsJobCategories.Source = "{call dbo.spJobCategories(" + Replace(rsJobCategories__JobID, "'", "''") + ")}" rsJobCategories.CursorType = 3
rsJobCategories.CursorLocation = 3
rsJobCategories.LockType = 1
rsJobCategories.Open rsJobCategories_numRows = 0
%>

<%
Dim Repeat1__numRows
Repeat1__numRows = -1 Dim
Repeat1__index Repeat1__index = 0
rsJobCategories_numRows = rsJobCategories_numRows + Repeat1__numRows
%>

<html><head>...

[Note: You may notice that I have CursorLocation = 3 instead of the default 2. This is because my Jobs database has ntext fields (like Access memo fields) and the UltraDev code generates an ASP error while trying to read the large text field. Changing the cursor location fixes this. - Thanks to Adnan Smajlovic for this valuable tip.]

What Does it All Mean?

The Job Search page passes the following form elements to the Job Posting page using POST (EnterDate, CategoryID, RegionID, TypeID, PopulationID). These values are placed in Request variables and passed as parameters to the rsJobSearch stored procedure which then delivers the appropriate database records. The Job Posting page also utilizes the Go to Detail Page Server Behavior to link to the Jobs Detail page. This is where the magic begins.

The Job Details page has the same rsJobSearch recordset as the Job Postings page (and the same Request variables) to pull the same records from the database. The Job Details page also has the rsJobCategories recordset. What we have done is to rearrange the UltraDev code so that things are created in the correct order as the page is loaded. One of the important things that we did from moving the code is to have it happen after all of the code that UltraDev generates for moving forward and backward through the rsJobSearch data. I found that things simply didn't work unless the code was at the very end. As the Job Details page is called, the following things happen:

1. The various Request variables are passed to the rsJobSearch stored procedure and the recordset is created.
2. The value of the JobID for the currently displayed record is assigned to the variable rsJobCategories__JobID
3. The rsJobCategories__JobID is passed as a parameter to the rsJobCategories recordset and that recordset is created.
4. The page is rendered with the details from the rsJobSearch and the appropriate repeat region for that JobID populated by the rsJobCategories.
5. As you navigate from page to page the rsJobSearch recordset is recalled and the rsJobCategories__JobID is created again with the new JobID value which then repopulates the rsJobCategories recordset with the correct values. Voila!

So each time you move to a different parent record the appropriate child record is displayed in the repeat region.

Stored Procedures

Here is the code for the Job Search Stored Procedure. This is a dynamically generated SQL statement that is used to create the rsJobSearch recordset on the Job Posting page and the Job Details page.

Alter Procedure spJobSearch

Here is the JobCategories Stored Procedure. This takes the value of the JobID and selects all of the job categories for that JobID.

Alter Procedure spJobCategories

@JobID int

As
SELECT JobCategory.JobID, JobCategory.CategoryID, CategoryType.Category
FROM CategoryType INNER JOIN JobCategory
ON JobCategory.CategoryID = CategoryType.CategoryID
WHERE JobCategory.JobID = @JobID ORDER BY CategoryType.Category