Today let’s talk about some methods of the Recordset object.
1. The AddNew method creates new records that can update the Recordset object.
recordset.AddNew FieldList, Values
FieldList Optional. A single name, a set of names, or the ordinal position of the field in the new record.
Values are optional. A single or set of values for a field in the new record. If Fields is an array, Values must also be an array with the same number of members, otherwise an error will occur. The order of field names must match the order of field values in each array.
We are usually
rs.addnew
rs("xx")=xx
rs("xx")=xx
rs.update
Note that in immediate update mode (the provider writes changes to the underlying data source immediately when the Update method is called), calling the AddNew method without parameters sets the EditMode property to adEditAdd. The provider caches any changes to field values locally. Call the Update method to pass the new record to the database and reset the EditMode property to adEditNone. If the Fields and Values parameters are passed, ADO immediately passes the new record to the database (without calling Update), and the EditMode property value does not change (adEditNone).
You may ask what is the difference between using ADO's AddNew method in ASP and directly using the "Insert into..." statement? Which way is better? Answer: ADO's AddNew method only encapsulates the "Insert into" statement. Therefore, when operating on a large amount of data, directly using SQL statements will greatly speed up data access because it reduces the "translation" of ADO. "time.
2. Delete method deletes the current record or record group.
recordset.Delete AffectRecords
AffectRecords AffectEnum value determines the number of records affected by the Delete method. This value can be one of the following constants.
AdAffectCurrent Default. Only delete the current record.
AdAffectGroup deletes records that meet the current Filter property setting. To use this option, the Filter property must be set to one of the valid predefined constants.
adAffectAll deletes all records.
adAffectAllChapters Delete all subset records.
Using immediate update mode will cause immediate deletion in the database, otherwise the record will be marked for deletion from the cache and the actual deletion will occur when the Update method is called.
3. The Update method saves all changes made to the current record of the Recordset object.
recordset.Update Fields, Values
Fields are optional. Variant type, representing a single name; or variant type array, representing the name and serial number position of the field (one or more) that needs to be modified.
Values are optional. Variant type, representing a single value; or variant type array, representing the field (single or multiple) values in the new record.
If you wish to cancel any changes made to the current record or discard newly added records, you must call the CancelUpdate method.
4. CancelUpdate method
recordset.CancelUpdate
Use the CancelUpdate method to cancel any changes to the current record or discard newly added records. Changes to the current record or new records cannot be undone after the Update method is called. If a new record is added when the CancelUpdate method is called, the current record before AddNew is called will become the current record again. If the current record has not been changed or a new record has been added, calling the CancelUpdate method will generate an error.
5. The Find method searches the Recordset for records that meet specified criteria. If the criteria are met, the recordset position is set on the found record, otherwise the position is set at the end of the recordset.
Find (criteria, SkipRows, searchDirection, start)
criteria A string containing statements that specify column names, comparison operators, and values for the search.
SkipRows Optional, long integer value, whose default value is zero, which specifies the offset of the current row or start bookmark to start the search.
searchDirection Optional SearchDirectionEnum value that specifies whether the search should start at the current row or the next valid row. Its value can be adSearchForward or
adSearchBackward. Whether the search ends at the beginning or end of the recordset is determined by the searchDirection value.
start Optional, variant bookmark, used as the starting position of the search.
criteria "Comparison operator" can be ">" (greater than), "<" (less than), "=" (equal to), ">=" (greater than or equal to), "<=" (less than or equal to), " <>"
(not equal to) or "like" (pattern match). The values in criteria can be strings, floating point numbers, or dates. String values are delimited by single quotes (e.g. "state = 'WA'").
Date values are delimited by "#" (numeric symbols) (e.g. "start_date > #7/22/97#").
It should be noted that find does not support multiple fields. But it can be achieved using filters. "name='abc'"AND "city='sh'" is not allowed
6. Move method moves the position of the current record in the Recordset object
recordset.Move NumRecords, Start
NumRecords A signed long integer expression that specifies the number of records to move from the current record position.
Start optional, string or variant type, used to calculate bookmarks. Can also be one of the following values:
AdBookmarkCurrent Default. Start from the current record.
AdBookmarkFirst starts with the first record.
AdBookmarkLast starts from the last record.
Things to note are:
(1) If the NumRecords parameter is greater than zero, the current record position will move forward (toward the end of the records set). If NumRecords is less than zero, the current record position is moved backward (toward the beginning of the recordset).
(2) Calling the Move method from an empty Recordset object will generate an error.
(3) If the Move call moves the current record position to before the first record, ADO will place the current record before the first record of the recordset (BOF is True). Attempting to move backward when the BOF property is already True will generate an error; if the Move call moves the current record position after the tail record, ADO places the current record after the tail record of the recordset (EOF is True). Attempting to move forward when the EOF attribute is already True will generate an error.
7. The MoveFirst, MoveLast, MoveNext and MovePrevious methods move to the first, last, next or previous record in the specified Recordset object and make the record the current record.
recordset.{MoveFirst | MoveLast | MoveNext | MovePrevious}
Things to note are:
(1) Use the MoveNext method to move the current record forward by one record (towards the bottom of the Recordset). If the last record is the current record and the MoveNext method is called, ADO sets the current record after the tail record of the Recordset (EOF is True). Attempting to move forward when the EOF attribute is already True will generate an error.
(2) Use the MovePrevious method to move the current record position backward by one record (toward the top of the records set). The Recordset object must support backward cursor movement; otherwise the method call will generate an error. If the first record is the current record and the MovePrevious method is called, ADO sets the current record before the first record of the Recordset (BOF is True). When the BOF attribute is True, moving backward will generate an error.
8. The Clone method creates a duplicate Recordset object that is identical to the existing Recordset object. Optionally specify that the replica is read-only.
Set rstDuplicate = rstOriginal.Clone
rstDuplicate object variable that identifies the duplicate Recordset object being created.
rstOriginal object variable identifies the Recordset object to be copied.
Use the Clone method to create multiple copies of a Recordset object, which is useful if you want to keep multiple current records in a given record group. Using the Clone method is much more efficient than creating and opening a new Recordset object with the same definition as the initial definition.
That is to say
rs.open exec,conn,1,1
rs2.open exec,conn,1,1
It should be rewritten like this
rs.open exec,conn,1,1
rs2=rs.clone
should be noted that:
(1) The current record of the newly created copy will be set as the first record.
(2) Closing the original Recordset does not close its copy, and closing a copy will not close the original Recordset or any other copy.
9. The Close method closes the open object and any related objects.
object.Close
Things to note are:
(1) Use the Close method to close the Recordset object in order to release all associated system resources. Closing an object does not delete it from memory; you can change its property settings and
open it again later. To completely remove the object from memory, set the object variable to Nothing.
(2) If you are editing in immediate update mode, calling the Close method will generate an error, and you should first call the Update or CancelUpdate method.
10. Open method, why do we say this last? Because the previous attribute methods are not clear, we will not understand the CursorType parameter.
recordset.Open Source, ActiveConnection, CursorType, LockType, Options
Recordset objects can connect to Command objects through the Source property. The Source parameter can be a Command object name, a SQL command, a specified data table name or a Stored Procedure. If this parameter is omitted, the system uses the Source property of the Recordset object. ActiveConnection
Recordset objects can connect to Connection objects through the ActiveConnection property. The ActiveConnection here can be a Connection object or a string of string parameters containing
database
connection information (ConnectionString).CursorType
The CursorType parameter of the Open method of the Recordset object indicates what type of cursor the data will be started with, including adOpenForwardOnly, adOpenKeyset, adOpenDynamic and ad
OpenStatic, which are described as follows:
----------------- ------------------------------------------------
Constant constant value
description- -------------------------------------------------- ----------
adOpenForwardOnly 0 Default value, starts a cursor that can only move forward (Forward Only).
adOpenKeyset 1 starts a Keyset type cursor.
adOpenDynamic 2 starts a Dynamic type cursor.
adOpenStatic 3 starts a Static type cursor.
-------------------------------------------------- -----------
The above cursor types will directly affect all properties and methods of the Recordset object. The following list explains the differences between them.
Recordset properties adOpenForwardOnly adOpenKeyset adOpenDynamic adOpenStatic
AbsolutePage Not supported Not supported Readable and writable Readable and writable
AbsolutePosition Not supported Not supported Readable and writable Readable and writable
BOF Read-only Read-only Read-only
CursorType Read-write Read-write Read-write Read-write
EOF Read-only Read-only Read only
Filter Read and write Read and write Read and write Read and write
LockType Read and write Read and write Read and write Read and write
PageCount Not supported Not supported Read only Read only
PageSize Read and write Read and write Read and write Read Write
RecordCount Not supported Not supported Read-only Read-only
AddNew Supported Supported Supported Supported
CancelBatch Supported Supported Supported Supported CancelUpdate Supported Supported
Supported Supported
Close Supported Supported Supported Supported Supported Supported Supported Supported
Delete Supported Supported Supported Supported Supported Supported Supported Supported Supported
Move Not supported Supported Supported Supported Supported
MoveFirst Supported Supported Supported Supported Supported
MoveLast Not supported Supported Supported Support Support
MoveNext Support Support Support Support Support
MovePrevious Not support Support Support Support
Open Support Support Support Support
Update Support Support Support Support Support
UpdateBatch Support Support Support Support
---------------------------- -------------------------------------
The LockType parameter of the Open method of the Recordset object indicates that the Lock type. If this parameter is omitted, the system will use the LockType property of the Recordset object as the default value. LockType parameters include adLockReadOnly, adLockPrssimistic, adLockOptimistic and adLockBatchOptimistic, etc., which are described as follows:
Constant value description
--------------------------------- --------------------------------
adLockReadOnly 1 Default value, the Recordset object is started in read-only mode and cannot run AddNew, Update and Delete methods
adLockPrssimistic 2 When the data source is being updated, the system will temporarily lock other users' actions to maintain data consistency.
adLockOptimistic 3 When the data source is being updated, the system will not lock other users' actions, and other users can add, delete, and modify data.
adLockBatchOptimistic 4 When the data source is being updated, other users must change the CursorLocation attribute to adUdeClientBatch to add, delete, or modify data.
Things to note are:
If the data source returns no records, the provider sets both the BOF and EOF properties to True and does not define the current record position. If the cursor type allows it, new data can still be added to the empty Recordset object.