|
|
|
|
|
|
|
|
|
|
|
|
|
|
ASP ADVANCED
Up to this point, you've learned only how to use SQL to modify the records in a Recordset. However, you can also use a number of Recordset methods to modify the records in a Recordset. The following list briefly explains each method:
For example, you can use
the AddNew method to add a brand new record to an open Recordset, like
this: <!--
#INCLUDE VIRTUAL="ADOVBS.inc" --> In this script, the AddNew method is used to create a new record. Next, the MyColumn field of the new record is given the value A new column. Finally, the Update method is called to save the new record. To use these methods, the Recordset must be opened with a locking type other than read-only. Instead of using the AddNew
method to add a new record to a table, however, you can use the SQL INSERT
method instead. In general,
it's better to use SQL than the methods just described because SQL is much
more flexible. For the
remainder of this book, SQL is used to manipulate the data in a database
table. Navigating a Recordset The Recordset object
includes a number of methods for moving through the records in a Recordset.
Many of these methods can be used only when a Recordset is opened
with a particular type of cursor. This
list includes some of these methods and an explanation of their functions:
For example, suppose you
want to move backward through the records in a Recordset.
You could do this by using the MoveLast and MovePrevious methods
and the BOF property. The
following Active Server Page illustrates how: <HTML> In this example, the
Recordset is opened with a static cursor.
This stratagy is required for both the MoveLast and the
MovePrevious methods to work. Once
the Recordset is opened, each record in the Recordset is displayed until
the beginning of the Recordset is reached.
The BOF property is used to detect when this happens. You can use these methods
to move backward through a Recordset.
However, it's difficult to think of a legitimate reason why you
would need to do this. It's
much more efficient to use the SQL language itself to order the results of
a query. When you can, you
should order your records by using a SQL ORDER BY clause. (See Chapter
" Basic SQL," for more information on using the ORDER BY
clause.) Retrieving a Record
Count You can use the RecordCount
property of the Recordset object to determine the number of records
contained in a Recordset. However,
you should resist using this property.
The problem is that this property is extremely inefficient in most
situations. You can't use the
RecordCount property with a Recordset that uses a forward-only cursor.
You must open a less efficient cursor, as in this example: <!-- #INCLUDE
VIRTUAL="ADOVBS.inc" --> This script outputs the
number of records in the table named MyTable. The
RecordCount property is used to return this number.
For the RecordCount property to be used, the Recordset is opened
with a static cursor. Normally, the only reason
you need a record count is to determine whether at least one record fits
certain criteria. For
example, you might want to check whether a person has entered a valid
password. In this situation,
you may be tempted to query a password table and use the RecordCount
property to determine whether the password exists.
If RecordCount is greater than 0, the password exists; otherwise,
it doesn't, and the password is invalid. However, it's far better to
use the EOF property to check whether a query has returned any results.
You can use the EOF property when using a Recordset with a
forward-only cursor. Here's
an example: <!-- #INCLUDE
VIRTUAL="ADOVBS.inc" --> In this example, the EOF property is used to test whether any results were returned from the query. If the EOF property is TRUE, the password that the user entered doesn't exist in the password table. In some situations, you
really do need to retrieve a record count.
For example, you might want to display the total number of
registered users at your Web site. Again,
however, you should avoid using the RecordCount property.
Instead, you can perform a SQL COUNT( * ) query, as in the
following lines: <% Notice how the column alias
MyCount is used in this SQL query. By
providing the aggregate function COUNT( * ) with a name, you can use this
name when outputting the results of the query. Paging Through a Recordset Suppose you want to display
a list of products for sale at your Web site.
However, you're selling hundreds of items.
In that case, you probably wouldn't want to display all the items
within a single Active Server Page. It
would be better if you could allow the user to page through the list of
items. The Recordset object has
three properties for this very purpose.
You can use these properties to divide the records in a Recordset
into logical pages. By
dividing the records in a Recordset into different pages, you can display
only portions of a Recordset at a time.
Here's a list of these properties:
To divide a Recordset into multiple pages, you use the PageSize property to specify the number of records in a single page. You then can use the AbsolutePage property to move to a particular page of records. Finally, the PageCount property can be used to return the total number of pages. Listing below shows how these properties can be used. Lisring
Script for pages.asp. <HTML> This
Active Server Page displays the records in the Products table five records
at a time. A session variable
named CurrentPage is used to keep track of the current page within the
Recordset. When the user
clicks NEXT, the next page of records is displayed.
When the user clicks PREV, the previous page is displayed. Retrieving
Records into an Array In
certain situations, you'll need to retrieve the records of a Recordset
into an array. For example,
if you need to alter the data represented by a Recordset, but you don't
want to change the records in the Recordset itself, you can retrieve the
records into an array. To
assign the records in a Recordset to an array, you use the GetRows( )
method of the Recordset object. Here's
an example: <% In this script, all the records contained in the Recordset named RS are assigned to the array named MyArray. This array is automatically created and populated by the GetRows( ) method. The GetRows( ) method creates a two-dimensional array. The first subscript identifies the field and the second subscript indicates the record number. The following script can be used to display all the contents of MyArray:
<% The VBScript UBOUND( ) function is used to determine the size of the second dimension of the array. The FOR...NEXT loop is used to iterate through all of its elements. This array represents a Recordset with two columns. When the first index of the array has the value 0, the first column is represented. When the first index of the array has the value 1, the second column is represented. Specifying the Maximum Size of a RecordSet Suppose
you want to display the last 10 messages posted on your Web site, but no
more than 10 messages. Or
imagine that you want to display three links and no more than three links
to your favorite Web sites. How
can you limit the records that you retrieve into a Recordset?
A property of the Recordset object is available for this purpose.
You can limit the number of records retrieved into a Recordset from
a database query by using the maxRecords property, as in the following
example: <%
In
this script, the MaxRecords property is set to 10.
When the Rs Recordset is opened, no more When
you use the MaxRecords property, you must set the property before you open
the Recordset. After a
Recordset is open, the property is read-only. Summary This
chapter explored the Recordset object.
You learned how to use a Recordset to display data from a database
table in your Active Server Pages. You
also learned how to open a Recordset with different types of cursors and
locking. Finally, you learned
a number of advanced methods of the Recordset object.
|
 |
||||
|
|
||||||
|
|
||||||
|
|
|
|
 |
