22.1 Retrieving Information

Since databases can contain lots of information, you need a way to find just the data you need. Even though your company keeps information about its products, customers, suppliers, and so on in one database, you may only be interested in, say, an alphabetical list of all your customers. After securing that list, you might want to look at a particular customer's contact information, or perhaps the list of products that person bought.

22.1.1 Understanding Recordsets

To retrieve specific information from a database, you start by creating what's called a recordset. A recordset—also called a database query—is a command issued to a database asking for particular information: "Hey Database, show me all the customers listed in the Customers table." It's the heart of many database operations you'll perform in Dreamweaver (and a piece of jargon you can't escape in the dynamic-Web-page business).

Recordsets let you retrieve specified columns in a database. They can also sort records alphabetically or in numerical order, as when viewing a list of products from least to most expensive. In addition, a recordset can zero in on a specific record based on information submitted by a visitor to the site, or based on information provided in a URL. In essence, recordsets let you winnow down massive amounts of database information in a fraction of a second—a powerful benefit, indeed.

NOTE

ASP.NET note—Dreamweaver uses the term DataSet instead of recordset to refer to database queries in ASP.NET.

22.1.2 Creating Recordsets

Querying a database can be quite simple or extremely complex. Dreamweaver provides tools to get the novice database developer up and running quickly, while also supplying what's necessary to create more advanced recordsets. Whatever your level of expertise, you start by opening the Recordset dialog box using one of these methods (each of which assumes you've set up a server model as described in Chapter 21):

Click the Recordset button on the Application tab of the Insert bar (see Figure 22-1).
Choose InsertApplication ObjectsRecordset.
Click the + button on either the Bindings or Server Behaviors panels in the Application panel group (see Figure 21-12).

The Application tab of the Insert bar (shown here split in half to fit the page) provides one-click access to many powerful application objects, which automate common dynamic Web page building tasks. (See the next two chapters for details on the rightmost four buttons.)

Whichever technique you choose, the Recordset dialog box opens (Figure 22-2). This box lets you create a database query or recordset, and provides both simple and advanced modes of operation.

To create a simple query, make sure you're in the Simple mode. (If a button labeled Simple appears at the right edge of the dialog box, click it to make it say Advanced. Now you're in Simple mode.)

In the Name field, type a name for the recordset.
You can use any name you want, as long as it doesn't start with a number and doesn't contain any characters other than letters, numbers, and underscores (_).

A common technique is to begin the name with rs (rsProducts, for example). The rs helps identify the recordset when you're working in Code view.

The Recordset window lets you retrieve data from a database. The main window (pictured here) lets beginners search and sort databases for specific information. Advanced options let even seasoned database programmers take advantage of Dreamweaver's dynamic page-building abilities.

From the Connection menu, select a database connection.
The menu lists all of the database connections you've defined for the site. If you haven't yet created a connection, you can do so now by clicking Define and following the instructions for creating database connections in Section 21.6.
From the Table menu, select the table that will supply the data.
Information in a relational database is usually distributed among different tables, each of which holds information about a particular item, such as customer data or product data (see Section 21.4). For example, to get a list of customers from a database, you'd select the Customers table (or whatever its name happens to be).

NOTE

To retrieve data from more than one table at a time, you need to create an advanced recordset (see Section 22.1.6).

To select columns from which you want to extract data, click the All or Selected button. If you choose Selected, click the columns you wish to select.
By default, Dreamweaver highlights the All button, but you may not want to get data from all columns. For example, suppose your table contains lots of detailed information for each product your company sells. You may want to create a basic index of all your products that simply lists its name, price, and description. For this index, you won't need all of the details like SKU number, sizes, inventory status, and so on. Therefore, just select the three columns you're interested in.
To select multiple columns, Ctrl+click (-click) their names in the list in the Recordset dialog box.
It's always best to limit your recordset to just those columns whose information you need. The more data you retrieve, the more you make the application and database servers work and slow down your site, especially when the database is large.
Choose a Filter option, if you like.
In many cases, you don't want to retrieve every record in a table. For example, if you were looking for a phone number of a particular customer in your database, you wouldn't want the details on every one of your customers. Filters let you limit the records retrieved by a recordset. (Details in Section 22.1.3.)
Choose a Sort option, if desired.
Data from a database may not appear in any particular order. Dreamweaver's sort options let you sort information based on a particular column. For example, maybe you're creating a recordset that gathers the title and release date for every CD you own. You might want to sort the results in alphabetical order by the title of the album, or chronologically by the date they were released.
To sort database records, choose a column to sort by from the first Sort menu (Figure 22-2). Then select the sort order: either Ascending (A—Z, 0?0, earliest to latest) or Descending (Z—A, 10?, latest to earliest).

The simple recordset mode lets you sort by only one column. So, continuing with the above example, if you wanted to sort records by date (so the most recent CDs appear first) and then by name (so CDs with the same date are then listed in alphabetical order), you have to use the advanced mode (see Section 22.1.6).

To view the results of the recordset, click Test to open the Test SQL Statement window, which contains all records that match that query. If there are more than 25 matches, you can see the next group of results by clicking Next 25 at the bottom of the window. When you're done looking at the test results, click OK to return to the Recordset window.

If the test results looked right, click OK to close the Recordset window and add the code into the currently opened page.

NOTE

Unlike a database connection, which is listed in the Databases panel and is available to every page on the site, a recordset is specific to a particular page. (See Section 22.1.7 to learn how to reuse recordsets on other pages.)

22.1.3 Filtering Information

Although you may have selected a limited number of columns when creating a basic recordset, the final results of the recordset still include all of the records within the table. That's fine when you want a list of all items in a database, like when you're creating an index of all your company's products. But it's not so useful when you want a particular subset of those records, like a list of just the red toupees your company sells, or when you want details on a single record—the "Flaming Inferno 78B toupee," for example.

To search a database table for specific records, use the Filter option in the Recordset window (see Figure 22-3). A filter lets you compare the information in one database column with a particular value and then select records that match. Suppose, for example, that your products database table contains a column named Price. To find all products that cost less than $35, you could create a filter that looked for all records where the price column held a value of less than 35.

Filters let you limit the number of records retrieved by a recordset using information supplied in a URL or in a form submitted by a visitor to the site, among other ways. Using a filter, a recordset can identify and retrieve data for a single record in the database.

Using the Filter feature in the Recordset dialog box takes only a few steps:

Create a recordset as described in Section 22.1.2.
To create a filter, you must fill out the four form fields of the Recordset window's Filter options—three menus and one text field.
From the first Filter menu, select a column name.
This is the column that will be compared to a particular value. In the example above, you would select "price" from the menu to indicate the table's price column (see Figure 22-3).
From the next menu, choose a comparison operator (< or >, for example).
To find products whose prices are less than $35, for example, you would use the < (less than) operator. To find an exact value (all products that are exactly $35), use the = (equal to) sign. Comparison operators are described below.
Using the third Filter pop-up menu, select a source for the comparison value.
A filter compares the information in a table column against some other value. There are many different sources for such a comparison value. For example, on a Search page, you could create a form that allows visitors to type in a search term and click a Search button. In this case, the comparison value would come from a form. To set up this arrangement, you, the designer, would select Form Variable from this menu.
For complete information on selecting a source for a comparison value, see "Getting Comparison Values" on the facing page.
Into the lower-right Filter box, type a name or value.
The value for this field depends on the source you selected in the last step; type in the name of the form variable, cookie, session variable, or whatever. The one exception: If you selected Entered Value in the previous step, type a specific value in this field. For instance, to compare the "price" column to a specific value, you would select Entered Value and then type a number into the text field. The Recordset window would then look like Figure 22-3.
Complete the Recordset window by choosing a sort option (if desired) and clicking OK.
You can test the recordset and filter by clicking Test. If you selected anything other than Entered Value from the source menu, a message will prompt you to type in a test value for the source—URL parameter, form variable, and so on.

22.1.4 Comparison Operators for Filters

Dreamweaver provides many different ways to compare information from a database column with information from another source, such as a form, cookie, or simply a value you type into the Recordset window. The type of comparison you choose also depends on the type of data you're comparing: text or numbers.

22.1.4.1 Comparing text values

You'll often want to create recordsets that find database matches to particular words. For example, a recordset could filter a list of products to find only those records whose descriptions contain the word "green," or you could filter a database of clients to search for a record for "Craig McCord." Dreamweaver provides the following types of text comparisons:

Equality. To check whether the information in a column is exactly the same as another value, select the = sign from the comparison menu.
Inequality. To find records that don't match a particular piece of text, select the <> (doesn't match) operator from the menu. You would use this, say, if you wanted to search a database of clothing for items that do not include a certain word (like "winter" in the Season column).
Begins With, Ends With, and Contains. The last three comparison operators are ideal for locating one or more words within text. For example, a database of movies might have a column containing a short review of each movie. To locate reviews that included the words "horrible acting," you could choose the Contains option, which will find any movie that included the phrase "horrible acting" anywhere in its review.
The Begins With and Ends With options are more selective. The former only finds records when the text at the very beginning of a particular record matches; the latter works only when the text appears at the end. You probably won't use these options very often.

The other comparison operators (<, >, <=, >=) aren't very useful for searching text in a database. They're intended for comparing numbers, as described next.

22.1.4.2 Comparing numbers

Filters are particularly useful for numbers: finding products that cost less than $35, albums that were released in 1967, products with more than 3,000 items in stock. If you've taken basic algebra, these options for comparing numbers should be familiar: = (equal to), <> (not equal to), < (less than), > (greater than), <= (less than or equal to), or >= (greater than or equal to).

22.1.5 Getting Comparison Values

By now it should be clear that the Filter option of the Recordset window lets you compare data from one column with some other value. But you're probably wondering where this "some other value" comes from. It depends on which option you selected from the third drop-down menu—the Source menu (see Figure 22-3).

The most straightforward option is the last item in the menu: Entered Value. After selecting it, you simply type the value into the field to the right of the menu. This could be a number, a letter, or one or more words. So, to create a recordset that will find a product whose price is more than $50, you'd select the price column, the > (greater than) comparison symbol, and the Entered Value source option, and then type 50 into the value field.

Unfortunately, this kind of recordset is rather limited. The comparison value you specify (50) is hardwired into the recordset, making it very inflexible. What if a visitor wanted to see products that cost more than $15, $30, or $100? No joy. This recordset is limited to what you, the designer, entered as a value.

A better option would be to create the filter on the fly from information gathered when the page with the recordset is requested by the visitor's Web browser. In this way, you can create very flexible recordsets that are capable of searching databases for a variety of different pieces of information, not just the one value selected by a programmer. (After all, how good a search engine would Yahoo be if the programmers determined what the search criteria were? No matter what you searched for?span class="docEmphasis">Web design, Used cars—it would always find Web sites about Java, Burning Man, and Diet Coke.)

Dreamweaver can also draw a filter value from a form, cookie, or even the URL of a link. The process is always the same: From the filter's comparison value source menu (Figure 22-3), select the source you want, and then type the name of the appropriate source item. For example, if you select Form Variable from the source menu, type the name of the form field in the box to the right.

In most cases, you must depend on an additional Web page to provide the source of these values. For example, a search function on a Web site usually requires more than a single page: One (or more) containing a Search field and a Submit button to send the information, and another that displays the results of the search. In this example, the form on one page sends information (the search terms) to another page (the results page), which uses the form information to search the database. In essence, the words typed into the search form on one page are used to create the recordset on another page.

The two most common ways to pass information from one page to another are forms and URLs. (Three advanced sources—cookies, session variables, and application variables—are discussed in Section 24.2.)

22.1.5.1 Form variables

A form variable is simply the information that a visitor types into a form field (or the value of a selected radio button, menu item, or checkbox). Forms are discussed in depth in Chapter 11, but their use in recordset filters is straightforward.

Create a form page.
It can include a text field, pop-up menu, or some other form element. Make sure you name the form element. For use in a simple recordset filter, you're limited to a single form variable. Using an advanced recordset (see Section 22.1.6), you can use information from more than one form field to filter the data in a recordset.
If you wanted to give your site's visitors a chance to look at differently priced products, for example, you could create a pop-up menu that included the values 10, 50, 100, 500, and so on. People could then choose one of those options to look at products below the selected price. (Also be sure to give the menu a name, such as "price," as described in Section 11.3.1.)
Set the Action property of the form (Section 11.2).
You'll want it to point to the results page.

NOTE

For these steps to work, the form's method must be set to Post in the Property inspector (see Section 11.2). If Get is selected, the form information will appear in the URL when the form is submitted. What's more, form information won't be sent as a form variable. (You can, however, use the Get method in conjunction with the URL parameters option discussed next.)

Open (or create) the results page.
This page will display the results of the recordset that's created using information from the form.
Add a recordset to the page, using the directions in Section 22.1.2.
You'll also create a filter using a form variable.
From the Filter menu, select a database column. Then choose a type of comparison, as described in Section 22.1.4.
All of this is the standard filter-creation routine.

WORKAROUND WORKSHOP
The Default Value for a Filter Source

There's a problem with using a variable source of information for a filter. If the filter requires information from a form or URL parameter, what happens if someone comes to the page without first filling out the form or clicking a link with a URL parameter? In most cases, the recordset will be empty and the page will display no records. You can, however, set a default value for the form variable or URL parameter, so that at least some records will always appear.

Using the steps outlined on these pages, create a basic recordset; include a filter using a form variable or URL parameter. Then click the Advanced button in the Recordset window.

Now you get a more complex view of the recordset. In the Variables, list, there's a single entry: the selected filter source (Form or URL). Click in the Default Value column and change the 1 to something that will match records in your database.

One solution is to type a value that will match all the records in the database. For example, if the recordset is used to find products under a certain price, type a value (price) that's larger than the most expensive product in the database. This way, the recordset will retrieve all items under that price—in other words, all of the products. (This trick also works for the other sources discussed in Section 24.2: cookies, application variables, and session variables.)

One last word of warning. If you switch back to the basic recordset view by clicking the Simple button, Dreamweaver resets the recordset variable to the default value of 1. In other words, if you change the default value in the advanced view, don't switch back to the basic recordset view.

figs/22_sb1.jpg

From the source pop-up menu, select Form Variable. In the box to the right of the source menu, type the name of the form field that contains the value for comparison.
In keeping with the above example, you would type price into the box, since that's the name of the menu on the form page.
Add a sort option, if you like, and click OK to create the recordset.
Remember that this kind of recordset's results depend upon information sent from a form. If a visitor just stumbles across the results page without using a form, the recordset will most likely produce no results. That's why you should link to this kind of page only by using a form's Action property (see Section 11.2).

22.1.5.2 URL parameters

In your Web travels, you've probably encountered URLs that look kind of strange, along the lines of www.nationalexasperater.com/shopping.asp?prodID=34&quant ity=4. Everything up to the ? looks fine, but you're probably wondering what the ?prodID=34&quantity=4 means.

Forms aren't the only way to pass information to a dynamic Web page; URLs can do it, too, thanks to information tidbits called parameters. Dynamic Web sites can read parameters and use them to create a recordset, among other things. (In fact, using the Get method for a form puts the form's information into the URL.)

By using the Recordset window's Filter option, you can use one of these parameters to search a database. To identify a single record in a database, for instance, the URL could contain a number identifying the record's primary key (see Section 21.4.2 for a definition). You'll find an example of this trick in the tutorial in Section 22.8.4.

The steps for using URL parameters to filter recordsets are similar to those for form variables. You need two pages, one with a link containing the URL parameter and another containing the recordset.

It's possible to add a link with a URL parameter on the same page as the recordset. For example, you could have several text links like "Products under $10" and "Products under $100" that link to the same page but have different URL parameters.

22.1.5.3 Creating a link with a URL parameter

Dreamweaver provides several ways to create a link that contains a URL parameter. The simplest way is to highlight the item you wish to turn into a link—usually text or a graphic. Then, in the Property inspector's link box, type the link followed by a ?, a parameter name, an =, and the value (for example: products.asp?category=7).

However, you'll probably find it easier to browse for the file and let Dreamweaver write all the complex stuff. To do this, proceed as follows:

Highlight the item you wish to turn into a link.
In other words, select a graphic or text on the page.
Click the folder icon (browse button) on the Property inspector.
The Select File window appears. (For more on creating links, see Section 4.2)
Browse to and select the page containing the recordset.
This is the page that will display the results of the database search.
Click the Parameters box in the lower-right corner of the Select File window.
The Parameters window appears (see Figure 22-4).

The Parameters window lets you add URL parameters to a link. Recordsets can use these pieces of information to filter a database query, as discussed in Section 22.1.3.

Click in the space below the Name column and type the name of the URL parameter.
Since this will be included in a URL, avoid spaces and any punctuation characters except hyphens and underscores. (Those characters aren't allowed in file names or links on the Web.)
Click in the space below the Value column and type the value for the URL parameter.
This is the value that the filter in the recordset will use to match records in the database.
Unfortunately, here again, including spaces and other punctuation marks in the value will keep the recordset from working correctly. Therefore, you can't type a text value like Bob Jones in order to search for "Bob Jones" in the database. (To do that, you'd have to write some special code yourself.) In this situation, you'd be better off using form variables, because recordsets can read longer text (including punctuation marks) typed into form fields.
Click OK to close the Parameters window. Click OK to close the Select File window and apply the link.

22.1.5.4 Creating the recordset for the Results page

Once you've created the link, you need to create an appropriate recordset for the results page. Here's how:

Open (or create) the results page.
This page will display the results of the recordset created using information from the form.
Add a recordset to the page, using the directions in Section 22.1.2.
You'll also create a filter using a URL parameter.
From the Filter menu, select a database column. Choose a type of comparison, as described in Section 22.1.4. From the source menu, select URL Parameter. In the box to the right of the source menu, type the name of the URL parameter.
This is the name supplied in step 5 of the previous instructions.
Add a sort option, if you like; click OK to create the recordset.

Like form variables, this recordset depends on information included in the URL of a link. If a visitor just stumbles across the results page without using a link with a URL parameter, the recordset will most likely produce no results. Because of this, make sure you only link to this kind of page via a link with a parameter. Otherwise, modify the default value for the URL parameter in the recordset as described in Section 22.1.5.2 .

22.1.6 Advanced Recordsets and SQL

Sometimes you'll need more power than Dreamweaver's simple recordset tool provides. For example, say you're building an online classified ads system. On one page, you want to present various pieces of information: the name of the sale item, its price, who's selling it, and how to contact the seller, for example. In order to store this kind of information, your database has two tables—one for products and one for sellers.

To present all this information, you must simultaneously access both tables. In addition, you need to connect the records of those two tables so that each product is associated with the correct seller—John Smith is selling the Whirligig 2003, for example. There's only one way to create this kind of complex query: using the advanced options of the Recordset window.

To display these options, insert a recordset, using the steps described in Section 22.1.2. Then, in the Recordset window, click the Advanced button. The Advanced Recordset window should appear (see Figure 22-5). (If you see a Simple button, then you're in the advanced options.)

UP TO SPEED
Getting Your Feet Wet in SQL

SQL isn't difficult to pick up. While it is possible to create very complex SQL queries, a basic SQL statement is straightforward. Once you've reached the limits of Dreamweaver's basic recordset, you may want to expand your skills beyond this simple tool.

A great place to start learning how to write SQL statements is in Dreamweaver itself. After you create a simple recordset (see Section 22.1.2), click the Advanced button. The SQL statement for the simple query appears in the SQL box.

This chapter introduces the very basics of SQL. For a more complete introduction, check out SQLCourse.com ( www. sqlcourse.com/ ). Or pick up a book like SQL Queries for Mere Mortals by Michael Hernandez or SQL in a Nutshell by Kevin Kline.

Unfortunately, putting together advanced database queries is not as easy as most other operations in Dreamweaver. The Advanced Recordset window is basically just a way of typing in commands, using a database programming language called SQL (Structured Query Language, pronounced "ess-cue-ell"). SQL is a standard language used by many database servers to access, update, delete, and add information to a database.

To create an advanced recordset, type an SQL statement in the window's SQL box.

The Recordset window's advanced options aren't for the uninitiated. You'll need to have a good grasp of SQL—the standard database program language—to make complex recordsets.

22.1.6.1 SQL: The very basics

SQL lets you communicate with a database by adding new records, deleting records, and creating new tables. In the context of the Advanced Recordset window, you only need to understand how SQL retrieves information. After all, a recordset is just a selection of data pulled from the database.

To make an SQL query (called an SQL statement), you must first specify:

Which columns of data you want to retrieve. For example, product prices, product names, seller name, and seller contact information.
Which tables will supply this data. In the example above, the information is stored in two tables: Ads and Sellers.
How the search should be limited. You might just want products that are less than $10, or whose seller is Zachariah Smith.
The sort order. You could sort items using the Price column to view a list of products from least to most expensive, for example.

Only the first two pieces of information are absolutely necessary. A very basic SQL statement would look like this:

SELECT prodPrice, prodName

FROM products

SELECT is an SQL keyword that specifies columns of data for retrieval; FROM indicates which database table contains it. This statement instructs the database server to look inside the Products table and retrieve the price and name of each product listed. The result is a list of the price and the name of each product in the database.

Of course, you may not always want every record in a table. You may want to limit the search to a select number or items, such as products under $10. The WHERE keyword lets you do just that.

SELECT prodPrice, prodName

FROM products

WHERE prodPrice < 10

Now the SQL statement retrieves only the price and the name of products that cost less than $10. Finally, SQL can sort records into order. In this example, you could also sort all of the results from least to most expensive, like this:

SELECT prodPrice, prodName

FROM products

WHERE prodPrice < 10

ORDER BY prodPrice ASC

The ORDER BY keywords indicate which column should be used to sort the records. Specifying the prodPrice column sorts the items by price. ASC is short for ascending, meaning that the records will appear in low-to-high price order. (DESC sorts records into descending order, Z—A, or high-to-low.) You can even sort by multiple columns. If, for example, you wanted a list of all products sorted by price and then alphabetically by product name, you would simply change the above ORDER BY keyword to read like this:

ORDER BY prodPrice ASC, prodName ASC

In this way, all the products that were the same price (for example, $10) would then be presented in alphabetical order (A—Z).

22.1.6.2 Using the Data Tree view

Although you need to know SQL to use the Recordset window's advanced options, you can get a little help from the data tree in the Database Items list at the bottom of the window (see Figure 22-5). This area of the window functions just like the Databases panel, and lets you view the tables, columns, views, and stored procedures in the database (see Section 21.6.1).

Click the + (arrow) button next to the word Tables heading to make a list of all tables in the database appear. Click the + (arrow) next to a table name to see all of the columns within that table. That's a very helpful technique when you're building an SQL statement, because you may not remember the exact names of every table and column in your database.

To build an SQL statement, you can select a column name and click one of the three buttons—SELECT, WHERE, or ORDER BY. The SQL command and column name then appear in the SQL box.

Suppose, for example, you wanted to create the following SQL statement:

SELECT prodPrice, prodName

FROM products

To build this statement using the data tree, click the + button next to the table named Products, which expands to show a list of all columns. Then click the column named prod- Price and click SELECT. Next, click the prodName column and click SELECT again.

Although these buttons can save you time, they don't check whether the SQL statement is valid. Unless you've got some grasp of SQL, you can easily create a statement that generates errors when run by the test server.

NOTE

Be careful when using multiple database tables that contain the same column name. For example, it's possible to have two tables?span class="docEmphasis">products and sellers, for example—that contain columns with the same name, such as sellerID. Dreamweaver won't make any distinction when inserting these columns using the Database Items tools. To correctly differentiate like-named columns in the Advanced Recordset's SQL box, you must begin the column name with the name of the table, like this: products.sellerID or seller.sellerID.

22.1.6.3 Creating variables for filtering data

Variables let you filter data using information from sources such as forms, URLs, cookies, session variables, and application variables. If you use the filtering option in the basic Recordset window, Dreamweaver creates the variables for you—but in the Advanced Recordset window, you must create them yourself.

To add a variable for use in an SQL query, follow these steps:

In the Recordset window, click the + button above the Variables box (see Figure 22-5). Click in the empty space below the Name column and type a name for the variable.
The name shouldn't include spaces or other punctuation marks.

As with database connections and recordsets, it's a good idea to prefix the name of the variable so you'll be able to more easily identify it in the code. For example, you could begin the variable name with var—varPrice, for instance—just as you'd begin a recordset name with rs (rsProducts, for example).

Press Tab to jump to the Default Value column. Type a default value for the variable.
A default value comes in handy when the form, URL, cookie, session variable, or application variable is empty. The recordset will use the default value to filter the database records.
Press Tab to jump to the Run-Time Value column; type the appropriate code.
The exact code depends on the server model you selected. For example, in ASPspeak, you'd type Request.Form("price") to retrieve the value of a form field named price. To do the same thing in PHP-land, you'd type $_POST['price']. The best way to learn how to create variables is to use Dreamweaver's filter tool in the Recordset window (see instructions in Section 22.1.3) and then switch to the Advanced Recordset window. The proper code for collecting information from forms, URLs, cookies, and so on will appear in the variables Run-Time Value column.

NOTE

Keep in mind that if you add more than one SQL variable in the Advanced Recordset window, you won't be able to switch back to the simple view.

Once you create a variable, you can include it in your SQL statement. Since variables are frequently used to help filter information, you'll often add them to the SQL WHERE keyword. For example, if you create a variable named formPrice that retrieves information from a form, you can add it to the SQL statement like this:

SELECT prodPrice, prodName

FROM products

WHERE prodPrice < formPrice

In this example, whatever information is passed from the form is stored in the formPrice variable and compared to the price stored in the prodPrice column of the database.

22.1.7 Reusing Recordsets

Recordsets are created on a page-by-page basis. In other words, when you create a recordset, it's added only to the current document. If you create another Web page that requires the same recordset, you must add the proper code to the new page. You can do this either by recreating the recordset—a potentially laborious process—or by simply copying the recordset from one page and pasting it into another.

Here's how:

Open the Server Behaviors panel by choosing WindowServer Behaviors.
Ctrl+F9 (-F9) also works. You can also copy and paste from the Bindings Panel.
Right-click (Control-click) the name of the recordset you wish to copy; choose Copy from the contextual menu that appears.
In the Server Behaviors panel, recordsets appear like this: Recordset (rsName), with the name of the recordset inside the parentheses. (If you're using the Bindings panel, this procedure won't work unless you actually select the name before right-clicking.)

Now switch to the document that will receive the pasted recordset. Right-click (Control-click) in the Server Behaviors panel and choose Paste from the contextual menu.

If you need a recordset that's similar to a recordset you've already created—but not identical—you can copy the original recordset, paste it into a new document, and then edit it, following the instructions below.

22.1.8 Editing Recordsets

What if you have to edit a recordset? Maybe you forgot an important column of information when you originally created the recordset, or perhaps you want to modify a recordset you copied from another page. The process is easy: Simply open either the Bindings panel (Ctrl+F10) or Server Behaviors panel (Ctrl+F9) and double-click the name of the recordset you wish to edit.

The Recordset window appears, looking just as it did when you first created the recordset (see Figure 22-2). Make any changes to the recordset, and then click OK.

If you change the name of a recordset while editing it, Dreamweaver displays a message indicating that you need to use Find and Replace to locate and update every instance of the recordset's name. Dreamweaver opens the Find and Replace window for you when you click OK, but it's up to you to make sure the changes are correct.

This is another reason why beginning a recordset with "rs" (rsProducts, for example) is a good idea. If you've named a recordset simply "products," you could end up finding and replacing not only the name of the recordset, but also any other instance where the word "products" appears in the page.

The safest (although slowest) way to change a recordset's name is to recreate it. Of course, that's extra effort? a good argument for making sure you're satisfied with a recordset's name when you first create it.

22.1.9 Deleting Recordsets

If you add a recordset to a page, and later realize that the page isn't using any of the information retrieved by the recordset, you should delete it. Each recordset forces the database server to do some work. Unnecessary recordsets only make your Web pages work harder and more slowly.

You can delete a recordset using either the Bindings or Server Behaviors panel. Just select the name of the recordset in either panel and click the —button at the top of the panel (pressing the Delete key on your keyboard has the same effect).

< Day Day Up >