VB Migration Partner

KNOWLEDGE BASE - Database and data-binding


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 




Follow Francesco Balena on VB6 migration’s group on

LinkedIn





Read Microsoft Corp’s official case study of a VB6 conversion using VB Migration Partner.




Code Architects and its partners offers remote and onsite migration services.

More details




Subscribe to our free newsletter for useful VB6 migration tips and techniques.

newsletter



To learn more about your VB6 applications, run VB6 Analyzer on your source code and send us the generated text file. You will receive a detailed report about your VB6 applications and how VB Migration Partner can help you to quickly and effectively migrate it to .NET.

Get free advice



A fully-working, time-limited Trial Edition of VB Migration Partner allows you to test it against your actual code

Get the Trial




The price of VB Migration Partner depends on the size of the VB6 application, the type of license, and other factors

Request a quote




Migrating a VB6 application in 10 easy steps

Comparing VB Migration Partner with Upgrade Wizard

Migration tools: Feature Comparison Table

All whitepapers