Data Access  «Prev  Next»

Lesson 8
Objective Use the Recordset Object within Scripts

Use the Recordset Object within Scripts

The recordset object is the primary interface to data. You use it to access and manipulate the data returned from a query. The recordset object represents all the records from a table or query result, but only references a single record at a time.
The recordset object has several properties, methods, and events that are exposed to scripting. You use these to set parameters, such as the SQL query string, execute functions, and respond to actions.
You will only use some of the properties, methods, and events in this course.
You have already used recordset objects in some of the exercises. When a Recordset Design-Time control is inserted into a page, it creates a recordset object and some initialization script[1] to set up some of the object's properties. You can see the script by right-clicking the control and selecting Show Runtime Text. In this lesson you will learn to use the recordset object via script.
The following image and MouseOver shows script from within the source editor of the file Specials. The Textbox DTCs have been replaced with server-side script, and the script is bound to the Recordset DTC to display the data. The purpose of Specials.asp is to display only books that are on special. To do this, we will use script to change the recordset object's default behavior from retrieving all records to retrieving only records who's Status field equals 'S.' In this example, the onbeforeopen event is used to change the default query before the recordset object is opened.

src="images/query-string.gif" width="420" height="549" alt="Apply, Filter, Sort">
  1. This is the onbeforeopen event handler code. The code is executed just before the recordset object is loaded and changes the default SQL query to retrieve only records of books on special. The name BookRecords was assigned to this instance of the recordset object in the Binding data-bound Design Time Controls lesson. This function is executed just before the BookRecords recordset object is loaded.
  2. Declare the variable querystring. It will contain the SQL query.
  3. This statement assigns a SQL query to querystring. This SQL query specifies to return only records with a Status of 'S' (where Status = 'S'), which indicates that the book is on special.
  4. The recordset object's setSQLText method is called and passes querystring as the parameter.
  5. These lines define a standard HTML table and the table's first row.
  6. This line begins a VBScript For … Next loop that will execute once for each column in the database table.
  7. The purpose of the For…Next loop is to display the column names. This line gets the field name and displays it as table data in the HTML table row.
  8. Now that the column headings are displayed, it's time to display one row in the HTML table for each row in the database table. This line uses the recordset control's end of file, EOF, property to execute a Do…While loop until the end of the recordset is reached
  9. This For…Next loop also executes one time for each column in the database table, this time to display the column data
  10. This line gets the database column value from the recordset and displays it in the HTML table.
  11. After a row is displayed, the recordset object's MoveNext method is called to move to the next row. The Loop statement indicates the end of the Do…While loop body.
  12. This statement closes the recordset after all the rows are displayed.

Using onbeforeopen in SQL-Server
src="images/book-specials.gif" width="516" height="182" alt="book specials">
Specials_3


Click the View Image button to see the new browser display.
The SQL query in the BookRecords_onbeforeopen event handler is explicitly assigned. You can also build the query string dynamically. For example, you could pass the Status value from another page and capture it in Specials.asp using the Request object, and then use routine string concatenation to build the query string. In the next lesson, you will see how error-handling code is added to an ASP page.
[1]initialization script: Commonly used to set initial values of variables, properties, and so on. Initialization code can be executed as soon as the Web page is loaded to make global settings, or in a function or procedure to make local settings.