ASP Getting Started tutorials-recordset objects

A summary of the Recordset object

A Recordset object represents the complete collection of records from the results of a basic table or command execution. In any case, the current record that the object refers to is a single record within the collection. Using a Recordset object, you can manipulate data from the provider, which can manipulate almost all data. All Recordset objects are constructed using records (rows) and fields (columns). The Recordset object is actually attached to the Connection object and the Command object. By creating and opening a Connection object, you can connect to the database we care about; by using the Command object, you can tell the database what we want to do: Insert a record, or find a record that matches the criteria, and by using the Recordset object, You can easily manipulate the results returned by the Command object.

Ii. Creating a Recordset Object

To work with the result with a Recordset object, you must first create a Recordset object instance. The format is as follows:

Set RS=Server.CreateObject("adodb.recordset")

Open a recordset

RS.Open Source,ActiveConnection,CursorType,LockType,Options

All parameters are optional. Source is the Command object variable name, SQL statement, table name, stored procedure call, or persistent rcordset file name.

ActiveConnection is a valid Connection object variable name or contains a ConnectionString string.

LOCKTYPE Specifies the type of locking that should be used when opening the Recordset.

The Options specify how to calculate the Source parameter or restore the recordset from a file that previously saved the recordset.

Iv. Common properties of Recordset objects

Common Properties < Tables > of Recordset objects

Property	Description/annotation
ActiveConnection	Specifies the connection information to the data provider that specifies which Connection object the current Recordset object belongs to.
Source	Specifies the data source for the Recordset object, which can be a Command object name, SQL statement, database table, or stored procedure.
Coursortype	Specifies the type of cursor used by the Recordset object.
There are 4 types of cursors: 01 before the cursor, the cursor can only move forward, the implementation of high efficiency. is the default value of Cursor; 11 keyboard cursor, the cursor can move forward or backward, Recordset recordset synchronization reflected from the creation of other users after the modification and deletion, but can not be synchronized reflected from the creation of other users of the new record; 21 Dynamic cursors, the cursor can be scrolled forward or backward, any time the recordset Recordset is synchronized to reflect any other user's actions; 31 static cursors, the cursor can move forward or backward, since the creation can not be synchronized to reflect the actions of other users, it is simple but consumes less resources.
LockType	Represents the type of lock recorded when editing. It determines how the recordset processes data records when more than one user tries to change a record at the same time.
01 read-only lock, record read only, cannot update Recordset, default value for LockType; 11 pessimistic lock, the edit record starts to lock immediately, until submits to the data provider; 21 optimistic lock, lock one record at a time, only when the Update method is invoked to commit the data, the record is locked; 31 optimistic batch updates, allowing simultaneous updating of multiple records.
Filter	Use to set a filter condition to filter the recordset records.
CacheSize	Represents the number of records for a Recordset object in the cache.
MaxRecords	Returns the maximum number of records for a Recordset object when executing an SQL query.
Bof	Determines whether the record pointer is before the first record.
Eof	Determines whether the record pointer is after the last record.
RecordCount	Returns the number of records for the Recordset object (the possible return of the same to a less accurate value when using the keyboard cursor; In general, this method is not used to count the number of records in the recordset because its performance is not stable)
Bookmark	A bookmark mark that is used to hold the location of the current record.
AbsolutePosition	The ordinal position in which to set the current record in the Recordset object is the first few lines of records.
PageSize	Represents the page size of the Reccordset object (how many records per page), and the default value is 10.
PageCount	Represents the number of pages in a Recordset object.
AbsolutePage	Represents the page number of the current record.
EditMode	Indicates the editing status of the current record, 01 has been edited, 11 has been modified without submission, and 21 is a new record in the database.
Status	Queries the status of the current record after a batch update to the Recordset object.
Value	Meaning description	Value	Meaning description
0	Record update succeeded.	1024	A record lock exists and the new record is not saved.
1	New records.	2048	Use optimistic concurrency events, not saved.
2	The record was deleted.	4096	The user violated the integrity rule and the record was not saved.
4	The record was not deleted.	8192	Very pending modification, record not saved.
8	The record has not been modified.	16384	Conflicts with a stored object, the record is not saved.
16	The bookmark is not valid and the record is not saved.	32768	There is not enough memory to save the record.
64	affect multiple records, not saved.	65536	The user does not have sufficient permissions in and is not saved.
128	Point to a pending insert, the record is not saved.	131072	Violation of basic database principles, record not saved.
256	The operation was canceled and the record was not saved.	262144	The record has been deleted from the data source.

　　　　

V. Methods of Rcordset objects

Methods of Recordset Objects < table two >

Method	Description/Format/annotation
Open	Opens a Recordset object.
Rs.open[source][,activeconnection][,cursortype][,lursortype][,option]
rs: For an already established Recordset object; Source: is an optional parameter, can be a Command object name, SQL statement, database table, or stored procedure; ActiveConnection: Defines connection information to the database; CursorType: Is the type of cursor used by the Recordset object; Locktypec is the type of lock used when editing Recordset object records; Option indicates how the data provider handles the content type of Source, its parameter values, and their meanings are shown in the following table:   options value meaning description 1 indicates that the executed string contains a command text. The 2 indicates that the executed string contains a A table name. The 4 indicates that the executed string contains a A stored procedure name. 8
Close	Closes a Recordset object. The object is not deleted from memory, but the data is not readable, but its properties can still be read. Therefore, a closed Recordset object can also open with the open method and maintain its original properties.
Rs.close
Rs: For a Recordset object that has already been established; If you are editing the update data, you must first invoke the update and CancelUpdate methods before using this method. Otherwise, there will be an error.
Clone	Creates a full copy of a Recordset object, but this method is used only if the Recordset object type used allows bookmarks to be used.
MoveFirst	Point the recordset pointer to the first row of records.
Rs.movefirst
MoveLast	Point the recordset pointer to the last record (the recordset must support bookmarks).
Rs.movelast
MovePrevious	Move the recordset pointer up one line (you should determine whether the BOF is true before use, and the method must support bookmarks).
Rs.movepreviors
MoveNext	Move the recordset pointer down one line (to determine if EOF is true before using it).
Rs.movenext
Move	Points the recordset pointer to the specified record.
Move n [, start]
n is the number of records to be moved, the positive time means moving forward (down), taking a negative to represent backward (up) movement; Start is an optional parameter that represents the starting point for the move.
GetRows	Reads one or more rows from a Rexordset object to an array.
Myarray=rs.getrows (Rows,start,fields)
MyArray is the target array name; Rs is the created Recordset object, rows is the number of rows to return the array, start is the starting point for reading the data (bookmark), Fields is the recordset's field.
NextRecordset	Clears the current Recordset and executes the next instruction to return the next Recordset object, and returns nothing to the recordset without the next instruction.
Requery	Re-execute the SQL operation to update the data in the Recordset object.
Resync	Updates the current recordset record from the data source to achieve synchronization with the data source.
Addnew	Add an empty record.
Rs.addnew N
Adds an empty record and adds an element (N) from the array to the empty record.
Delete	Deletes the current record.
Delete [value]
If value=1 (the default) indicates that the method deletes only the current record, value=2 indicates that the method deletes all records set by the Filter property.
Update	Saves any changes to the current record.
Rs.update
CancelUpdate	Cancels all changes made by the previous Update method.
UpdateBatch	The recordset works in bulk mode, canceling the update of the recordset.
Suports	Gets the ability information for the created Recordset object.
Note: When moving a record pointer using the move, MoveFirst, MoveLast, MovePrevious, MoveNext methods, you need to be aware of the cursor type of the Rrcordset object. such as the roll forward cursor restricts the record pointer to move forwards only.