Setting the advanced Recordset dialog box (ASP.NET)

The purpose of this dialog box is to define a DataSet as a source of dynamic content by writing a custom SQL statement, or by creating a SQL statement using the Database Items tree.

To set the advanced Recordset dialog box options for ASP.NET:

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

   A common practice is to add the prefix ds to DataSet names to distinguish them from other object names in your code. For example: dsPressRelease

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

2. Select a connection from the Connection pop-up menu.
3. Enter a SQL statement in the SQL text area or use the Database Items tree at the bottom of the dialog box to build a SQL statement from the chosen database tables.

   For more information about writing SQL statements for ASP.NET see the following:

   • Creating SQL queries using the Database Items tree
   • Creating SQL queries using the Database Items tree
   • SQL Primer

   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--a column in a table, for example, or a stored procedure in the database.
   • 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.

   You can also use a predefined SQL statement in a stored procedure by selecting the stored procedure from the Database Items tree and clicking the Procedure button. Dreamweaver automatically fills in the SQL and Parameters areas.

4. You can specify a page to redirect users to if the DataSet query should for some reason fail. For example, if the database were to become unavailable during a query and failed to return the DataSet for the page, you could display an error page with a link to the site’s home page.
5. If the 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 value
   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 Obtaining Data for Your Page.

6. 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 DataSet. Each row contains a record and each column represents a field in that record. Click OK to clear the DataSet.

7. If satisfied with your work, click OK.

Related topics

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