ADO tips: Firehose cursors and raising errors from stored procs

Tweak your ADO code for more efficiency with these two VB tips. Find out how to use a firehose cursor and how to raise errors from stored procedures.

Working with ActiveX Data Objects (ADO) is a core part of many VB6 applications. Try these two ADO tips to tweak your VB code. The first walks you through creating firehose cursors; the second looks at raising errors from stored procedures.

Speed data access with firehose cursors
Many applications use ADO to retrieve data from a database. Using firehose cursors to retrieve the data can significantly increase application access speed.

Firehose cursors are server-side, forward-only, read-only cursors. When a firehose cursor is used, the records are streamed directly from the server to the client and put into the Recordset object. Firehose cursors are particularly useful for read-only data or data that will be processed immediately. The code in Listing A generates a firehose cursor.

When using firehose cursors, it's best to retrieve all the records from the Recordset and close the Recordset as quickly as possible. This will free up resources on the client and the server.

Under ADO.NET, you can use a DataReader object to achieve similar results because it also operates as a forward-only, read-only cursor, as shown in Listing B. The DataReader has the same restrictions as an ADO firehose cursor: The rows should be processed and the DataReader should be closed as quickly as possible to free up resources and enable the connection to be used for other purposes.

Raise errors from stored procedures with ADO
Applications often use ActiveX Data Objects (ADO) and stored procedures to read and write data to a SQL Server database. When doing this, you may need to raise errors from the stored procedures. To implement this correctly, the ADO Command must be called in a specific way. Calling the RAISERROR statement in the stored procedure, as shown in Listing C, raises errors from stored procedures.

Normally, errors raised in the stored procedure are returned in the ADO Connection object’s DBErrors collection. However, these errors are not trapped unless all of the following conditions are met:
  • ·        An ADO Command object is used to execute the procedure.
  • ·        The adExecuteNoRecords flag is passed to the Execute method.
  • ·        The stored procedure has output parameters or returns a Recordset.

If you use the VB code below to execute a stored procedure that has no output parameters and returns no records, any errors raised by the stored procedure will be added to the Connection object’s DBErrors collection:
Call Command.Execute(, , adExecuteNoRecords)

If Command.ActiveConnection.Errors.Count > 0 Then
    'Handle Error
End If

However, if the stored procedure returns records or has output parameters, it must be called without the adExecuteNoRecords flag. Even if you do not need the records to be returned, it should be called without the flag, or the errors will not be trapped:
Set Recordset = Command.Execute()

If Command.ActiveConnection.Errors.Count > 0 Then
    'Handle Error
End If

Knowing the proper settings to ensure that ADO traps errors correctly can save many hours of troubleshooting your applications.

Editor's Picks

Free Newsletters, In your Inbox