Does con.execute("Insert blah") open a recordset on the SQL server?

[Iliemoo]

Hi all,
I've been getting "Transaction cannot have multiple recordsets with this cursor type. Change the cursor type, commit the transaction, or close one of the recordsets." when I run my asp pages. I am using adodb connection with transcation. I did a very through check to make sure every rs that is opened is closed before I open another and the error occurs on the lines where cnn.execute("some update or delete SQL statements)are. This makes sense for one occasion when a DELETE statment has sub-select in it. So I closed all the rs before the cnn.execute("Delete..&quot and then the page worked fine. But what about just pure INSERT statment without subselects? I can't really think of reason why it is giving the same error? Please help.

 

[fhlee]

Wanna post your codes here?

~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

 

[Iliemoo]

it looks something like this, thank you:


Set cn = Server.CreateObject("ADODB.Connection&quot
Set rs = Server.CreateObject("ADODB.Recordset&quot


cn.CommandTimeout = 300
cn.Open DBConnectString
cn.BeginTrans

rs.open "Select * from table1", cn

while not rs.eof
rs.close
set rs = nothing
cn.Execute "INSERT INTO table2 VALUES(" & rs("fld1&quot& &quot"
'Error occurs on this line:
'Transaction cannot have multiple recordsets with this cursor type.
'Change the cursor type, commit the transaction, or close one of the recordsets
rs.movenext
wend

rs.Close
set rs = nothing
cn.commit

 

[pjames]

'The connection will return a Closed RecordSet only If the Query is a non row returning execution. Other wise it returns a open recordset. VALUES(" & rs("fld1&quot& &quot" Will also retun a null since the rs.close was called prior to the request of the field Also The MoveNext will Cause a error.

'If you do not need transaction go this route.

Dim objRecordSet
Set objRecordSet = Server.CreateObject("ADODB.RecordSet&quot
objRecordSet.ActiveConnection = DBConnectString
objRecordset.CursorLocation = 3
objRecordset.LockType = 3
objRecordset.CursorType = 0
objRecordset.Source = "Select * from table1",

Dim objCommand
Set objCommand = Server.CreateObject("ADODB.Command&quot
objCommand.ActiveConnection = objRecordSet.ActiveConnection
objCommand.CommandTimeout = 300

objRecordset.Open
Do While (Not objRecordset.EOF)
objCommand.CommandText = "INSERT INTO table2 VALUES(" & rs("fld1&quot& &quot"
objCommand.Execute
Loop
objRecordSet.Close
Set objRecordSet = Nothing
Set objCommand = Nothing

'Else

Dim objRecordSet
Set objRecordSet = Server.CreateObject("ADODB.RecordSet&quot
objRecordSet.ActiveConnection = DBConnectString
objRecordset.CursorLocation = 3
objRecordset.LockType = 3
objRecordset.CursorType = 0
objRecordset.Source = "Select * from table1",

Dim objConnection
Set objConnection = Server.CreateObject("ADODB.Connection&quot
objConnection.Open DBConnectString
objConnection.CommandTimeout = 300
objConnection.BeginTrans

Dim strRecordSetValue()
Redim strRecordSetValue(0)

objRecordset.Open
Do While (Not objRecordset.EOF)
ReDim Preserve strRecordSetValue(UBound(strRecordSetValue) + 1)
strRecordSetValue(UBound(strRecordSetValue) + 1) = objRecordSet.Fields("fld1&quot
objRecordset.MoveNext
Loop

objRecordset.Close
Set objRecordSet = Nothing

Dim i
For i = 1 To UBound(strRecordSetValue)
objConnection.Execute = "INSERT INTO table2 VALUES(" & strRecordSetValue(i) & &quot"
Next

objConnection.CommitTrans
objConnection.Close
Set objConnection = Nothing

'I have not tested this. So let me know how this works out for you.

 

[Iliemoo]

Oh my god, I am so sorry, I posted the wrong code. The rs.close and set = nothig line should be ignored in the while loop. However, using an array to hold the rs values then close the rs before insert is a fantastic idea, I will give it a try!

 

[codestorm]

And furthering the use of an array there:

objRecordset.Open
if Not (objRecordset.BOF and objRecordset.EOF)
strRecordSetValue = objRecordSet.GetRows
end if
objRecordset.Close

codestorm
Newbie Life Member.
Fire bad. Tree pretty. - Buffy
<insert witticism here>