Setting the advanced Recordset dialog box options (ColdFusion)

The advanced Recordset dialog box lets you write custom SQL queries, or use the Database Items tree to author SQL queries using a point-and-click interface.

To complete the dialog box:

1. In the Name text box, enter a name for the recordset.

   A common practice is to add the prefix rs to recordset names to distinguish them from other object names in your code. For example: rsPressReleases

   Note that recordset names can only contain letters, numbers, and the underscore character (_). You cannot use special characters or spaces.

   If you’re defining a recordset for a ColdFusion component (that is, if a CFC file is currently open in Dreamweaver), select an existing CFC function from the Function pop-up menu, or click the New Function button to create a new function.

   NOTE

   The Function pop-up menu is only available if a CFC file is the current document and you have access to a computer running ColdFusion MX 7 or better. For more information, see Enabling the ColdFusion enhancements.

   The recordset will be defined in the function. For more information, see Defining a recordset in a ColdFusion component.

2. Select a data source from the Data Source pop-up menu.

   If no data source appears in the pop-up menu list, you will need to first create a ColdFusion data source. For more information, see Database Connections for ColdFusion Developers.

3. In the Username and Password text boxes, enter the user name and password for the ColdFusion application server if required.

   Data sources in ColdFusion may require a user name and password to access them. If you do not have the user name and password to access a data source in ColdFusion, contact your organization’s ColdFusion administrator.

4. Enter a SQL statement in the SQL text area or use the graphical Database Items tree at the bottom of the dialog box to build a SQL statement from the chosen recordset

   For more information, see the SQL Primer and Creating SQL queries using the Database Items tree

If you want to use the Database Items tree to build the SQL statement:

• Ensure the SQL text area is blank.
• Expand the branches of the tree until you find the database object you need--for example, a column in a table.
• Select the database object and click one of the buttons on the right side of the tree.

  For example, if you select a table column, the available buttons are Select, Where, and Order By. Click one of the buttons to add the associated clause to your SQL statement.

1. If your SQL statement contains parameters, define their values in the Parameters area by clicking the Plus (+) button and entering the parameter’s name and default value (the value the parameter should take if no runtime value is returned).

   If the SQL statement contains parameters, make sure the Default Value column of the Parameters box contains valid test values.

   The Page Parameters allow you to provide default values for runtime value references in the SQL you write. For example, the following SQL statement selects an employee record based on the value of the employee’s ID. You can assign a default value to this parameter, ensuring that a runtime value is always returned. In this example, FormFieldName refers to a form field in which the user enters their employee ID:

   SELECT * FROM Employees WHERE EmpID = + (Request.Form(#FormFieldName#))
   

   The Add Page Parameters dialog box would contain a name/value paring similar to:

   Name	Default Values
   FormFieldName	0001

   The runtime value is usually a URL or form parameter entered by a user in an HTML form field. For more information on URL and form parameters, see Retrieving form and URL parameters.

2. Click Test to connect to the database and create an instance of the recordset.

   If the SQL statement contains runtime references, make sure the Default Value column of the Page Parameters field contains valid test values before clicking Test.

   If successful, a table appears displaying the data in your recordset. Each row contains a record and each column represents a field in that record. Click OK to clear the recordset.

3. If satisfied with your work, click OK.

Related topics

• Database Connections for ColdFusion Developers
• Defining a recordset
• Beginner’s Guide to Databases
• SQL Primer