PRB: Problems Reusing ADO Command Object on Multiple Recordsets

Article translations Article translations
Article ID: 197449 - View products that this article applies to.
This article was previously published under Q197449
Expand all | Collapse all

SYMPTOMS

When you use a single command object to open multiple recordset objects with one or more of those recordset objects open at the same time, you encounter unexpected behavior. This is most noticeable when you open stored procedures with output or return parameters.

CAUSE

Consider the following ActiveX Data Objects (ADO) code, which is written in Visual Basic for Applications:

Note You must change UID=<username> and PWD=<strong password> to the correct values before you run this code. Make sure that UID has the appropriate permissions to perform this operation on the database.ion on the database.
   Dim cn As New ADODB.Connection
   Dim cm As New ADODB.Command
   Dim rs1 As New ADODB.Recordset
   Dim rs2 As ADODB.Recordset
   Dim rs3 As New ADODB.Recordset

   cn.Open "DSN=Pubs;UID=<username>;PWD=<strong password>;"

   On Error Resume Next
   cn.Execute "drop procedure dbo.sp_AdoTest"
   cn.Execute "create proc sp_AdoTest ( @InParam int ) as " & _
              "select * FROM Authors WHERE State <> 'CA' " & _
              "return @InParam + 10"

   cn.Execute "drop procedure dbo.sp_AdoTest2"
   cn.Execute "create proc sp_AdoTest2  as " & _
              "select * FROM Authors WHERE State <> 'CA' " & _
              "return 10"

   Set cm.ActiveConnection = cn
   cm.CommandType = adCmdStoredProc
   cm.CommandText = "sp_AdoTest"
   cm.Parameters.Refresh
   cm.Parameters(1).Value = 10
   Set rs1 = cm.Execute

   Debug.Print cm.Parameters.Count
				
At this point, the Parameters collection for the command object has two objects, and you can reference them as you would normally. However, consider the following code, which destroys (resets) the Parameters collection:
cm.CommandText = "sp_AdoTest2"
   cm.Parameters.Refresh
   Set rs1 = cm.Execute

   Debug.Print cm.Parameters.Count
				
At this point, the Parameters collection has been reset to contain only one Parameter object. However, the original Recordset (rs1), which has not been closed or released, still has a reference to an internal buffer that stores the value of each parameter that was originally created. When the original recordset is closed, it attempts to release the buffer. However, the second recordset's Parameters collection may be using the same memory location for its own buffers, and if both recordsets are trying to release the same memory location, you may receive an Access Violation.
   rs2.Close
   rs1.Close

   cn.Close

				
The Access Violation has been confirmed to occur (at random intervals) when using a forward only cursor with a parameterized stored procedure using the OLE DB Provider for ODBC to the SQL Server ODBC Driver. It may occur with other ODBC Drivers or OLE DB Providers as well.

The Access Violation appears to have been fixed in the MDAC 2.1 SP2 release. However, recycling the same Command Object for multiple, concurrently open Recordsets is not a safe practice. Any savings in performance or memory allocation you receive in minimizing the number of created objects will not be compensated for by increased risk of your code causing a failure within ADO. The Command Object is not designed or intended for this kind of utilization.

STATUS

This behavior is by design.

Properties

Article ID: 197449 - Last Review: March 14, 2005 - Revision: 2.1
APPLIES TO
  • Microsoft ActiveX Data Objects 1.0
  • Microsoft ActiveX Data Objects 1.5
  • Microsoft ActiveX Data Objects 2.0
  • Microsoft ActiveX Data Objects 2.1
  • Microsoft ActiveX Data Objects 2.1 Service Pack 2
  • Microsoft ActiveX Data Objects 2.5
  • Microsoft ActiveX Data Objects 2.6
  • Microsoft ActiveX Data Objects 2.7
Keywords: 
kbdatabase kbprb KB197449
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com