Whilst the examples in the previous sections clearly have their uses, they still can use some improvement, on the following performance grounds:

• they all display the entire set of records from a data source.
• while in the first example we can define headers and data cells only for the fields we intend to display, the Recordset still contains all of a table's fields, even those we have no need for.

This can impose a major drain on system resources, especially when working with larger databases in the megabytes file size.

Alternatively, the record set can be defined to contain only the information we need to work on. Using Structured Query Language ( SQL ) statements, we can create a custom query to specify only a selected set of records and fields to view.

Here we explore database queries in brief, made using both the Connection and Recordset objects to execute an SQL SELECT command.

In this example, we use the SELECT command to retrieve a specific set of information based on query constraints. The query also contains an SQL WHERE clause, to narrow down the query to a specific criterion.

strQuery="SELECT ProductType, ProductName, ProductDescription, 
   UnitPrice FROM Products WHERE UnitPrice >=100"

NOTE: An SQL statement must be written as a single line without any line breaks; the examples shown here are done so only for readability.

The query selects only four fields from the Products table, and the WHERE clause limits the query to only those records whose Unit Price is more than or equal to $100.

<% ' specify the driver
strProvider="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=dbtutor.mdb"

' establish the connection
Set dbConn=Server.CreateObject( "ADODB.Connection" )
dbConn.Open strProvider

' define the query
strQuery="SELECT ProductType, ProductName, ProductDescription, 
   UnitPrice FROM Products WHERE UnitPrice >=100"

' create and open the record set using the established connection
Set rsProds=Server.CreateObject( "ADODB.Recordset" )
rsProds.Open strQuery, dbConn, , , adCmdText
%>

In that example, the Connection object establishes the database connection and the Recordset object uses the SAME connection to retrieve results from the database.

This method is useful when opening multiple record sets with one connection, and when you need to precisely configure the way in which the link with the data source is established.

For example, if you need to specify the time delay before a connection attempt aborts, you have to use the Connection object to set this property.

However, if you just want to establish a connection using ADO's default connection properties, you could use the Recordset object's Open method to establish the link.

<% ' specify the driver
strProvider="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=dbtutor.mdb"

' define the query
strQuery="SELECT ProductType, ProductName, ProductDescription, 
   UnitPrice FROM Products WHERE UnitPrice >=100"

' create and open the record set
Set rsProds=Server.CreateObject( "ADODB.Recordset" )
rsProds.Open strQuery, strProvider, , , adCmdText
%>

The samples above are useful when the conditions for the query are known beforehand.

Selecting Records from a Database Run Sample | View Source

Now let's explore a method that can retrieve specific data sets based on a variable query constraint. This is extremely useful in cases where the query condition is initially unknown.

The sample below demonstrates dynamic queries in action, where the user decides on which records to display.

This is made possible by providing the variable to the SQL query string that retrieves the data set.

strQuery="SELECT ProductType, ProductName, ProductDescription, 
   UnitPrice FROM Products 
   WHERE ProductType='" & strType & "'"

in which strType is the value returned from user input.

The code also illustrates use of the DISTINCT clause in another SQL statement, to display only unique values ( no duplicates ) from a given field

strQuery="SELECT DISTINCT ProductType FROM Products"

the results of which we use in a selectable list-box to accept the user input.

Applying a Filter to a Recordset Run Sample | View Source

There is one other way to dynamically retrieve a specified set of records.

The ADO Recordset.Filter property can be used to selectively "screen out" records in a Recordset object.

The code is basically identical, except that the method applies a "filter condition" to the Recordset to retrieve the results,

rsProds.Filter="ProductType='" & strType & "'"

instead of issuing a query. For the Filter to work, though, the Recordset must already be open, and must of course include the field used for the filter condition.

When deciding whether to use queries or filters to dynamically retrieve subsets of data, you should carefully consider the actual application scenario.

The Filter property applies only to an open data source, which can mean having the entire set of records ( and fields ) to be initially open, especially in cases where your filter condition may involve fields that are initially unknown.

Personally, on performance grounds, I prefer to use dynamic queries instead of filters on the larger data sources.