Dreamweaver.MX.1002004.The.Missing.Manual [Electronic resources] نسخه متنی

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 recordsetalso called a database queryis 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 seconda powerful benefit, indeed.

NOTE

ASP.NET noteDreamweaver 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 (AZ, 010, earliest to
latest) or Descending (ZA, 100, 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 recordthe "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 optionsthree 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 sourceURL 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 menuthe 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 forWeb design, Used
carsit 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 sourcescookies, session variables, and application
variablesare 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.

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 pricein 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.

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 linkusually 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 tablesone 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 sellerJohn 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.)

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 SQLthe standard
database program
languageto 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, ZA, 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 (AZ).

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
buttonsSELECT, 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 tablesproducts and sellers, for examplethat 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 youbut 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 varvarPrice, for instancejust 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 recordseta potentially laborious processor 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 createdbut not identicalyou 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).