FrontPage Tips by Ward Cameron Enterprises
Take Your Web Site to New Levels

Super Redirect - Redirect With A Parameter to one of several pages based upon data in several tables

Sometimes, it's useful to be able to create a OK, here's the situation. In MountainNature.com, I had three tables, one with data on plants, another on birds and a third on wildlife. I also had three database pages that display data from the tables is part of an online field guide. Since the information you track is different for each, it makes sense to have the data in different tables.

I wanted to offer uses a quick species finder search page. First they would select plants, animals or birds from a drop-down box. If they selected plants, this would open a second drop-down box that would list all of the plants with both common and Latin names sorted in a list (essentially displaying two fields in the same table). Selecting any one would open a page that would display the results.

I thought that I needed to create a unique query that would pass a table value to a query and then pass the ID of the record. Finally, it would need a way to determine which page to send the query to.

Suddenly I had an epiphany

I realized that all I really needed to do was build a parameter based hyperlink. When you examine most ASP pages, you have a unique hyperlink with a parameter added to the end of the link to tell the page what record to display.

If I had different target pages then all I really needed was to find a way to build a hyperlink that would contain the name of the target page AND the ID value of the database record that I wanted to display.

For this to work, I needed to do three things:

1. First, I needed to pass a parameter that would contain the target page. All that would be missing would be the ID tag for the particular species.
2. This would take them to a second form where they will pick the individual record they would like to see. Their selection on this page will provide the missing ID tag to the redirect page.
3. Finally, I needed a redirect page that would accept the link address, add the ID tag to the end of the address and then redirect to the correct page for displaying plant data.

Check it out here

This took a great deal of planning to execute. In order to illustrate the technique, we'll do the same thing using the Northwind database. In our case, we'll create the option to select from either customers or suppliers, and then open a page that displays the appropriate results.

Step 1. Begin the Process in Microsoft Access

The process begins by making some changes to the Northwind database. We need to do two things in Access. First, we need to create a table to hold the main portion of the hyperlink. Second, we need to create a query for each table that will match the link code.

To begin, create a new table in the Northwind database called TableLink. Give it three fields, Table, Link and TableSelectID. Set the TableSelectID as an autonumber (key) and the others as text values. It is in this table that you will provide the basic link address that your final page will be sent to.

Add values to the table so that it looks like the following image:

You can see from this image that the first field lists the table names and the second lists the hyperlink address. The only thing missing from the address is the ID value that will be passed after the ?

Our final task in Access involves creating two special queries that will help us create two new queries that will allow us to filter our data based upon your selection in the first step of the process.

Let's create the first query for the Customers table. Create a new query and attach the Customers table. Add only the CustomerID and CompanyName fields. Your query will look like the following:

In the first blank column, enter the following text:

Link: "customer.asp?CustomerID="

This will create a new query column called Link with the value customer.asp?CustomerID=. We'll use this information later. Save the query as qCustomerLink.

Your query will look like the following:

Now do the same thing for the Suppliers table and save the query as qSupplierLink.

In qSupplierLink use the following value in your blank column:

Link: "supplier.asp?SupplierID="

This will create the matching supplier link value.

Step 2. Create the Pages to Receive the Database Results

Now, we'll create our final results pages, one each for the customers and  supplier tables. Each table records different information so it's important to have a different results page for data from each table.

To create these tables, open a blank page and select Insert-Database-Results. The process is the same for each page so I'll illustrate the Customer.asp page as an example.

Follow these steps as you work your way through the Database Results Wizard (DRW):

1. Step 1. Select the Northwind database connection
2. Step 2. Under Record Source, select the Customers table
3. Step 3. By default, all the fields in the table will be included. This is fine. Click the More Options button and then Criteria. In the Criteria window, click add and select the CustomerID as the Field Name, Contains as the comparison and the Value should also be CustomerID. You can deselect the Use this search form field as we won't be creating a search form. Click OK twice to return to the More Options window. Click the Ordering Button and add CompanyName. Now click the Defaults button. This will allow you to add a default value for CustomerID. In this way, if someone opens the page without passing the CustomerID parameter, the page will display a default record. Without this, it will display an error. When the defaults screen opens, you'll see CustomerID as the Name and the Default Value will be blank. Click the Edit button and enter a percent sign as the value. This is a wild card character that will return all the values. If someone opens the page without using our search forms, it will return all the records in the Customer table beginning with the first record alphabetically. When you go through this process for the Supplier tables, you use the value "1 or 1" (no quotations) as the default since they use a numerical autonumber key for their ID values. Also, when you set the criteria in these tables use SupplierID Equals SupplierID rather than Contains as we did in this example. This is simply due to the fact that the Customer table uses a text key and the other tables use a numerical key.
4. Step 4. Select List - one field per item and leave all the other default settings as they are.
5. Step 5. Select Split Records Into Groups and add 1 as the value for the number of records per group.

You can check out the results pages here:

• Customer.asp
• Supplier.asp

Step 3. Finally Create RedirectStep1.asp

As you saw from the example at the beginning of this tutorial, we'll create a two step search form In the first step, you'll select either Customers or Suppliers from a drop-down box.

On a blank page, select Insert-Form-Form. This will place a blank form on your page. Now add a few blank lines at the top of form. On the first line, select Insert-Database-Results to open the Database Results Wizard.

• Step 1. Select the Northwind Database Connection.
• Step 2. Select TableSelect as your data source.
• Step 3. Remove TableSelectID from your list of fields. Now click the More Options button and then Ordering. Sort by the Table field in Ascending order.
• Step 4. Select Drop-down list as the format, Table as the Field to Display and Link as the field to submit.
• Step 5. Display all records together and deselect Add Search Form if it is selected.

Right click on the form and select Properties. Select Send to Other as the action and then click Options. Type RedirectStep2.asp as the target and Post as the Method. Save the form as RedirectStep1.asp.

Step 4. Create RedirectStep2.asp

Don't close RedirectStep1.asp. Instead Select File-Save AS and save the file as RedirectStep2.asp. This will save us some time in creating this second step in the process. We need to make a few changes to the DRW to make this work. In Step 1, your users will select the Table name and this will send a Link parameter to the second page. We need to tell it to look for that parameter.

Right click on the DRW and select Database Results Properties. This will open up the DRW again. Step through until you get to Step 3 of the Wizard.

In Step 3, click the More Options button and then Criteria. In the Criteria window select Add. You will add the parameter Link Equals Link.

Now, on a blank line beneath the first DRW, you need to add a second. Select Insert-Database-Results and use the Northwind Database Connection. In Step 2, select Custom Query and then Edit. Type the following code into the edit window:

SELECT CustomerID AS ID, CompanyName, Link
FROM qCustomerLink
WHERE Link='::Link::'
UNION
SELECT SupplierID AS ID, CompanyName, Link
FROM qSupplierLink
WHERE Link='::Link::'

This SQL statement does several things. First, it selects the CustomerID (renames it ID for the purposes of this exercise) and Link fields from qCustomerLink and then limits the view to show only those records were the Link field has been received as a parameter.

By creating a UNION query, we can link the data in two queries as if they were in a single long table.

The second SELECT statement does the same thing with the qSupplierLink query. Again, if the Link field does not match, the records will NOT be returned. 

Click the Verify Query button to make sure that you've not made any errors. Now step through the remaining steps and select Drop Down List in Step 4, with CompanyName as the field to display and ID as the submitted field.

Step 5. Create the Redirect Page

Finally, you need to create a page that will accept the Link AND the ID parameter, glue them together into a single parameter and then open the appropriate page. Open a blank page and switch to HTML view. Delete all the existing code and type the following:

<%@ language="JScript"> %>
<%
Response.Buffer = true;
var Website = Request.Form("Link") + Request.Form("ID");
Response.Redirect( Website );
%>

You don't need any HTML code on the page since your visitors will never see this page. The page code redirects them to the page specified in the parameter values. In this case the page takes the Link parameter and adds the ID value so that the page can be directed to the appropriate page and record.