Previous | Index | Next 

[PRB] The Execute method of the ADODB.Connection object can create a spurious Recordset object

COM Interop has a weird and interesting bug might manifests itself when migrating calls to the Execute method of the ADODB.Connection object:

        ' open an ADODB connection and then a recordset
        Dim cn As New ADODB.Connection
        cn.Open(myConnectionString)
        cn.BeginTrans()
        Dim rs As New ADODB.Recordset
        rs.Open("SELECT * FROM Products", cn, ADODB.CursorTypeEnum.adOpenKeyset)
        ' insert a new record
        cn.Execute("INSERT Orders (OrderId) VALUES (1234)") '  <<< error!

This code - which works beautifully under VB6 - stops with the following error when it runs under VB.NET

fhloinsert error=Transaction cannot have multiple recordset with this cursor type. Change the cursor type, commit the transaction, or close one of the recordsets.

According to this Microsoft KB Article, this error message appears because a ForwardOnly-Readonly cursor can't live in a transaction with multiple recorsets. The problem is, the cn.Execute method doesn't create a FO-RO recordset (or any kind of recordset, for that matter). The ADODB documentation is clear on this point and, in fact, the VB6 code had worked fine for years.

When you see problems like this - a piece of VB6 code that accesses COM objects and that stops working when translated to VB.NET - then nearly always the problem lies in COM Interop. More precisely, it turns out that the cn.Execute does create a hidden recordset under .NET. The following code snippet proves this point:

        Dim rs As ADODB.Recordset = cn.Execute("INSERT Orders (OrderId) VALUES (1234)")
        Debug.WriteLine(rs IsNot Nothing)     '  <<< display "True"

Fortunately, the Execute method supports an option that allows you to specify that the SQL command doesn't produce a resultset. So you can avoid this very subtle problem by means of a minor fix to the original VB6 code or the migrated VB.NET code:

        cn.Execute("INSERT Orders (OrderId) VALUES (1234)", , 
             ADODB.ExecuteOptionEnum.adExecuteNoRecords) 

Notice that it is recommended that you always specify the adExecuteNoRecords in Execute methods that do not return a Recordset, even if your code doesn't live inside a transaction and therefore doesn't raise a runtime error. By doing so you avoid that a Recordset be created and later destroyed, which makes your code slightly faster.

Specifying the adExecuteNoRecords option was good VB6 programming practice that turns to be even better when you migrate the code to VB.NET.

 

Previous | Index | Next