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"
%>
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"
%>
Comment