Web Analytics Made Easy -
StatCounter i get error when im doing add record in a loop by using BeginTran. - CodingForum

Announcement

Collapse
No announcement yet.

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

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

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

    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"
    %>
    Last edited by NinjaTurtle; Feb 19, 2004, 11:51 PM.
    Thanks.
    =====================================================
    From NinjaTurtle
    ++http://ohmygoh.blogspot.com|http://technology.ohmygoh.com++

  • #2
    See if this article helps you any:
    http://support.microsoft.com/default...b;en-us;272358
    Check out the Forum Search. It's the short path to getting great results from this forum.

    Comment


    • #3
      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.
      This is in spanish when you're not looking.

      Comment

      Working...
      X