The Recordset Object
As we have seen in previous chapters, you can use the Recordset design-time control (DTC) at design time to place a Recordset object onto your Web pages. After you have placed the Recordset DTC on your page, you can script the object in whatever manner necessary. The Recordset object has a rich set of properties, methods, and events for you to take advantage of in your applications.
Recordset Properties
The Recordset object has seven properties, as listed in Table 14-1.
Table 14-1. Recordset object properties.
| Property | Description |
|---|---|
| absolutePosition | Absolute position of the current record in the Recordset object |
| BOF | Cursor is positioned before the first record in the Recordset object |
| EOF | Cursor is positioned after the last record in the Recordset object |
| fields | Enables you to return names and values of fields that are related to the Recordset object's fields |
| id | Recordset object's ID |
| maintainState | Maintains the state of the Recordset object during server processing |
| name | Recordset object's name |
absolutePosition
The absolutePosition property specifies the absolute position of the current record in the Recordset object. The syntax for the absolutePosition property is as follows,
Recordset.absolutePosition
where Recordset is the name of your Recordset script object. The index returned is 1-based, so the first record is 1, the second record is 2, and so on. This can be useful for keeping track of the cursor location as the recordset is traversed. For example, you could use this property to print out the row number on a Web page, as shown here:
Row <% =rsAccountCode.absolutePosition %> of <% =rsAccountCode.getCount() %>
BOF and EOF
The BOF and EOF properties indicate when you are at the beginning and the end of the recordset data. These properties are quite useful when you are iterating through the recordset and displaying the results on screen. By checking the EOF property, you can determine when to end your loop, as in the following example:
Do While not MyRecordset.EOF Response.write MyRecordset.fields.getValue("myfield") MyRecordset.moveNext Loop A more elaborate example using the <TABLE> syntax for formatting the resulting output might look like this:
<TABLE BORDER=1> <TR> <TH>Account Id</TH> <TH>Account Description</TH> </TR> <% Do While NOT rsAccountCode.EOF Response.Write "<TR><TD>" + _ CStr(rsAccountCode.fields.getValue("account_id")) + _ "</TD>" Response.Write "<TD>" + _ rsAccountCode.fields.getValue("account_description") + _ "</TD></TR>" rsAccountCode.MoveNext Loop %> </TABLE> Note that the EOF property tells you when the cursor is positioned after the last record in the recordset and the BOF property tells you when the cursor is positioned before the first record in the recordset. Both of these properties are read-only and available only at run time.
fields
The fields property of the Recordset object is actually an object in itself. It contains the fields collection, which enables you to return names and values of fields that are related to the Recordset object's fields.
The fields object has four methods: getCount, getName, getValue, and setValue, as shown in Table 14-2.
Table 14-2. Methods for the fields object.
| Method | Description |
|---|---|
| getCount | Returns the number of items in the object |
| getName | Returns the name of a field from the fields collection |
| getValue | Returns a value from an object |
| setValue | Sets a value of an object |
You can use getCount to determine the number of columns in the Recordset object, as in the following example:
Number of columns = <% =rsAccountCode.fields.getCount() %>
You can use getName to determine the column name for each column in the Recordset object, as shown here:
<% For i = 0 to rsAccountCode.fields.getCount() - 1 Response.Write "Field " + Cstr(i) + " = " + _ rsAccountCode.fields.getName(i) + "<BR>" Next %>
Notice that when you use the getName method the index is zero-based. Therefore, getName(0) returns the column name for the first column, getName(1) returns the column name for the second column, and so on.
You can use getValue to determine the values of the data in the Recordset object, as in the following example:
Account Id = <% =rsAccountCode.fields.getValue("account_id") %> This gets the value for the account_id column in the currently selected row. In addition to using the actual field name as the parameter for the getValue method, you can also use an index number. The index number is zero-based and indicates which column you are interested in. Assuming the account_id column is the first column in the Recordset object, the following code produces the same result as above by using an index instead of the column name.
Account Id = <% =rsAccountCode.fields.getValue(0) %>
You can use setValue to update the values of the data in the Recordset object, as in the following example:
rsAccountCode.fields.setValue "account_id", 1
This example updates the value of the account_id column to 1 for the currently selected record in the Recordset object. Changes are applied to the database directly using this method, eliminating the need to call the updateRecord method. When making these types of updates, be sure to check for errors arising from the database update because of primary or foreign key constraints.
id and name
The id and name properties specify a unique identifier and the name of the Recordset object. These are often identical. The name is taken from the name given to the Recordset object on the General tab of the Recordset Properties dialog box. By default this name is Recordset#, where # is a sequential number. Both the id and name properties are read-only at run time.
maintainState
The maintainState property specifies whether the object state is maintained through server processing. The object state default setting is set to True. This means that the object maintains its properties and values during server processing. The object state can be turned off using the following syntax,
ObjRS.maintainState = False
where ObjRS is the name of the Recordset object.
Recordset Object Methods
Table 14-3 lists the Recordset object's many methods.
Table 14-3. Recordset object methods.
| Method | Description |
|---|---|
| addRecord | Creates a new record in the Recordset object |
| advise | Registers an object to be notified and a function to be called when a specific event occurs |
| cancelUpdate | Cancels the changes being made to the current record |
| close | Closes a Recordset object |
| deleteRecord | Deletes the current record from the Recordset object |
| getBookmark | Returns a bookmark for the current record |
| getConnectString | Returns a text string or object that determines the data connection |
| getCount | Returns the number of items in the Recordset object |
| getDHTMLDataSourceID | Returns a text string from the ID of the DHTML data source; available only in client script |
| getParameter | Gets a parameter from a stored procedure or parameterized query |
| getRecordSource | Returns the ActiveX Data Objects (ADO) Recordset object |
| getSQLText | Returns the SQL statement that queries the database for the Recordset object |
| isOpen | Returns a Boolean value that indicates whether a Recordset object is open |
| move | Moves the cursor, relative to its current position, within a Recordset object |
| moveAbsolute | Moves the cursor to a specific index within a Recordset object |
| moveFirst | Moves the cursor to the first record of the Recordset object |
| moveLast | Moves the cursor to the last record of the Recordset object |
| moveNext | Moves the cursor forward by one record within the Recordset object |
| movePrevious | Moves the cursor backward by one record within the Recordset object |
| open | Opens a Recordset object |
| requery | Refreshes the current recordset by requerying the database |
| setBookmark | Sets the bookmark for pointing to a particular record |
| setParameter | Sets a parameter for a stored procedure or parameterized query that is referenced by the Recordset object |
| setRecordSource | Sets the connection properties for opening a Recordset object |
| setSQLText | Sets the SQL statement that is used to query the database for the recordset |
| unadvise | Cancels the registration of an object that was registered by the advise method |
| updateRecord | Updates the Recordset object with changes to the current record |
addRecord, deleteRecord, updateRecord, and cancelUpdate
The addRecord, deleteRecord, updateRecord, and cancelUpdate methods allow you to modify the Recordset object or cancel the update. The addRecord method adds a new record to the Recordset object, the deleteRecord method deletes a record, and the updateRecord updates the Recordset object with updates to the current record. Updates have to be made on a record-by-record basis.
The cancelUpdate method cancels the changes that are currently being made to the current record. This method is typically called from a cancel button that the user can click on to undo any pending changes to the data. The cancelUpdate method will replace the values on the form with their previous values prior to the user's changes.
advise and unadvise
The advise method allows you to register an object to be notified and a function to be called when a specific event occurs. The syntax for the advise method is as follows:
[id = ]object.advise(strEvent, CallFunction)
Here, object can be any script object, not just a Recordset object; id is an identifier that can be used by the unadvise method to unregister the object. The strEvent event causes the object to be notified; CallFunction is the function that the object should call when the event occurs.
The advise method is needed only for events that are not implicit to the particular object in question.
The unadvise method cancels the registration of the object with the particular event. The syntax is as follows:
object.unadvise(strEvent, id)
close, open, and isOpen
The close, open, and isOpen methods are useful when you want to use the same recordset but make a few programmatic modifications. For example, you might want to allow an end user to build his or her own queries and then view the results. After the results have been displayed, you might want to give the user the ability to create a new, different query using the same recordset behind the scenes. To do this, you'll need to close the recordset, modify the SQL statement, and then reopen the recordset. The following code shows an example:
If rsQuery.isOpen() Then rsQuery.close() … // Build your custom SQL statement here. … rsQuery.setSQLText(strSQL) rsQuery.open() End If
getBookmark and setBookmark
The getBookmark and setBookmark methods allow you to set a bookmark for the current record in the recordset and then to return to that specific record even if the recordset has been closed and then reopened. The bookmarks also work if you have added or deleted records from the recordset between the time you get the bookmark and the time you set the bookmark.
The syntax for using the getBookmark method is as follows,
strBookmark = Recordset.getBookmark()
where strBookmark is a string representing the value of the bookmark.
The syntax for using the setBookmark method is shown here:
Recordset.setBookmark(strBookmark)
You might use these methods in your applications when you want to add bookmarking capability to an online catalog. This way you can allow your users to bookmark an item of interest and then to return to it later on. To do this, you would call the getBookmark and setBookmark methods from a couple of buttons that you'd place on screen. The following code shows an example:
<SCRIPT ID=serverEventHandlersVBS LANGUAGE=vbscript RUNAT=Server> Sub getBookmark_onclick() Session("tmpBookmark") = rsCustomer.getBookmark() Response.Write Session("tmpBookmark") End Sub Sub setBookmark_onclick() rsCustomer.setBookmark(Session("tmpBookmark")) End Sub </SCRIPT> In this example, the scripting platform is server-side. The Web page for this example contains two buttons, as shown in Figure 14-1. The Bookmark This Record button is used to create the bookmark; the Return To Bookmark button is used to return to the bookmark. Since the bookmark must be persisted between calls to the ASP Web page, it is stored in a Session object variable. The code for this example is contained on the CD-ROM in the DBSamples Web project in a file named Bookmark.asp. To illustrate the actual text contained in a bookmark, the onclick event of the getBookmark button prints out the value of the bookmark. You can see from Figure 14-1 that the bookmark string contains the absolute number of the row plus the primary key value for the Recordset object.
Figure 14-1. Sample Web page with bookmarking functionality.
When the user creates the bookmark and then clicks the Return To Bookmark button, the ASP Web page reloads and displays the bookmarked record.
getConnectString
The getConnectString method returns a text string that shows the data connection string for the Recordset object. The following code shows an example:
Connection String = <% =rsAccountCode.getConnectString() %>
A typical connection string output from the above statement would be:
Connection String = Provider=MSDASQL.1;User ID=admin; Connect Timeout=15;Extended Properties="DBQ=C:\database\bank.mdb; DefaultDir=C:\database;Driver={Microsoft Access Driver (*.mdb)};DriverId=25;FIL=MS Access;ImplicitCommitSync=Yes; MaxBufferSize=512;MaxScanRows=8;PageTimeout=5; SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;"; Locale Identifier=1033 getCount
The getCount method can be used to determine the number of records present in the Recordset object, as shown in the following example:
Number of records = <% =rsAccountCode.getCount() %>
This is useful if you wish to inform the user of the total record count—perhaps at the bottom of a Web page that shows 10 records at a time. The getCount method can be applied to other scripting objects, such as list boxes, as well as to Recordset objects.
getDHTMLDataSourceID
The getDHTMLDataSourceID method returns a text string that represents the ID of the DHTML data source. This method is available only in client script. The method must be placed in client side, and the Recordset object must be set for the client (IE 4.0 DHTML) scripting platform.
<SCRIPT ID=clientEventHandlersVB LANGUAGE=VBSCRIPT> <!-- Sub rsAccountCode_ondatasetcomplete() MsgBox(rsAccountCode.getDHTMLDataSourceID()) End Sub //--> </SCRIPT>
The example above would return an ID such as rsAccountCode_RDS. This ID can be used to bind the Recordset object with intrinsic HTML.
getParameter and setParameter
The getParameter method gets a parameter from a stored procedure or a parameterized query. The syntax is as follows:
Recordset.getParameter(n)
Here, n—which is zero-based—is the index that specifies the particular parameter to return.
The setParameter method sets a parameter for a stored procedure or a parameterized query. The syntax is as follows:
Recordset.setParameter(nIndex, strParameter)
Here, nIndex is the zero-based parameter number to set, and strParameter is a string value that you want to assign to the parameter. You could use the setParameter method to pass a parameter to a Recordset object, as follows:
<% rsAccountCode.setParameter 0, 4 rsAccountCode.open() %>
Notice that the Automatically Open The Recordset check box in the Implementation tab of the Recordset Properties dialog box must not be set, as shown in Figure 14-2. This is because the setParameter method must be called prior to opening the recordset. In the sample code listed above, a parameter value of 4 is passed into the first parameter of the Recordset object. In the following example, the Recordset object is actually a parameterized query:
SELECT account_id, account_description FROM Account_Code WHERE (account_id = ?) ORDER BY account_id
Figure 14-2. The Implementation tab of the Recordset Properties dialog box showing the Automatically Open The Recordset check box.
Note
If the Recordset object represents a stored procedure, as opposed to a parameterized query, the 0 index refers to the return value from the stored procedure and the 1 index and above refer to the actual parameters that are passed to the stored procedure.
getRecordSource and setRecordSource
The getRecordSource method returns the ADO Recordset object. This object can then be used to access properties and methods supported by ADO that are not exposed in the Recordset script object.
The setRecordSource method sets the connection properties for opening a Recordset object. You can pass the setRecordSource method either an ADO Recordset object or a data connection string plus a SQL statement.
An interesting application of the setRecordSource method is to assign a recordset returned from a middle-tier COM component to a Recordset script object within your Web page. This way you can pass rich data types such as Recordset objects between two tiers of your Web applications, and you don't have to worry, for example, about programmatically parsing through strings with delimiters to separate variables. The following code shows an example of how you would take a recordset returned from a server-side component and load it into a Recordset script object within your Web page:
Sub rsData_onbeforeopen() Set obj = Server.CreateObject("COMObject.Class") rsData.setRecordSource( obj.recordsetfunction ) End Sub In this example, VBScript running on the server side within an ASP Web page creates an instance of a server-side component named COMObject.Class and then executes its recordsetfunction method. The recordset returned from the function is assigned to the rsData recordset using the setRecordSource method.
getSQLText and setSQLText
The getSQLText and setSQLText methods are useful if you want to display the SQL statement to the user or if you wish to adjust the SQL statement. The following example shows how to determine the SQL statement and present it to the user:
SQL = <% =rsAccountCode.getSQLText() %>
Note
If you use getSQLText to get the SQL statement for a Recordset object containing a parameterized query, you will get the placeholder for the parameter along with the SQL statement, not the actual value of the parameter. This is true even if you have already used the setParameter method to specify the parameter. To get the parameter value, use the getParameter method.
To set the SQL statement for the Recordset object, use the setSQLText method and pass it the relevant SQL statement, as in the following example:
Recordset.setSQLText("select * from account_code order by account_id") The setSQLText method can be used for custom queries, as mentioned in the earlier section on the open and close methods. The following code shows an example:
Sub Search_onclick() rsSearch.close() sql = "SELECT * FROM Customer WHERE first_name like '%" + _ txtFirst.value + "%' AND last_name like '%" + _ txtLast.value + "%' AND city like '%" + _ txtCity.value + "%' ORDER BY " + _ txtOrder.getValue(txtOrder.selectedIndex) rsSearch.setSQLText(sql) rsSearch.open() End Sub
In this example, the SQL statement is built by using the values that the end user has entered into text boxes for the search criteria: first name, last name, and city. The SQL statement also includes an ORDER BY clause that is determined by the end user's selection from a drop-down list box named txtOrder. The complete Web page for this sample search is on the companion CD-ROM under the DBSamples Web project. It includes a Recordset object, rsSearch, which takes the custom SQL statement, a search button for building the query and performing the search, and a Grid DTC for displaying the output results. Figure 14-3 shows how this page—named setSQLText.asp—appears within Visual InterDev 6.0.
Figure 14-3. setSQLText.asp is a sample Web page that uses the setSQLText method to provide a custom query capability.
Navigation methods
Six methods can be used for Recordset navigation: move, moveAbsolute, moveFirst, moveLast, moveNext, and movePrevious. The latter four are by far the most common and are fairly self-explanatory. The move method moves the cursor a certain number of records—either forward or backward—from its current position. The syntax for the move method is as follows:
Recordset.move(nRecords)
Here nRecords is an integer that determines the number of records to move either forward or backward. If the integer is positive, the cursor moves forward. If it is negative, the cursor moves backward. The method returns a Boolean value indicating the success or failure of the operation.
The moveAbsolute method allows you to move the cursor to a specific record in the Recordset object. The syntax is as follows:
Recordset.moveAbsolute(n)
Here n is an index that specifies the number of the record to move to. The index is 1-based, so if n is 1 the cursor moves to the first record, and so on.
Recordset Events
At run time, the Recordset object exposes several events (via the Scripting Object Model) that can be handled either on the server side within Active Server Pages or on the client side using Dynamic HTML. These events give Web developers precise control over their data-driven applications at all stages of recordset processing. Recordset object events are triggered as a response to certain user actions or changes that occur in the recordset itself.
Table 14-4 lists the seven events available within the Recordset object.
Table 14-4. Recordset object events.
| Event | Description |
|---|---|
| onafterupdate | Fires after the UpdateRecord method has been called successfully on the Recordset object |
| onbeforeopen | Occurs right before a Recordset object is opened (either automatically or through the open method) |
| onbeforeupdate | Fires after the UpdateRecord method has been called, but before the actual update occurs on the Recordset object |
| ondatasetchanged | Occurs whenever there is a change made to the Recordset object |
| ondatasetcomplete | Occurs when the Recordset object has finished being downloaded from the server |
| onrowenter | Occurs when the cursor position moves to another record in the Recordset object |
| onrowexit | Occurs when the cursor position moves from a record in the Recordset object |
Each event fires at a certain point during recordset processing. For example, during a simple select operation where data is queried and then loaded into the recordset, the following events are triggered: onbeforeopen, onrowenter, onrowexit, onrowenter, ondatasetchanged, and ondatasetcomplete. An update operation triggers onbeforeupdate and onafterupdate. Record navigation triggers onrowexit and onrowenter.
An easy way to determine the sequence of event firing is to display a message within your event handlers indicating which event has fired. This can be achieved by using the MsgBox function for client-side scripting or by using the Response.Write syntax for server-side scripting. Be aware of the sequence of event firing so that you can place validation code and other types of code in the appropriate events.
The onafterupdate event
The onafterupdate event fires after the record has been updated via the UpdateRecord method. This event can be used to notify the end user that changes have been successfully saved to the database. The following code shows an example:
<SCRIPT ID=clientEventHandlersVB LANGUAGE=vbscript> <!-- Sub rsProfile_onafterupdate() MsgBox "Your changes have been saved.", 0, "VI-Bank" End Sub //--> </SCRIPT>
This code would be placed within the <HEAD> section of your HTML code and would be appropriate for Internet Explorer browsers since the scripting language is VBScript.
The onbeforeopen event
The onbeforeopen event fires before the recordset is opened either automatically or via the open method. It is useful if you want to change the SQL statement for the recordset or set some parameters.
The onbeforeupdate event
The onbeforeupdate event fires after the UpdateRecord method has been called but before the actual update occurs within the data source. This event is extremely useful for data validation. Within the onbeforeupdate event, data fields that have been input by the user can be validated against certain criteria. If they meet the criteria, the update can be allowed to proceed; if they do not meet the criteria, you can call the CancelUpdate method within the event to cancel the update and prompt the user to retry the input with some different values. Here's an example:
<SCRIPT ID=clientEventHandlersVB LANGUAGE=vbscript> <!-- Sub rsProfile_onbeforeupdate() If trim(rsProfile.fields.getValue("e_mail")) = "" Then MsgBox "E-Mail is a required field.", 0, "VI-Bank" rsProfile.CancelUpdate() End If End Sub //--> </SCRIPT> This example checks the value of the e_mail field within the rsProfile Recordset object. If there is no entry in the field, a message box appears to the user and reminds the user that this is a required field. Finally, the CancelUpdate method is used to cancel the update of the recordset. If you were writing the same procedure for server-side execution, you might take advantage of a label field for displaying the message, like this:
Sub rsProfile_onbeforeupdate() If trim(rsProfile.fields.getValue("e_mail")) = "" Then lblStatus.setCaption("E-Mail is a required field.") rsProfile.CancelUpdate() End If End Sub The ondatasetchanged event
The ondatasetchanged event fires whenever there is a change made to the Recordset object. The event is fired under two circumstances: after a new data set is requested or when the existing data set is altered via the addRecord, deleteRecord, or updateRecord methods.
The ondatasetcomplete event
The ondatasetcomplete event fires when the Recordset object has finished being downloaded from the server. When the scripting platform is Internet Explorer 4.0, this means that the Recordset object has cached all its data on the client. When the scripting platform is ASP, this means that the Recordset object is completely available to the ASP code. In either case, all the requested data is available in the Recordset object once the ondatasetcomplete event has fired (assuming the query was valid and returned some rows).
This event is particularly useful when performing client-side scripting because the data transfer is asynchronous. The ondatasetcomplete event can be used to tell you when the entire transfer of data from the server to the client has completed.
The onrowenter event
The onrowenter event fires when the cursor position moves to another record in the Recordset object. Methods that can trigger this event include the move, moveAbsolute, moveFirst, moveLast, movePrevious, and moveNext methods. The event is also fired when data is first populated into the Recordset object.
An interesting thing happens at the first or last row of the Recordset object. If the cursor is on the first row and the movePrevious method is called, the cursor moves to the BOF record and then back to the first row again. This causes the onrowenter event to fire twice. If the cursor is on the first row and the moveFirst method is called, the cursor simply moves to the first row and the onrowenter event fires once. Something similar happens when the cursor is located on the last row of the Recordset object. The moveNext method causes the onrowenter event to fire twice, whereas the moveLast method causes the onrowenter event to fire just once.
The onrowexit event
The onrowexit event fires when the cursor position moves from a record in the Recordset object. Methods that can trigger this event include the move, moveAbsolute, moveFirst, moveLast, movePrevious, and moveNext methods. The event is also fired when data is first populated into the Recordset object.
If you are using the client-side scripting platform, the onrowexit event also fires when you move away from the Web page within your browser—perhaps by using the browser's Back button, Refresh button, Home button, or bookmarks. You can't use this event to determine whether the user is leaving the page, however, because it also fires when the user first retrieves the record and then whenever the user navigates through the Recordset object.
If you want to know when the user is leaving the page, you should use another event—the onbeforeunload event for the window object. The window object onbeforeunload event can therefore be used to warn users that their changes (if any have been made) will be lost if they continue with the operation and they have not yet saved the changes. You can present the user with a message box and then allow the user to decide whether to continue and thereby lose any changes or to go back to the page and save any changes.
Function window_onbeforeunload() window_onbeforeunload = "If you have made changes and have " + _ "not yet saved them, they will be lost." End Function
Figure 14-4 shows the dialog box that appears given the sample code above.
Figure 14-4. A message box that asks users if they want to navigate away from the current page.
EAN: 2147483647
Pages: 143