Limit Query Results

Overview

In an Internet environment, it is often desirable to limit the amount of information that a database query returns to a client browser. This example demonstrates how a script, using ASP and ADO, can limit the number of rows returned.

Code Tour

The sample first creates an instance of the Connection object, and opens the OLE DB connection with this object's Open method. CreateObject is used again to instantiate an empty Recordset object. The ActiveConnection property of the new Recordset object is set to point at the open OLE DB connection, an SQL source string is assigned, and cursor type specified. The key to limiting the results lies with the Recordset object's PageSize property. For this example, the value is set to 10, which indicates that ADO is to return at most 10 records. Finally, the Open method is called, and ADO searches for the first 10 records that fulfill the SQL search string.

When ADO has returned and placed the results of the search into the Recordset object, the script loops through the page, displaying all fields of each record in a table. The script then performs the typical housecleaning operations, closing both the recordset and the connection.

It is important to realize that if the SQL query had returned more than 10 records, this script would not display them. Instead, the extra records would be deposited on one or more additional, logical pages. The property PageCount indicates how many logical pages of data were returned.

Important   OLE DB must be properly configured on the server for this sample to run properly.

Location

The VBScript and JScript versions of this script are available in the IIS samples directory, at ...\asp\database\LimitRows_VBScript.asp and ...\asp\database\LimitRows_JScript.asp.