Visual Basic 6 Black Book
(Publisher: The Coriolis Group)
Author(s): Steven Holzner
ISBN: 1576102831
Publication Date: 08/01/98

ADO: Moving To The Next Record In A Record Set

To move to the next record in an ADO record set, you use the Recordset object’s MoveNext method (this method takes no parameters). Let’s see an example. When the user clicks the appropriate button in our ADO code example, the adocode example (see “A Full-Scale ADO Example” in this chapter), we’ll move to the next record in our record set, adoRecordset. Note that we make sure we don’t move past the end of the record set by checking the record set’s EOF property:

Private Sub cmdNext_Click()
    On Error GoTo ErrLabel

    If Not adoRecordset.EOF Then
        adoRecordset.MoveNext
    End If

    If adoRecordset.EOF And adoRecordset.RecordCount > 0 Then
        adoRecordset.MoveLast
    End If

    Exit Sub

ErrLabel:
    MsgBox Err.Description
End Sub

ADO: Moving To The Previous Record In A Record Set

To move to the next record in an ADO record set, you use the Recordset object’s MovePrevious method (this method takes no parameters). Let’s see an example. When the user clicks the appropriate button in our ADO code example, the adocode example (see “A Full-Scale ADO Example” in this chapter), we’ll move to the previous record in our record set, adoRecordset. Note that we make sure we don’t move past the end of the record set by checking the record set’s BOF property:

Private Sub cmdPrevious_Click()
    On Error GoTo ErrLabel

    If Not adoRecordset.BOF Then adoRecordset.MovePrevious
    If adoRecordset.BOF And adoRecordset.RecordCount > 0 Then
        adoRecordset.MoveFirst
    End If

    Exit Sub

ErrLabel:
    MsgBox Err.Description
End Sub

ADO: Deleting A Record In A Record Set

To delete a record in an ADO record set, you use the Delete method:

recordset.Delete AffectRecords

Here, AffectRecords is a value that determines how many records the Delete method will affect. It can be one of the following constants:

•  adAffectCurrent—The default; deletes only the current record.

•  adAffectGroup—Deletes the records that satisfy the current Filter property setting.

Let’s see an example. Here, we delete a record in our ADO code example, the adocode example (see “A Full-Scale ADO Example” in this chapter), when the user presses the appropriate button:

Private Sub cmdDelete_Click()
    On Error GoTo ErrLabel

    adoRecordset.Delete
...

In addition, we move to the next record this way:

Private Sub cmdDelete_Click()
    On Error GoTo ErrLabel

    adoRecordset.Delete

    adoRecordset.MoveNext
    If adoRecordset.EOF Then
        adoRecordset.MoveLast
    End If

    Exit Sub

ErrLabel:
    MsgBox Err.Description
End Sub

And that’s it—now we’ve deleted a record.

ADO: Executing SQL In A Record Set

You can execute an SQL statement when you open a record set using the Open method by passing that statement as the Source argument:

recordset.Open [ Source, [ActiveConnection, [Type, [LockType, [Options]]]]

Here are the arguments for this method:

•  Source—A valid Command object variable name, an SQL statement, a table name, a stored procedure call, or the file name of a Recordset.

•  ActiveConnection—A valid Connection object variable name or a string containing ConnectionString parameters.

•  Type—Sets the Recordset type (see the following list).

•  LockType—A value that determines what type of locking (concurrency) the provider should use when opening the Recordset object (see the following list).

•  Options—A Long value that indicates how the provider should evaluate the Source argument if it represents something other than a Command object, or that the Recordset object should be restored from a file where it was previously saved (see the following list).

Here are the possible values for the Type argument:

•  dbOpenKeyset—Opens a dynaset-type Recordset object, which is like an ODBC keyset cursor.

•  dbOpenDynamic—Opens a dynamic-type Recordset object, which lets the application see changes made by other users.

•  dbOpenStatic—Opens a static-type Recordset object.

•  dbOpenForwardOnly—Opens a forward-only-type Recordset object, where you can only use MoveNext to move.

Here are the possible values for the LockType argument:

•  adLockReadOnly—The default; read-only.

•  adLockPessimistic—Pessimistic locking, record by record.

•  adLockOptimistic—Optimistic locking, record by record.

•  adLockBatchOptimistic—Optimistic batch updates.

Here are the possible values for the Options argument:

•  adCmdText—Provider should evaluate Source as a definition of a command.

•  adCmdTable—ADO should generate an SQL query to return all rows from the table named in Source.

•  adCmdTableDirect—Provider should return all rows from the table named in Source.

•  adCmdStoredProc—Provider should evaluate Source as a stored procedure.

•  adCmdUnknown—Type of command in the Source argument is not known.

•  adCommandFile—Record set should be restored from the file named in Source.

•  adExecuteAsync—Source should be executed asynchronously.

•  adFetchAsync—After the initial quantity specified in the CacheSize property is fetched, any remaining rows should be fetched asynchronously.

Here’s an example where we open a record set with the SQL statement “select * from students”:

adoRecordset.Open "select * from students", db, adOpenStatic,
 adLockOptimistic