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.

Recordset object properties, methods, and Events

The recordset object has several properties, methods, and events that are exposed to scripting. The following tables provide a brief description of each.
PropertyPurposeSyntax
absolutePositionspecifies the absolute position of the current recordrecordset.absolutePosition[=index]
BOFindicates whether the cursor is positioned before the first recordrecordset.BOF
EOFindicates whether the cursor is at the end of the recordsetrecordset.EOF
fieldsspecifies an instance of the Fields objectrecordset.fields
idreturns a unique identifier for the objectrecordset.id
maintainStatespecifies whether object state is maintained during server processingrecordset.maintainState[=boolean]
namereturns the name of the objectrecordset.name

Method Purpose Syntax
addRecord creates a new record in the recordset recordset.addRecord()
advise registers an object to be notified when a specified event occurs and to call a particular function recordset.advise(event, function)
cancelUpdate cancels any changes being made to the current record recordset.cancelUpdate()
close closes the recordset object recordset.close()
deleteRecord deletes the current record recordset.delete()
getBookmark returns a bookmark for the current record recordset.getBookmark()
getConnectString returns a string or object that specifies the data connection recordset.getConnectString()
getCount returns the number of records in the recordset recordset.getCount()
getDHTMLDataSourceID returns a string from the DHTML data source's ID recordset.getDHTMLDataSourceID()
getParameter returns a parameter from a stored procedure or parameterized query recordset.getParameter(index)
getRecordSource returns the ADO recordset object recordset.getRecordSource()
getSQLText returns the SQL statement that queries the database recordset.getSQLTextg()
isOpen returns whether the recordset object is open recordset.isOpen()
move moves the cursor the specified number of positions recordset.move(records)
moveAbsolute moves the cursor to the specified record recordset.move(index)
moveFirst moves to the first record in the recordset recordset.moveFirst()
moveLast moves to the last record in the recordset recordset.moveLast()
moveNext moves to the next record in the recordset recordset.moveNext()
open opens a recordset object recordset.Open()
requery requerys the database recordset.requery()
setBookmark sets a bookmark to a particular record recordset.Bookmark(bookmark)
setParameter sets a parameter for a parameterized query or stored procedure recordset.setParameter.(index, parameter)
setRecordSource sets connection properties for opening a recordset object recordset.setRecordSource(ADO|conn,SQL)
setSQLText sets the SQL statement to query the database recordset.setSQLText(sql)
unadvise cancels the registration of an object recordset.unadvise(event, id)
updateRecord updates the current record recordset.updateRecord()

EventOccursSyntax
onafterupdateafter a record is updatedrecordset_onafterupdate
onbeforeopenjust before a recordset object is openedrecordset_onbeforeupdate
onbeforeupdatejust before a record is updatedrecordset_onbeforeupdate
ondatasetchangedwhen a change is made to the recordset objectrecordset_ondatasetchanged
ondatasetcompletewhen the server finishes downloading the recordsetrecordset_ondatasetsomplete
onrowenterwhen the cursor moves to another recordrecordset_onrowenter
onrowexitwhen the cursor moves to another recordrecordset.onrowexit

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.

Example of Legacy VBScript

Scripting with Record Sets
1) VBScript Code 
<%
Sub BookRecords_onbeforereopen()
    Dim querystring
    querystring = "select * from BookTable where Status = 'S'"
    BookRecords.setSQLText(querystring)
End Sub
%>

<HEAD>
</HEAD>
<BODY>
<H2 ALIGN=CENTER>This Week's Book Specials</H2>
<TABLE ALIGN=CENTER BORDER=1>
<TR>
v% For i = 0 to BookRecords.fields.getCount() - 1 %>
    <TD> <%= BookRecords.fields.getName(i) %></TD>
<% Next %>
</TR>
<%
Do While Not BookRecords.EOF
%>
<TR>
    <% For i = 0 to BookRecords.fields.getCount() - 1 %>
        <TD VALIGN=TOP><%= BookRecords.fields.getValue(i) %></TD>
    <% Next %>
</TR>
<%
    BookRecords.moveNext
Loop
BookRecords.close
%>
</TABLE>
</BODY>

2) Relevant Text
  • Script Objective:
    • The script dynamically queries a database table (BookTable) for rows where the Status column is 'S' (indicating "specials").
    • It uses the result set (BookRecords) to generate an HTML table displaying the data.

Key Components:
  1. VBScript Section:
    • Defines a subroutine BookRecords_onbeforereopen() that sets a SQL query (select * from BookTable where Status = 'S') to the BookRecords object using the setSQLText method.
  2. HTML Section:
    • Displays a title ("This Week's Book Specials") and renders the data in an HTML table.
    • Dynamically creates table headers using field names from the BookRecords object.
    • Iterates through each row in the recordset (BookRecords) to populate the table with field values.
  3. Logic for Handling Recordset:
    • Uses BookRecords.fields.getName(i) to retrieve column names for the table headers.
    • Uses BookRecords.fields.getValue(i) to retrieve field values for each row in the table.
    • Employs BookRecords.moveNext to iterate through the rows and closes the recordset after processing all data.

Database Connection:
  • Shown in the visual snippet:
    • Connection: vbookconn
    • Database Object: Tables
    • Object Name: BookTable

Output: An HTML table is dynamically generated, displaying headers and rows for books with the status `'S'`.
This script combines VBScript for database interaction with dynamic HTML generation, allowing for a real-time display of filtered data from a database.
  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.

Books Special

book specials
Specials_3


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.

SEMrush Software