Today let’s talk about the properties of Recordset object
1. CursorType attribute
AdOpenForwardOnly: Forward cursor only, default value. Same as a static cursor, except it can only scroll forward through the records. Use it to improve performance when only one-way movement in the recordset is required. (As the name implies, this cursor can only move forward. However, because of its limited functionality, it is very efficient when used with system resources.)
AdOpenKeyset: Keyset cursor. Although records deleted by other users are not accessible from your recordset, keyset cursors are similar to dynamic cursors except that records added by other users cannot be viewed. Data changed by other users can still be seen. (A KeySet cursor allows you to see changes made by other users since it was created, but you cannot see records added or deleted by other users.)
AdOpenDynamic: dynamic cursor. Can see additions, changes, and deletions made by other users. All types of moves within the recordset are allowed, except for bookmark operations that are not supported by the provider. (This type of cursor is powerful and consumes the most system resources. Dynamic cursors can see all changes in the record collections they save. Users using Dynamic cursors can see the edits, additions, and deletions made by other users. If the data Providers that allow this type of cursor support this visibility by retrieving data from the data source at regular intervals will undoubtedly require a lot of resources.)
AdOpenStatic: static cursor. A static copy of a collection of records that can be used to find data or generate reports. Additionally, additions, changes, or deletions made by other users are not visible. (A Static class cursor is just a snapshot of the data. That is, it cannot see changes made to the RecordSet by other users since it was created. With this type of cursor, you can navigate forward and backward. Because of its simplicity, Resource requirements are smaller than Dynamic!)
Note: Once the RecordSet is opened, you cannot change the CursorType property. However, if you first close the RecordSet, change the CursorType property, and then reopen the RecordSet, you can still effectively change the type of the cursor!
2. LockType attribute In any database application that can be modified by multiple users at the same time, you must deal with the situation that may occur when multiple users operate on the same record at the same time. When this happens, data integrity is compromised because one user may unknowingly overwrite someone else's changes when saving their own. At that time, you will feel as if you are not doing anything. To handle this situation. ADO allows you to determine the type of concurrent event control when updating a RecordSet object, and how to lock records by a user while he or she is editing. This is determined by the LockType attribute. This attribute has four values:
adLockReadonly: Default value, read-only. Data cannot be changed. (This is the default value of RecordSet. If you set the locking method to this value, you will not be able to update the Recordset.)
adLockPessimistic: Conservative record locking (entry-by-entry). The provider performs the necessary actions to ensure successful editing of the record, typically by immediately locking the record in the data source upon editing. (If set to this type of lock, the record is locked and can only be accessed by users who edit between the time the edit is started and when the record update is submitted to the data provider!)
adLockOptimistic: Open record locking (record-by-record). The provider uses open locking, locking records only when the Update method is called. (Records are locked only at the moment the data is submitted to the data provider.)
adlockBatchOptimistic: open batch update. Used for batch update mode as opposed to immediate update mode. (A RecordSet set to this type of locking mode will be called a batch update mode of the RecordSet. It can speed up the speed of updating the RecordSet to modify the data, but because multiple records are updated at the same time, it will also worsen the problems related to concurrent access!)
3 ,AbsolutePage property
The AbsolutePage attribute sets the page number of the page where the current record is located; use the PageSize attribute to divide the Recordset object into logical pages. The number of records on each page is PageSize (except that the last page may have less than PageSize number of records). It must be noted here that not all data providers support this attribute, so use it with caution.
Same as the AbsolutePosition property, the AbsolutePage property starts with 1. If the current record is the first row of Recordset, AbsolutePage is 1. You can set the AbsolutePage property to move to the first row of records on a specified page.
4. AbsolutePosition attribute If you need to determine the position of the current indicator in the RecordSet, you can use the AbsolutePosition attribute.
The value of the AbsolutePosition attribute is the position of the current indicator relative to the first trade, starting from 1, that is, the AbsolutePosition of the first trade is 1.
Note that when accessing a RecordSet, there is no guarantee that the RecordSet will appear in the same order every time.
To enable AbsolutePosition, you must first set it to use the client cursor (pointer): rs.CursorLocation=3
5. PageCount attribute Use the PageCount attribute to determine how many "pages" of data the Recordset object includes. The "page" here is a collection of data records with a size equal to the setting of the PageSize property. Even if the number of records on the last page is less than the value of PageSize, the last page is also considered a page of PageCount. It must be noted that not all data providers support this attribute.
6. PageSize attribute
The PageSize attribute is the key to determining how to display pages when ADO accesses the database. You can use it to determine how many records form a logical "page". Sets and creates a page size that allows moving to the first record of another logical page using the AbsolutePage property. The PageSize property can be set at any time.
7. RecordCount attribute This is also a very commonly used and important attribute. We often use the RecordCount attribute to find out how many records a Recordset object contains. Use the RecordCount property to determine the number of records in a Recordset object. This property returns –1 when ADO cannot determine the number of records, or if the provider or cursor type does not support RecordCount. Reading the RecordCount property on a closed Recordset will generate an error. The cursor type of the Recordset object affects whether the number of records can be determined. The RecordCount property returns -1 for forward-only cursors, the actual count for static or keyset cursors, and -1 or the actual count for dynamic cursors depending on the data source.
8. BOF and EOF attributes. Usually we write code in the ASP program to check the BOF and EOF attributes, so as to know the location of the RecordSet currently pointed by the indicator. Using the BOF and EOF attributes, we can know whether a Recordset object contains records or Know whether the moved record row has exceeded the range of the Recordset object.
If the position of the current record is before the first row of a Recordset object, the BOF property returns true, otherwise it returns false.
If the position of the current record is after the last row of a Recordset object, the EOF property returns true, otherwise it returns false.
(Both BOF and EOF are True, indicating that there are no records in the RecordSet.)
9. The Filter attribute specifies filtering conditions for the data in the Recordset. Use the Filter attribute to selectively block records in the Recordset object. The filtered Recordset will become the current cursor.
This will affect other properties based on the current cursor return value, such as AbsolutePosition, AbsolutePage, RecordCount, and PageCount, because setting the Filter property to a
specific
value moves the current record to the first record that satisfies the new value.
I think this attribute is quite useful. Sometimes after we open the Recordset and make certain judgments, we want to filter the records, that is, readjust the SQL statement. Should we close the Recordset and open it with a new SQL statement? No, we use the Filter attribute to filter, for example
rs.open exec,conn,1,1
if .... then rs.filter="name='xxx'"
instead of
rs.open exec,conn,1,1
if...then
rs.close
exec=exec&" where name='xxx'"
rs.open exec,conn,1,1
end if
In fact, Filter has to be used in many places. It will be mentioned in the future ASP skills. You can also think about it.
Tomorrow we will continue to talk about the methods of Recordset object.