After migrating a piece of VB6 code to VB.NET, one of our customers found himself facing a weird problem. After stripping down all unneeded code, all boiled down to these statements
' open an ADODB connection and then a recordset
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
rs.Open("SELECT * FROM Products", cn, _
' 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.
It turned 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.