In the earlier example the data returned from a SQL query was
assigned to a RecordSet even though the RecordSet object was
not created. The RecordSet object was explicitly created when
the conn.Execute statement was executed. Alternatively a
RecordSet object can be explicitly initialized and the
additional flexibility of the RecordSet used.
A RecordSet contains a collection of records with the first
record as the current record.
Click for code example 4.
The RecordSet object has a few extra optional parameters to
help the programmer get more control over how the data is
accessible to different users and how data in the RecordSet
can be manipulated. The first parameter is the type of cursor
used. The cursor defines the types of manipulations that can
be done on an open RecordSet.
There are four types of cursors:
adOpenForwardOnly: This allows only forward movement in the RecordSet
adOpenKeyset: This allows both forward and backward movement in the RecordSet. It also reflects delete and update operations made on the records by other users
adOpenDynamic: This allows both forward and backward movement in the RecordSet. It reflects the operations done on the records by other users.
AdOpenStatic: This allows both forward and backward movement in the RecordSet. It does not reflect any operations done on the records by other users.
If omitted the
adOpenForwardOnly is taken as default cursor.
The other optional parameter is the locking type. The
locking type tells the database how to deal with situations
where more than one user tries to manipulate a record.
The options are:
adLockReadOnly: The records are read only.
adLockPessimistic: The records are locked immediately upon editing.
adLockOptimistic: The record is locked when the RecordSet's Update method is called.
adLockBatchOptimistic: The records are batch updated.
adLockReadOnly is taken as the default locking type.
The RecordSet object has a number of methods that help navigate through the RecordSet.
||Moves the specified number of records forward or backward|
||Moves to the first record|
||Moves to the next record|
||Moves to the previous record|
||Moves to the last record|
||Closes the recordset|
||Opens the cursor on the recordset|
BOF - Indicates that the current position is before the
EOF - Indicates that the position is after the last
Apart from the above methods and parameters the
RecordSet has many additional methods like AddNew to add
a new record, Update to save changes to a record, UpdateBatch
to save changes to all the records when the RecordSet is in the
batch-update mode and so on. Most of the other methods are for
Displaying the results:
The RecordSet object has a Fields Collection that
can be used to manipulate the columns of a table. E.g.
RS.Fields.Count gives the number of columns in the table.
The data in a particular column in a record can be accessed
Set RS =Server.CreateObject("ADODB.RecordSet")
conn.Open "FILEDSN= C:\myAspDir\testDsn.dsn","username","password"
RS.Open "SELECT * FROM MYTABLE WHERE MYRECORDNO>0", conn
<% FOR i=0 to RS.Fields.Count -1 %>
<TH><% = RS(i).Name%></TH>
<% WHILE NOT RS.EOF %>
<% FOR i=0 TO RS.Fields.Count - 1%>
<TD><% = RS(i) %></TD>