i get error when im doing add record in a loop by using BeginTran.

[NinjaTurtle]

Dear,

i get error when im doing add record in a loop by using BeginTran & CommitTran.

The error mssg as:
Microsoft OLE DB Provider for SQL Server (0x80004005)
Cannot create new connection because in manual or distributed transaction mode.

i have to update 4 tables in a single process,
1) table1 (Exam Master)
2) table2 (Exam Detail)
3) table3 (Exam Question)
4) table4 (Exam Sub Question)

Sample code:

<%
Const adOpenKeyset = 1
Const adLockPessimistic = 2
Const adCmdText = &H0001

strconn = ITHD_Databasepath
set objconn = server.createobject("adodb.connection")
objconn.open strconn
SQLCL = "SELECT * FROM table1"
objConn.BeginTrans
set rsCL = server.createObject("ADODB.Recordset")
rsCL.Open SQLCL, objConn, adOpenKeyset, adLockPessimistic, adCmdText
'Add record.
With rsCL
AddNew
.fields ("Name")= "test"
...
.fields ("TimeStamp")= now()
.Update
end With
rsCL.close
set rsCL=Nothing

' Run the Exam TITLE.
'================================
strTitle = "SELECT * FROM table3"
set rsTitle=objConn.Execute(strTitle)
Do While Not rsTitle.EOF
TitleCode=Trim(rsTitle("titlecode"))
counter = counter +1
counter2 =0
DetailCount = "SELECT * FROM table4 where fTitleCode='"&TitleCode&""
set rsDetailCount=objConn.Execute(DetailCount)
IF NOT rsDetailCount.eof THEN
Do While Not rsDetailCount.eof
DetailCode=Trim(rsDetailCount("Detcode"))
counter2 = counter2 +1
' Run TITLE DETAIL. Add record to exam detail[add Title only]
'====================================
strCLDet = "SELECT * FROM table4"
set rsCLDet = server.createObject("ADODB.Recordset")
rsCLDet.Open strCLDet, objConn, adOpenKeyset, adLockPessimistic, adCmdText
'Add record.
With rsCLDet
.AddNew
.fields ("ExamNo")= CLNumber
.fields ("TitleCode")= TitleCode
.fields ("DetCode")= DetailCode
.Update
end With
rsCLDet.close
set rsCLDet=Nothing
rsDetailCount.Movenext
Loop

ELSE
' Run exam TITLE. [add Title only]
'====================================
strCLTit = "SELECT * FROM table2"
set rsCLTit = server.createObject("ADODB.Recordset")
rsCLTit.Open strCLTit, objConn, adOpenKeyset, adLockPessimistic, adCmdText
'Add record exam title.
With rsCLTit
.AddNew
.fields ("ExamNo")= CLNumber
.fields ("FTitleCode")= TitleCode
.Update
end With
rsCLTit.close
set rsCLTit=Nothing

END IF
rsTitle.MoveNext
Loop
objConn.CommitTrans
objconn.Close
set objconn=Nothing
response.redirect "done.asp"
%>

[Roy Sinclair]

See if this article helps you any:

http://support.microsoft.com/default.aspx?scid=kb;en-us;272358

[BigDaddy]

I use BeginTrans on a few of my pages...but not in the exact way you've used it.

A sample of mine:

Set DataConn = Server.CreateObject("ADODB.Connection")
DataConn.ConnectionString = CONN_STRING

DataConn.Open
DataConn.BeginTrans

DataConn.execute MYSQL
DataConn.execute MYSQL2
If DataConn.Errors.Count > 0 then
For each error in DatConn.Errors
Response.write Error.number
next
DataConn.RollbackTrans
else
DataConn.CommitTrans
end if

All I'm doing is making sure both of the insert statements execute, as I have to put info on a record into two different tables or neither at all.

I think it has something to do with you creating an object inside the transaction. Try creating the objects outside of it. You can then open them inside.