"Transaction cannot have multiple recordsets with this cursor type." Error

Question

I am getting the following error in my classic asp application:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

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

i am working on migrating the code from Oracle to SQL Server 2008, and this is an issue that i keep seeing here and there, all through out the application.

can't seem to find any fixes for it.

this particular case in this block of code: (i changed the selects to make them shorter)

Set MyConn  = Server.CreateObject("ADODB.Connection") 
Call OpenORPSConnect(MyConn)
ql = "Select username from mytable"
set rs = MyConn.Execute(sql)
if not rs.EOF then username = rs(0)
if username = "" then username = theUser
rs.close()
set rs = nothing

MyConn.BeginTrans()

sql = "Select someReport from MyTable"
set rs = MyConn.Execute(sql)

do while not rs.EOF
TIMESTAMP       = rs("TIMESTAMP")
rev      = rs("REV")

select case whatChange
    case "Target date"
       sql = "Insert into " & caJustTable & _
                 " (TEXT, TIMESTAMP, CURRENTFLAG)" & _
            " Values ( Text& "','" & COPY_TS & "', 'Y')""
MyConn.Execute(sql) 
    end select

sql = "update table, set this to that"
MyConn.Execute(sql) <-------- error happens here sometimes....

end if
rs.movenext
loop

rs.close()
set rs = nothing

Answer 1

Since this was answered in the comments I wanted to turn it into a better answer

Your problem seems to be the MyConn.BeginTrans() has no MyConn.CommitTrans() or MyConn.RollbackTrans() after the Insert Statement in your select case; therefore, an error is thrown when you try to update the data. If you commit or Rollback after that insert execute then your next execute should work just fine. The fact that the MyConn.BeginTrans() is before a simple select statement you might consider moving it after the select.

I would do something like this (if you want to use transactions):

'MyConn.BeginTrans()

sql = "Select someReport from MyTable"
set rs = MyConn.Execute(sql)

do while not rs.EOF
   TIMESTAMP = rs("TIMESTAMP")
   rev = rs("REV")

  select case whatChange
    case "Target date"
        MyConn.BeginTrans()
        sql = "Insert into " & caJustTable & _
                 " (TEXT, TIMESTAMP, CURRENTFLAG)" & _
            " Values ( Text& "','" & COPY_TS & "', 'Y')""
        MyConn.Execute(sql) 
        MyConn.CommitTrans() 'You'll want to validate your data inserts properly before committing
    end select

    MyConn.BeginTrans()
    sql = "update table, set this to that"
    MyConn.Execute(sql) <-------- error happens here sometimes....
    MyConn.CommitTrans()'You'll want to validate your data inserts properly before committing

  end if
  rs.movenext
loop

rs.close()
set rs = nothing

Transactions are generally used for inserting/updating or deleting data. Since you commented you don't know why the BeginTrans() statement is there then yes you could remove it altogether but I would recommend reading up on transactions and making sure you don't need it after your insert and update statements which occur later in the code.

Here is a reference for SQL transactions:

http://www.firstsql.com/tutor5.htm