Known issues for the ADODB Primary Interop Assembly (PIA) that is included with Visual Studio 2005 (910696)


The information in this article applies to:

  • Microsoft Visual Studio 2005 Professional Edition
  • Microsoft Visual Studio 2005 Standard Edition
  • Microsoft Visual Studio 2005 Team System Architect Edition
  • Microsoft Visual Studio 2005 Team System Developer Edition
  • Microsoft Visual Studio 2005 Team System Team Foundation:
  • Microsoft Visual Studio 2005 Team System Test Edition
  • Microsoft Visual Studio 2005 Express Edition

INTRODUCTION

This article describes the known issues for the ADODB Primary Interop Assembly (PIA) that is included with Microsoft Visual Studio 2005.

MORE INFORMATION

Differences in garbage collection between Microsoft Visual Basic 6.0 and Microsoft Visual Basic .NET

Major differences exist between garbage collection in Visual Basic 6.0 and in Visual Basic .NET. The primary difference is that Visual Basic 6.0 garbage collection is more aggressive than Visual Basic .NET garbage collection. With Visual Basic 6.0, as soon as an object instance falls out of scope, the object is immediately released. The same behavior does not occur with Visual Basic .NET or with ordinary .NET garbage collection. With .NET garbage collection, objects are released asynchronously.

This difference in garbage collection can have a large effect on your data access code when you move from Visual Basic 6.0 to Visual Basic .NET.

For example, an open ADODB Recordset object is closed when the object is reclaimed by garbage collection. Developers who have experience writing Visual Basic 6.0 code may rely on garbage collection semantics that will change when they migrate code to Visual Basic .NET. Because .NET garbage collection is asynchronous and non-deterministic, you may not see the changes even after basic testing occurs.

Some databases, such as Microsoft SQL Server 2000 databases, only support a single active result set per connection. If you have a firehose cursor open on a connection to SQL Server, that connection is blocked until the cursor is closed. By default, OLE DB providers will open additional connections to execute queries if the current connection cannot execute that query. Therefore, many ActiveX Data Objects (ADO) users are unaware of this limitation. These additional connections do not participate in connection pooling. Attempts to open additional connections when the blocked connection is participating in a transaction will fail.

We recommend that you review your Visual Basic 6.0 code, and explicitly close all Recordset objects and connections. Then, retest your code after you migrate your code to Visual Basic .NET.

This section lists three examples of this issue and code examples for each example.

Example 1: Additional connections open when you do not explicitly close Recordset objects

When you run the following code example in Visual Basic 6.0, only a single connection is required. This is true because the Recordset object that is created in the ExecuteQuery procedure is implicitly closed when the Recordset object falls out of scope. The code example uses the SQL Server @@SPID variable to represent the server process identifier that is used to execute the query. If you run the code, you will notice that the queries return the same value in the @@spid column. This result means that the queries were run on the same connection to the database.
Public Sub Main()
    Dim strConn As String
    Dim cn As ADODB.Connection
    
    strConn = "Provider=SQLOLEDB;Data Source=.;" & _
              "Initial Catalog=Northwind;Trusted_Connection=Yes;"
    
    Set cn = New ADODB.Connection
    cn.Open strConn
    
    cn.Properties("Multiple Connections").Value = False
    
    ExecuteQuery cn, "SELECT @@spid, * FROM Customers"
    ExecuteQuery cn, "SELECT @@spid, * FROM Customers"
End Sub

Private Sub ExecuteQuery(cn As ADODB.Connection, strSQL As String)
    Dim rs As ADODB.Recordset
    Set rs = cn.Execute(strSQL)
    MsgBox rs(0)
End Sub
If you use similar code in Visual Basic .NET, you will notice that the second Recordset object contains a different value for the @@spid column. This value means that the query was run on a different connection to the database.

This behavior is different because the Recordset object that was created in the ExecuteQuery procedure was not closed and will remain open until the .NET garbage collector cleans up the Recordset object. Garbage collection in the Microsoft .NET Framework occurs asynchronously. If the Recordset object has not been closed by the time that the ExecuteQuery procedure is called again, the SQL Server OLE DB provider will open a new connection to execute the second query.

If you add a call to the rs.Close command in the ExecuteQuery procedure, you make sure that the queries are executed on the same connection. You can also explicitly tell the SQL Server OLE DB provider not to open additional connections. To do this, add the following line of code immediately after you open the connection: 
cn.Properties("Multiple Connections").Value = False
This code causes the SQL Server OLE DB provider to throw an exception whenever the OLE DB provider would otherwise open additional connections.

Example 2: Problems occur when you work with transactions if you do not explicitly close Recordset objects

You cannot open additional connections when the blocked connection is participating in a transaction. When you run the following code example in Visual Basic 6.0, the code executes multiple queries on a single connection that has an open transaction. The code calls the following two functions:
  • GetCustomers
  • GetOrders
Each function opens a Recordset object.
Public Sub Main()
    Dim cn As ADODB.Connection
    Set cn = New ADODB.Connection
    cn.Open "Provider=SQLOLEDB;Data Source=.;" & _
            "Initial Catalog=Northwind;Trusted_Connection=Yes;"
    
    cn.BeginTrans
    
    GetCustomers cn
    GetOrders cn
    
    cn.RollbackTrans
    cn.Close
End Sub

Public Sub GetCustomers(Connection As ADODB.Connection)
    Dim rs As ADODB.Recordset
    Set rs = Connection.Execute("SELECT CustomerID, CompanyName FROM Customers")
End Sub

Public Sub GetOrders(Connection As ADODB.Connection)
    Dim rs As ADODB.Recordset
    Set rs = Connection.Execute("SELECT OrderID, CustomerID FROM Orders")
End Sub
The Recordset object that is created by calling the GetCustomers function is implicitly closed at the end of that function call in Visual Basic 6.0. This behavior is described in the "Example 1: Additional connections open when you do not explicitly close Recordset objects" section. However, that Recordset object may not be closed by the time that your code calls the GetOrders function in Visual Basic .NET. Therefore, the current connection to SQL Server has an open result set with customer information. Because the current connection is busy, retrieving the results for the query in the GetOrders function requires a new implicitly created connection. However, you cannot create an implicit connection as long as a transaction is open. Therefore, the code will fail, and you will receive the following error message:
Cannot create new connection because in manual or distributed transaction mode.
To resolve this problem, explicitly close the Recordset objects that were created by using the GetCustomers and GetOrders functions.

Example 3: Problems occur when you implicitly create and then abandon Recordset objects

By default, the Execute method of the Connection and Command objects implicitly creates and returns a new Recordset object. In Visual Basic 6.0, if this Recordset object is not maintained in an object variable, the Recordset object falls out of scope and is immediately closed. Therefore, the following code example runs successfully in Visual Basic 6.0. However, you will receive the error message that is mentioned in the "Example 2: Problems occur when you work with transactions if you do not explicitly close Recordset objects" section if the code is migrated to Visual Basic .NET.
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
cn.Open "Provider=SQLOLEDB;Data Source=.;" & _
        "Initial Catalog=Northwind;Trusted_Connection=Yes;"

cn.BeginTrans

cn.Execute "SELECT CustomerID, CompanyName FROM Customers"
cn.Execute "SELECT OrderID, CustomerID FROM Orders ORDER BY CustomerID"

cn.RollbackTrans
cn.Close
In this code example, the calls to the Execute method of the Connection object implicitly return Recordset objects. Garbage collection in Visual Basic 6.0 immediately closes each Recordset object because the return value is not stored in a variable. Garbage collection is not as aggressive in Visual Basic .NET. The Recordset object that contains customer information is still open when the call to query the database for order information occurs. Therefore, you receive the error message that is mentioned in the "Example 2: Problems occur when you work with transactions if you do not explicitly close Recordset objects" section.

To resolve this problem, pass the adExecuteNoRecords value from the ExecuteOptionsEnum value in the Options parameter of the Execute method. When you do this, you can indicate that the Execute method should not return a Recordset object as illustrated in the following code example.
cn.Execute "SELECT CustomerID, CompanyName FROM Customers", , _
           ADODB.ExecuteOptionsEnum.adExecuteNoRecords

Issue 2: We do not recommend the ADODB PIA for stress scenarios

We strongly discourage you from using the ADODB PIA under stress scenarios, such as in multiuser Microsoft ASP.NET or Microsoft COM+ components. When Microsoft test teams tested the ADODB PIA, the test teams found that the ADODB PIA fails under stress. If .NET data access code must perform reliably under stress, we strongly recommend that you write the code by using ADO.NET.

Issue 3: We do not recommend that you use the ADODB PIA in 64-bit mode

We strongly discourage you from using the ADODB PIA in 64-bit applications. The ADODB PIA has not been tested in 64-bit mode. Most 64-bit scenarios involve high-stress server-side components, such as ASP.NET or COM+. The ADODB PIA has known problems running under stress. The limited availability of 64-bit OLE DB providers also makes 64-bit mode less compelling for working with the ADODB PIA.

Issue 4: Failures occur when you use late-bound query execution

ADODB supports two forms of late-bound query execution. Late-bound query execution lets you use the IDispatch binding in COM to execute queries as if the queries were methods on a Connection object. ADODB supports the two following forms of late-bound query execution:
  • Create a "named" query by setting the Name property of the Command object.
  • Execute a stored procedure call.
You may experience problems with these forms when you migrate code from Visual Basic 6.0 to Visual Basic .NET.

The following examples illustrate these problems.

Example 1: If you create a second late-bound query that has the same name, the query fails

The following code example creates two late-bound queries that use the same value for the Name property of the Command object.
Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim strConn As String
    
'Open a new Connection.
strConn = "Provider=SQLOLEDB;Data Source=.;" & _
          "Initial Catalog=Northwind;Trusted_Connection=Yes"
Set cn = New ADODB.Connection
cn.Open strConn
    
'Create a Command object, and then set the Name property 
'to enable the Command object as a late-bound method call.
Set cmd = New ADODB.Command
cmd.CommandText = "SELECT COUNT(*) FROM Customers"
cmd.Name = "GetCount"
Set cmd.ActiveConnection = cn
    
'Execute the Command object as a late-bound method call on the Connection.
Set rs = New ADODB.Recordset
cn.GetCount rs
Debug.Print rs(0).Value
rs.Close
Set rs = Nothing
    
'Release the Command object.
Set cmd = Nothing
    
'Create a new Command object, and then set the Name property
'to enable the Command object as a late-bound method call.
Set cmd = New ADODB.Command
cmd.CommandText = "SELECT COUNT(*) FROM Orders"
cmd.Name = "GetCount"
Set cmd.ActiveConnection = cn
    
'Execute the Command object as a late-bound method call on the Connection object.
Set rs = New ADODB.Recordset
cn.GetCount rs
Debug.Print rs(0).Value
rs.Close
Set rs = Nothing
    
'Clean up.
cn.Close
In Visual Basic 6.0, as soon as the Command object is set to Nothing, the object is reclaimed by garbage collection. The Connection object is notified that the Command object has been destroyed. Therefore, when the second Command object is associated with the Connection object, no conflict exists.

When similar code is run in Visual Basic .NET, the Command object may not be reclaimed by garbage collection by the time that the second Command object is associated with the Connection object. Therefore, you may receive the following exception error message:
Object is already in collection. Cannot append.
To resolve this problem, manually disassociate the first Command object from the Connection object. To do this, explicitly call the cmd.ActiveConnection method when you clean up the Command object. To do this, use the following code example.
'Visual Basic 6.0 syntax
Set cmd.ActiveConnection = Nothing

'Visual Basic .NET syntax
cmd.ActiveConnection = Nothing

Example 2: If you call a late-bound query on a second connection, the query fails

The following code example performs the following tasks, in the order in which they are presented:
  1. Create two Connection objects.
  2. Create a Command object on each Connection object. Each Command object has the same value for the Name property.
  3. Execute each command by using late-bound query execution.
Dim strConn As String, strSQL As String
Dim cn1 As ADODB.Connection, cn2 As ADODB.Connection
Dim cmd1 As ADODB.Command, cmd2 As ADODB.Command
Dim rs1 As ADODB.Recordset, rs2 As ADODB.Recordset
    
strConn = "Provider=SQLOLEDB;Data Source=.;" & _
          "Initial Catalog=Northwind;Trusted_Connection=Yes;"
strSQL = "SELECT COUNT(*) FROM Customers"
    
'Open connections.
Set cn1 = New ADODB.Connection
Set cn2 = New ADODB.Connection
cn1.Open strConn
cn2.Open strConn
    
'Create commands so that the commands can be executed as late-bound methods.
Set cmd1 = New ADODB.Command
Set cmd2 = New ADODB.Command
cmd1.CommandText = strSQL
cmd2.CommandText = strSQL
cmd1.Name = "GetCount"
cmd2.Name = "GetCount"
Set cmd1.ActiveConnection = cn1
Set cmd2.ActiveConnection = cn2
    
'Execute queries as late-bound methods.
Set rs1 = New ADODB.Recordset
Set rs2 = New ADODB.Recordset
cn1.GetCount rs1
cn2.GetCount rs2
MsgBox rs1(0).Value
MsgBox rs2(0).Value
    
'Clean up.
rs1.Close
rs2.Close
cn1.Close
cn2.Close
This code example succeeds when you use Visual Basic 6.0. However, this code example fails when you use Visual Basic .NET. This failure occurs because of the following reasons:
  • How ADODB prepares these queries as late-bound methods
  • Assumptions that the Visual Basic .NET compiler makes about these methods
When you set the ActiveConnection property of the Command object, ADODB checks to see whether the Command object has the Name property set. If the Name property is set, ADODB prepares the query so that the query can be executed as a late-bound method on the Connection object. In this example, the two Command objects have the same value for the CommandText property. However, this is a very simplistic example. Although the Command objects have the same value for the Name property, the Command objects could have CommandText values that would execute very different queries. Therefore, ADODB generates unique method signatures for the late-bound methods.

The Visual Basic .NET compiler does not make this differentiation. On the first call to the "GetCount" query, the Visual Basic .NET compiler caches the method signature in case another call is made to a GetCount method on a Connection object. When the code calls the second "GetCount" query, Visual Basic .NET reuses the cached method signature for the second GetCount method. Because ADODB generates unique method signatures, the call to the second late-bound query fails.

No workaround exists for this scenario.

Issue 5: You can set some ADODB Variant data types to String data types

The ADODB object model lets you set some properties either to strings or to other ADODB objects. For example, the ActiveConnection property of the Recordset object appears in the Visual Basic 6.0 Object Browser as a Variant data type and can be set to a Connection object or a connection string.

If you have created your own object and want to support this functionality, you must create separate property accessors. To do this, use code that is similar to the following code example.
Public Property Let ActiveConnection (ByVal value As String)
    'Add logic here.
End Property

Public Property Set ActiveConnection (ByRef value As Object)
    'Add logic here.
End Property
The .NET Framework is somewhat stricter and does not allow for multiple property accessors with different data types. The ADODB PIA lets you set the ActiveConnection property of the Recordset object to a Connection object. If you want to set the ActiveConnection property to a string, you must use the let_ActiveConnection method as shown in the following code example.
Dim strConn As String
Dim rs As ADODB.Recordset
    
strConn = "Provider=SQLOLEDB;Data Source=.;" & _
          "Initial Catalog=Northwind;Trusted_Connection=Yes;"
rs = New ADODB.Recordset
rs.let_ActiveConnection(strConn)
The same approach is required when you set the following properties:
  • The Source property of the Recordset object
  • The ActiveConnection property of the Command object
These properties accept objects. To set these properties to strings, you must use the corresponding let_MethodName method.

Issue 6: An InvalidCastException exception occurs when you call the Parameters.Append method

The ADODB PIA that is included with Microsoft Visual Studio .NET 2003 and Visual Studio 2005 has a known problem that occurs when you call the Parameters.Append method together with a Parameter object that was created by using the default constructor.

The following code example will cause an InvalidCastException exception.
Dim cmd As ADODB.Command
Dim p As ADODB.Parameter

cmd = New ADODB.Command
cmd.CommandText = "SELECT CustomerID FROM Orders WHERE OrderID = ?"

p = New ADODB.Parameter
p.Name = "@OrderID"
p.Type = ADODB.DataTypeEnum.adInteger
p.Value = 10248

cmd.Parameters.Append(p)
To work around this problem, create your Parameter objects by using the CreateParameter method of the Command object, as illustrated by the following code example.
Dim cmd As ADODB.Command
Dim p As ADODB.Parameter

cmd = New ADODB.Command
cmd.CommandText = "SELECT CustomerID FROM Orders WHERE OrderID = ?"

p = cmd.CreateParameter()
p.Name = "@OrderID"
p.Type = ADODB.DataTypeEnum.adInteger
p.Value = 10248

cmd.Parameters.Append(p)

Issue 7: You experience problems working with components that expect ADO 2.8 interfaces

The ADODB PIA that is included with Visual Studio 2005 is the same component that was included with Visual Studio .NET 2003 and was built by using the Microsoft .NET Framework 1.1. The ADODB PIA was built to interact with ADO 2.7 interfaces and has not been updated to work with ADO 2.8 interfaces.

Therefore, attempts to use the ADODB PIA together with components that expose ADO 2.8 interfaces will fail. This scenario is not supported with the ADODB PIA.
Modification Type:MajorLast Reviewed:12/14/2005
Keywords:kbinfo KB910696 kbAudDeveloper kbAudITPRO
©2004 Microsoft Corporation. All rights reserved.