Asp RecordSet Object


Recordset types Lock types Opening recordset Methods Properties

Each information

in a database is a record and recordset is a collection of records.In ASP Recordset

objects is used to manipulate the records.All Recordset objects consist of records

(rows) and fields (columns). Depending on the functionality supported by the

provider, some Recordset methods or properties may not be available.

You can create a recordset using following syntax:



Asp Recordset Types


  • Forward-only

    – allows you to only scroll forward through the Recordset. Additions, changes,

    or deletions by other users will not be visible. This improves performance in

    situations where you need to make only a single pass through a Recordset.

  • Static

    — Similar to Forward only recordset, except that it is scrollable forward and

    backward and always allows bookmarks. Additions, changes, or deletions by other

    users will not be visible. This is the only type of cursor allowed when you

    open a client-side Recordset object.

  • Dynamic

    — allows you to view additions, changes, and deletions by other users; allows

    all types of movement through the Recordset that doesn’t rely on bookmarks;

    and allows bookmarks if the provider supports them.

  • Keyset —

    behaves like a dynamic type, except that it prevents you from seeing records

    that other users add, and prevents access to records that other users delete.

    Data changes by other users will still be visible. It always supports bookmarks

    and therefore allows all types of movement through the Recordset.




    Asp Recordset – Locking

    Locking in datstore

    prevents other people from changing them.The four types of locking are :

  • Read Only-This

    is default and no locking is performed since you can not change the data.

  • Pessimistic

    – record is locked as soon as you start editing so that no one else can change

    the record until you release the lock.

  • Optimistic

    – records locked only when you update them assuming that no one else will edit

    while you are editing it.

  • Optimistic

    Batch – in batch update only the record which is being updated is locked.




    Opening recordsets

    The general syntax


    <% Source,Activeconnection,CursorType,LockType,Options



    – is where the data comes from.This may be a table name,Sql statement a stored

    procedure or query, or a command object.

    ActiveConnection – identifies the datastore connection.this

    could be a connection string or connection object.

    Cursor Type and Lock Type -we have seen this before.

    Options – specifies how Source property is defined.If the

    source holds a table name then it is adCmdTable,or if the source is a SQL statement

    then it is adCmdText , or if the source holds the name of stored procedure then

    it is adCmdStoredProc , or source hold unknown type then it is adCmdUnknows.


    recordset.Open “Passenger” ,strConnection,

    adOpenForwardOnly , adLockReadOnly,adCmdTable



    Asp Recordset – Methods


    Method Syntax Description
    Open Source,Activeconnection,CursorType,LockType,Options opens a cursor that represents records from a base table,

    the results of a query, or a previously saved Recordset.

    Close RecordSet.Close closes a Recordset object releases the associated data and

    any exclusive access you may have had to the data through this particular


    AddNew recordset.AddNew FieldList, Values create and initialize a new record.All arguments are optional.

    FieldList is a single name, or an array of names or ordinal positions of

    the fields in the new record.Values are corresponding values in the record.

    Save recordset.Save Destination, PersistFormat Saves the Recordset in a file or Stream object.All arguments

    are optional. Destination is a Variant that represents the complete path

    name of the file where the Recordset is to be saved, or a reference to a

    Stream object.PersistFormat  is a value that specifies the format in

    which the Recordset is to be saved (XML or ADTG). The default value is adPersistADTG

    Update recordset.Update Fields, Values Saves any changes you make to the current row of a Recordset

    object, or the Fields collection of a Record object.All arguments are optional.

    Fields is a variant that represents a single name, or a Variant array that

    represents names or ordinal positions of the field or fields you wish to

    modify.Values is a Variant that represents a single value, or a Variant

    array that represents values for the field or fields in the new record.

    UpdateBatch recordset.UpdateBatch AffectRecords uses when modifying a Recordset object in batch update mode

    to transmit all changes made in a Recordset object to the underlying database.

    AffectRecords(Optional) indicates how many records the UpdateBatch method

    will affect.

    Cancel recordet.Cancel terminate execution of an asynchronous method call .The

    asynchronous methods are open, CopyRecord, DeleteRecord, or MoveRecord.

    CancelBatch recordset.CancelBatch AffectRecords cancel any pending updates in a Recordset in batch update

    mode. AffectRecords(Optional) is the  value that indicates how many

    records the CancelBatch method will affect.

    CancelUpdate recordset.CancelUpdate cancel any changes made to the current row or to discard

    a newly added row. You cannot cancel changes to the current row or a new

    row after you call the Update method, unless the changes are either part

    of a transaction that you can roll back with the RollbackTrans method, or

    part of a batch update.

    Delete recordset.Delete AffectRecords Deletes the current record or a group of records.AffectRecords

    is determines how many records the Delete method will affect. The default

    value is adAffectCurrent. 

    GetRows array = recordset.GetRows( Rows, Start, Fields ) Retrieves multiple records of a Recordset object into an

    array.All the arguments are optional and if not used it will return all

    rows starting from current.Rows argument specifies the number of rows returned,Start

    allows you to specify the start position,and fields is the name or number

    of of a field, if specified only the values of that fields are returned.

    GetString Set Variant = recordset.GetString(StringFormat, NumRows,

    ColumnDelimiter, RowDelimiter, NullExpr)

    Returns the Recordset as a string. StringFormat specifies

    how the Recordset should be converted to a string. The RowDelimiter, ColumnDelimiter,

    and NullExpr parameters are used only with a StringFormat of  adClipString.

    All other arguments are optional.NumRows is the number of rows to be converted

    in the Recordset. ColumnDelimiter is a delimiter used between columns, if

    specified, otherwise the TAB character. RowDelimiter is a delimiter used

    between rows, if specified, otherwise the CARRIAGE RETURN character.NullExpr

    is an expression used in place of a null value, if specified, otherwise

    the empty string.

    Find recordset.Find (Criteria, SkipRows, SearchDirection, Start) Searches a Recordset for the row that satisfies the specified

    criteria. Criteria is a string that contains a set of comparisons that will

    be matched with the records to be retrieved.SkipRows, SearchDirection, Start

    are optional. SkipRows specifies the row offset from the current row or

    Start bookmark to begin the search. By default, the search will start on

    the current row. SearchDirection specifies direction of search.Start specifies

    position of start.

    Filter recordset.Filter (Criteria) Indicates a filter for data in a Recordset.The criteria

    string is made up of clauses in the form FieldName-Operator-Value (for example,

    “LastName = ‘Smith'”). You can create compound clauses by concatenating

    individual clauses with AND (for example, “LastName = ‘Smith’ AND FirstName

    = ‘John'”) or OR (for example, “LastName = ‘Smith’ OR LastName = ‘Jones'”).

    Move recordset.Move NumRecords, Start allows to move the position of the current record in a Recordset

    object. NumRecords specifies the number of records that the current record

    position moves.Start(Optional) is a  String value or Variant that specifies

    the point you wish to start the move format could be adBookmarkCurrent,

    AdBookmarkFirst , adBookmarkLast.





    recordset.{MoveFirst | MoveLast | MoveNext | MovePrevious} MoveFirst method moves the current record position to the

    first record in the Recordset.MoveLast method moves the current record position

    to the last record in the Recordset. MoveNext method moves the current record

    position one record forward . MovePrevious method moves the current record

    position one record backward .


    Asp Recordset – Properties



    Property Description
    BOF & EOF BOF and EOF determines whether a Recordset object contains

    records or whether you’ve gone beyond the limits of a Recordset object when

    you move from record to record.The BOF property returns True (-1) if the

    current record position is before the first record and False (0) if the

    current record position is on or after the first record.

    The EOF property returns True if the current record position

    is after the last record and False if the current record position is on

    or before the last record.If either the BOF or EOF property is True, there

    is no current record

    Bookmark the Bookmark property is used to save the position of the

    current record and return to that record at any time. For example:

    <%Dim varBookmark

    varBookmark = recordset.Bookmark

    ……….Do some processing

    recordset.Bookmark = varBookmark%>

    AbsolutePage Indicates on which page the current record resides.We can

    Sets or returns a Long value from 1 to the number of pages in the Recordset

    object .

    AbsolutePosition Sets or returns the ordinal position of a Recordset object’s

    current record.

    MaxRecords Sets or returns a Long value that indicates the maximum

    number of records to return. Default is zero (no limit).

    PageCount Returns a Long value that indicates the number of pages

    in the Recordset

    PageSize Sets or returns a Long value that indicates how many records

    are on a page. The default is 10.

    RecordCount Returns a Long value that indicates the number of records

    in the Recordset

    CursorType Sets or returns the type of cursor used in a Recordset object.

    The default value is adOpenForwardOnly

    Locktype Sets or returns the type of locks placed on records during

    editing.The default value is adLockReadOnly





    Leave a Comment