Understanding recordsets

When using a database as a content source for a dynamic web page, you must first create a recordset in which to store the retrieved data. Recordsets serve as an intermediary between the database storing the content and the application server generating the page. Recordsets consist of the data returned by a database query, and are temporarily stored in the application server’s memory for faster data retrieval. The server discards the recordset when it is no longer needed.

The recordset itself is a collection of data retrieved from a specified database. It can include an entire database table, or a subset of the table’s rows and columns. These rows and columns are retrieved by means of a database query that is defined in the recordset. Database queries are written in Structured Query Language (SQL), a simple language that allows you to retrieve, add, and delete data to and from a database. The SQL builder included with Dreamweaver lets you create simple queries without having to understand SQL. However, if you want to create complex SQL queries, you will need to learn SQL, and manually write SQL statements that you enter into Dreamweaver.

For more information on using databases and SQL, see Beginner’s Guide to Databases and SQL Primer.

If you will be writing SQL for use with ASP.NET, there are some conditions specific to ASP.NET that you should be aware of. To learn about these conditions, see Writing SQL for ASP.NET.

Before you define a recordset for use with Dreamweaver, you must create a connection to a database and--if no data exists yet--enter data into the database. If you have not yet defined a database connection for your site, refer to the database connection chapter for the server technology you are developing for, and follow the instructions on creating a database connection.

Database connections for all Dreamweaver supported server technologies are described in the following chapters:

• Database Connections for ColdFusion Developers
• Database Connections for ASP Developers
• Database Connections for ASP.NET Developers
• Database Connections for JSP Developers
• Database Connections for PHP Developers

Related topics

• Defining a recordset

Writing SQL for ASP.NET

When writing SQL statements in the Advanced DataSet dialog box, there are conditions specific to ASP.NET that you must be aware of. These conditions are described in the next sections.

Parameters

The syntax you use to reference parameters varies depending on the database connection in use (for example, OLE DB or Microsoft SQL Server).

OLE DB

When connecting to a database using OLE DB, parameters must be referenced using a question mark (?). For example:

SELECT * FROM Employees WHERE HireDate > ?

Microsoft SQL Server

When connecting to Microsoft SQL Server using the Managed Data Provider for SQL Server supplied with the .NET Framework, all parameters must be named. For example:

SELECT * FROM Employees WHERE HireDate > @hireDate

Inserting code within SQL statements

When inserting code within SQL statements written for ASP.NET, you must enclose all strings in quotes (" "), and enclose the code in parentheses ( ).

SELECT * FROM Employees WHERE HireDate > "+ (Request.queryString("hireDate"))

Related topics:

• Database Connections for ASP.NET Developers.