Creating Filtered Recordsets

Twice now, once each in this lesson and the preceding one, you have created recordsets and displayed their data. In both cases, you just retrieved all of the information stored in the database and bound it to the page. But you have a different problem now. To get the calculator to function properly, you need to plug in the adult and child prices for the selected tournot all of the tours and not the first tour, but the one that the user selected.

How's that going to work? The user selected a tour from the menu on the form and submitted it. On tourprice_processor.asp, the tourID associated with that tour has been included as a form variable. Thus, when you query the database, you'll construct something along the following lines (in pseudocode):

[View full width]
Retrieve the adult price (basePriceAdult) and the child price (basePriceChild) from 
tbl_tours where tourID equals the tourID submitted by the user on the form.

It is very helpful to formulate your intentions very clearly before attempting any sort of programming, even if the programming (in this case SQL programming) is masked behind a graphic interface: Dreamweaver's Recordset dialog.

Open tourprice_processor.asp. Use the Bindings panel to open the Recordset dialog.

When you create a recordset in Dreamweaver, it persists in the individual page only.
Enter rs_tourprices as the Name, conn_Newland as the Connection, tbl_tours as the Table, Selected in the Columns category, with the following fields selected: tourID, tourName, basePriceAdult, and basePriceChild.

As it stands (which you could see for yourself by clicking Test), this query returns data from all 19 records in tbl_tours in the four fields you specified. But you want the query to return only the data for the tour that the user specified in the form.
In the filter category, create the following formula: tourID = Form Variable tourName by entering the appropriate choices in the four drop-down menus.

In this step, you are adding an additional criterion to the query. In pseudocode, you are saying,
```
[View full width]
retrieve all the specified information from the table, but only from the record that 
matches the record the user selected in the form
```
Click the Test button. Enter 9 in the Please Provide a Test Value dialog, and click OK.

The test this time works differently than it did in the past. This time, you must specify a test value. The reason is that the query needs a value to be sent from the form, but in the authoring environment, that data doesn't exist. So the dialog appears prompting you for a value.

This time, when the output window opens, only one tour is listedMachu Picchu, if you entered 9. If you go back and enter different test values, you'll see different tour records that have been retrieved from the database.
Click OK to exit the test output, and click OK again to save the recordset.

Once again, the appearance and functionality of the page isn't changed, because creating a recordset only retrieves the data and stores it in the server's memoryyou're not using it yet.