PRB027 Microsoft OLE DB Provider for ODBC Drivers error '8004d01d'
Cannot start transaction because more than one hdbc is in use.
Error Details:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers error
Associated Error Number:
8004d01d
Cause/Symptoms:
You are using the ActiveX Data Objects to manipulate a database programmatically. You are using the BeginTrans(), CommitTrans(), RollbackTrans() methods to enforce a transaction within the database.
For example, your code may look like this:
<%Dim conn, rst
set conn = createobject("ADODB.Connection")
conn.open
conn.BeginTrans
set rst = conn.execute(sqlrst)
If Not(rst.eof) Then
'do something
rst.Close
End If
rs = conn.execute(sql)
rs = conn.execute(sql2)
rs = conn.execute(sql3)
conn.CommitTrans
%>
Fix/Workaround:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnproasp/html/tipsforworkingwithcursors.asp
The problem here is that the connection is in forward-scrolling firehose mode, so it cannot also be involved in a batch mode. The error returned in the Errors collection from the provider will indicate that the transaction could not be started. For example, with the ODBC Provider against Microsoft SQL Server, you will get the error "Cannot start transaction because more than one hdbc is in use".
To fix the above sample:
<%Dim conn, rst
set conn = createobject("ADODB.Connection")
conn.open
conn.BeginTrans
set rst = createobject("ADODB.Recordset")
rst.open sqlrst, conn
If Not(rst.eof) Then
'do something
End If
rst.close
set rst = nothing
conn.execute sql
conn.execute sql2
conn.execute sql3
conn.CommitTrans
conn.close
%>
More Information:
Transactions thread from forum
http://www.aspemporium.com/aspEmporium/forum/display_message.asp?mid=622