ADO Recordset
The ADO Recordset object is used to hold a dynamic set of records from a database table. In ADO, this object is the most important and the most used object to manipulate data from a database.
Syntax:-
set objRecordset=Server.CreateObject("ADODB.recordset")
Example:-
Set rsstudent = Server.CreateObject(“ADODB.recordset”)
Where rsstudent is a variable of recordset object.When a recordset is opened, the current record pointer will point to the first record and the BOF and EOF properties are False. If there are no records, the BOF and EOF property are True.
Method
1. Open The Open method opens a database element that gives access to records in a table, the results of a query etc
Syntax:-objRecordset.Open source, Actconn, cursortype, locktype
Where Source indicates an SQL select statement a table namea Command object
Actconn indicates a connection string or a Connection object
Cursortype indicates a value that specifies the type of cursor to use when opening a recordset object. Default is adOpenForwardOnly
Locktype indicates a value that specifies the type of locking that is applied on the set of records in the recordset object. Default is adLockReadOnly.
objRecordset indicates a variable of recordset object.
CursorType Values
| Constant | Value | Description |
| adOpenUnspecified | -1 | Unspecified type of cursor |
| adOpenForwardOnly | 0 | Default. A forward-only cursor. Moves the cursor only in one direction. |
| adOpenDynamic | 2 | A dynamic cursor. Additions, changes, and deletions by other users are visible and all types of movement through the recordset are allowed. |
| adOpenKeyset | 1 | A keyset cursor. Like a dynamic cursor, except that cannot see records that other users add although records the other users delete are inaccessible from the Recordset. Data changes by other users are still visible. |
| adOpenStatic | 3 | A static cursor. A static copy of a set of records that you can be used to find data or generate reports. Additions, changes, or deletions by other users are not visible. |
LockType Values
| Constant | Value | Description |
| adLockUnspecified | -1 | Unspecified type of lock. |
| adLockReadOnly | 1 | Default. Read-only records. |
| adLockPessimistic | 2 | Pessimistic locking record by record. The provider locks the record immediately after editing. |
| adLockOptimistic | 3 | Optimistic locking record by record. The provider locks the record only when calling update. |
| adLockBatchOptimistic | 4 | Optimistic batch updates. Required for batch update mode. |
Example:-Open a recordset based on a table:-
<%
set connect=Server.CreateObject("ADODB.Connection")
connect.Open “northwind”
set rsstudent = Server.CreateObject("ADODB.recordset")
rsstudent.Open "Student", connect
%>
Open an ADO SQL Recordset:
<%
set connect=Server.CreateObject("ADODB.Connection")
connect.Open “northwind”
set rsstudent = Server.CreateObject("ADODB.recordset")
rsstudent.Open "select * from student", connect
%>
where connect is a variable of connection object and rsstudent is a variable of recordset object.
2. Close The Close method is used to close a recordset object if it is open.
Syntax:-objRecordset.Close
Example:-rsstudent.closewhere rsstudent is a variable of recordset object,
3. MoveFirstThis method is used to move to the first record in a recordset object.
Syntax:-objRecordset.MoveFirstExample:-rsstudent.movefirstwhere rsstudent is a variable of recordset object,
4. MoveLast This method is used to move to the last record in a recordset object.
Syntax:-objRecordset.MoveLastExample:-rsstudent.movelastwhere rsstudent is a variable of recordset object.
5. MoveNextThis method is used to move to the next record from the current record in a recordset object.
Syntax:-objRecordset.Movenext Example:-rsstudent.movenextwhere rsstudent is a variable of recordset object,
6. MovePreviousThis method is used to move to the previous record in a Recordset object.
Syntax:-objRecordset.Moveprevious Example:-rs.moveprevious
Property
1. BOF and EOF The BOF property returns True (-1) if the current record position is before the first record in the Recordset, otherwise it returns False (0).The EOF property returns True (-1) if the current record position is after the last record in the Recordset, otherwise it returns False (0). Syntax:-objRecordset.BOForobjRecordset.EOF
Example:
-<% while not rsstudent.EOF
rsstudent (“name”).value
rsstudent.movenext
wend%>where rsstudent is a variable of recordset object.
2. CursorLocation The CursorLocation property sets or returns a long value that indicates the location of the cursor service. Default value is AdUseServer.
Syntax :-objRecordset.CursorLocation = Value
Example:-rsstudent.cursorlocation=AdUseServer where rsstudent is a variable of recordset object.
CursorLocation Values
| Constant | Value | Description |
| AdUseNone | 1 | Does not use cursor services |
| AdUseServer | 2 | Default. Uses a server-side cursor |
| adUseClient | 3 | Uses a client-side cursor supplied by a local cursor library. |
3. CursorType The CursorType property sets or returns the cursor type to use when opening a recordset object. Default is adOpenForwardOnly.
Syntax:-objRecordset.CursorType = Value
Example:-rsstudent.cursortype=adOpenForwardOnly where rsstudent is a variable of recordset object.
4. ActiveConnection The ActiveConnection property sets or returns a string or variant that contains the Connection object associated with the Recordset object.
Syntax :-objRecordset.ActiveConnection= Value
Example:-rsstudent.activeconnection=connectwhere rsstudent is a variable of recordset object and connect is the variable of connection object.
5. RecordCountThe RecordCount property returns a long value that indicates the number of records in a Recordset object.
Note: This property will return -1 for a forward-only cursor; the actual count for a static or keyset cursor; and -1 or the actual count for a dynamic cursor. Syntax :-objRecordset.RecordcountExample:-Dim countCount = rsstudent.recordcountwhere rsstudent is a variable of recordset object.