Results 1 to 38 of 38

Thread: how to use BeginTrans, CommitTrans, & RollBackTrans?

  1. #1

    Thread Starter
    Addicted Member jeanette_db's Avatar
    Join Date
    Oct 2005
    Location
    DC, Phil
    Posts
    215

    Talking how to use BeginTrans, CommitTrans, & RollBackTrans?

    hi...

    i just came across these methods... BeginTrans, CommitTrans and RollBackTrans... i really have no idea on how to use this..

    can anybody teach me how? or give me a simple program having these methods?

    i'm using vb6 and mysql...

    thanks .in advance... =)
    Last edited by jeanette_db; May 30th, 2006 at 01:44 AM.

  2. #2
    Fanatic Member r0ach's Avatar
    Join Date
    Dec 1999
    Location
    South Africa
    Posts
    722

    Re: how to use BeginTrans, CommitTrans, & RollBackTrans?

    This is for database transactions. Before you start writing data to the db, you can BeginTrans (from the db object). should you get an error during the data writing, you can RollbackTrans in your error handler. If you wrote all the data without errors, you CommitTrans.

    An example would be a bank. Say you want to transfer $100 from 1 account to another. The db deducts this $100 from the first account but suddenly, something goes wrong. Now there's $100 missing because the second account has not been credited. The transactions checks that everything is fine BEFORE making the actual changes to the db. If soething is not right, it doesn't make the changes.

    My work doesn't really require that I use transactions, so I haven't used it for a long time. But now that you mentioned it, I can think of 1 or 2 places I can utilize it.

    r0achâ„¢
    Don't forget to rate the post

  3. #3

    Thread Starter
    Addicted Member jeanette_db's Avatar
    Join Date
    Oct 2005
    Location
    DC, Phil
    Posts
    215

    Talking Re: how to use BeginTrans, CommitTrans, & RollBackTrans?

    i see... that's clear...

    can you give me a simple program with these methods?...
    i tried using it... but an error occurred...
    it's 'no transaction active...' blah blah blah....

    can you help me with this?

  4. #4
    Fanatic Member r0ach's Avatar
    Join Date
    Dec 1999
    Location
    South Africa
    Posts
    722

    Re: how to use BeginTrans, CommitTrans, & RollBackTrans?

    OK, well...

    I can't quite remember, but I think you need to use it in an ActiveX DLL, and you need to set the dll's MTSTransactionMode to RequiresTransaction, NewTransaction or RequiresNewTransaction depending on your needs. Also, you would have to write it to be used in Microsoft Transaction Server. As far as I can remember.

    r0achâ„¢
    Don't forget to rate the post

  5. #5
    PowerPoster Simply Me's Avatar
    Join Date
    Aug 2003
    Posts
    2,732

    Re: how to use BeginTrans, CommitTrans, & RollBackTrans?

    you can do something like this
    VB Code:
    1. oConn.BeginTrans
    2.     sSQL = "INSERT INTO tblCashSales (TransactionID,Cashpayment) VALUES " & _
    3.                 "('" & txtTransactionID.Text & " ','" & CCur(txtAmountDue.Text) & "')"
    4.     oConn.Execute sSQL
    5.    
    6.     For i = 1 To lstStocks.ListItems.Count
    7.       sSQL = "INSERT INTO tblSales (transactionID, Code,QuantitySold," & _
    8.                 "SellingPrice,Discount,DateSold,TimeSold) VALUES " & _
    9.                   "('" & txtTransactionID.Text & _
    10.                   "','" & lstStocks.ListItems.Item(i).Text & _
    11.                   "','" & lstStocks.ListItems.Item(i).SubItems(2) & _
    12.                   "','" & lstStocks.ListItems.Item(i).SubItems(3) & _
    13.                   "','" & lstStocks.ListItems.Item(i).SubItems(4) & _
    14.                   "','" & Date & _
    15.                   "','" & Format(Time, "hh:mm:ss AM/PM") & "')"
    16.       oConn.Execute sSQL
    17.      Next
    18.    
    19. oConn.CommitTrans
    To give is always to be NOBLE...
    To received is always to be BLESSED....
    Each day strive to be NOBLE
    Each day strive to be BLESSED

    If this post has helped you. Please take time to rate it.

    >=|+|=< Simply Me >=|+|=<

    ----------------------------------------
    Connection Strings | Number Only in Textbox | Splash Screen with Progress Bar | Printing to 1/2 of perforated bond paper |
    Freeze 2005 DataGridView Column

  6. #6
    Fanatic Member r0ach's Avatar
    Join Date
    Dec 1999
    Location
    South Africa
    Posts
    722

    Re: how to use BeginTrans, CommitTrans, & RollBackTrans?

    I haven't quite looked at this, but here's a link

    r0achâ„¢
    Don't forget to rate the post

  7. #7
    PowerPoster Simply Me's Avatar
    Join Date
    Aug 2003
    Posts
    2,732

    Re: how to use BeginTrans, CommitTrans, & RollBackTrans?

    BTW, Begintrans and commitrans are usually used when there are more than one sql statement that you would like to execute.
    To give is always to be NOBLE...
    To received is always to be BLESSED....
    Each day strive to be NOBLE
    Each day strive to be BLESSED

    If this post has helped you. Please take time to rate it.

    >=|+|=< Simply Me >=|+|=<

    ----------------------------------------
    Connection Strings | Number Only in Textbox | Splash Screen with Progress Bar | Printing to 1/2 of perforated bond paper |
    Freeze 2005 DataGridView Column

  8. #8

    Thread Starter
    Addicted Member jeanette_db's Avatar
    Join Date
    Oct 2005
    Location
    DC, Phil
    Posts
    215

    Talking Re: how to use BeginTrans, CommitTrans, & RollBackTrans?

    Quote Originally Posted by r0ach
    I haven't quite looked at this, but here's a link
    is it ok to use 'workspace' if im using ADO?

  9. #9
    Fanatic Member r0ach's Avatar
    Join Date
    Dec 1999
    Location
    South Africa
    Posts
    722

    Re: how to use BeginTrans, CommitTrans, & RollBackTrans?

    Let me check the link...

    r0achâ„¢
    Don't forget to rate the post

  10. #10

    Thread Starter
    Addicted Member jeanette_db's Avatar
    Join Date
    Oct 2005
    Location
    DC, Phil
    Posts
    215

    Talking Re: how to use BeginTrans, CommitTrans, & RollBackTrans?

    Quote Originally Posted by Simply Me
    BTW, Begintrans and commitrans are usually used when there are more than one sql statement that you would like to execute.
    how about rollbacktrans? what's used if there is only one sql statement to be executed or aborted? is it advisable to used committrans and rollbacktrans?
    AND...

    instead of assigning the sql statement to a variable before executing it:

    SQLstring = "insert into...."
    dbconnect.execute SQLstring

    i used this code (instead):

    con.execute "insert into..."

    then i placed an 'if else statement' to either abort or execute the statement... but the error "no transaction active...." occurred...
    what's wrong?

  11. #11
    Fanatic Member r0ach's Avatar
    Join Date
    Dec 1999
    Location
    South Africa
    Posts
    722

    Re: how to use BeginTrans, CommitTrans, & RollBackTrans?

    I think you should follow Simply Me's example. I'd just add some error handling:

    VB Code:
    1. On Error Goto Error_Handler
    2.  
    3.     oConn.BeginTrans
    4.     sSQL = "INSERT INTO tblCashSales (TransactionID,Cashpayment) VALUES " & _
    5.                 "('" & txtTransactionID.Text & " ','" & CCur(txtAmountDue.Text) & "')"
    6.     oConn.Execute sSQL
    7.    
    8.     For i = 1 To lstStocks.ListItems.Count
    9.       sSQL = "INSERT INTO tblSales (transactionID, Code,QuantitySold," & _
    10.                 "SellingPrice,Discount,DateSold,TimeSold) VALUES " & _
    11.                   "('" & txtTransactionID.Text & _
    12.                   "','" & lstStocks.ListItems.Item(i).Text & _
    13.                   "','" & lstStocks.ListItems.Item(i).SubItems(2) & _
    14.                   "','" & lstStocks.ListItems.Item(i).SubItems(3) & _
    15.                   "','" & lstStocks.ListItems.Item(i).SubItems(4) & _
    16.                   "','" & Date & _
    17.                   "','" & Format(Time, "hh:mm:ss AM/PM") & "')"
    18.       oConn.Execute sSQL
    19.      Next
    20.      
    21.      '// Everything is ok. Commit the changes
    22.      oConn.CommitTrans
    23.  
    24. Error_Handler:
    25.      '// An error occurred. Do not commit changes
    26.      oConn.RollbackTrans

    r0achâ„¢
    Don't forget to rate the post

  12. #12

    Thread Starter
    Addicted Member jeanette_db's Avatar
    Join Date
    Oct 2005
    Location
    DC, Phil
    Posts
    215

    Re: how to use BeginTrans, CommitTrans, & RollBackTrans?

    how can you rollback a transaction if has not yet been executed?
    dont you think there's something wrong with the code??

    in my program... i need to execute a statement first then if it has an error, the program will display the error and rollback the transaction...

    here's the code in unix-informix:
    begin work
    insert into x_mafmast values
    (grt_mafnmds.class, grt_mafnmds.te, grt_mafnmds.stock_num,
    grt_mafnmds.stock_cd, grt_mafnmds.ui, grt_mafnmds.stat,
    grt_mafnmds.srce, grt_mafnmds.std_pqty, grt_mafnmds.std_pui,
    grt_mafnmds.mfg, grt_mafnmds.pn_indic, grt_mafnmds.part_num,
    grt_mafnmds.desc_main, grt_mafnmds.desc_ext,
    grt_mafnmds.trans_date, 0, 0, 0, " ", " " , " ", 0,
    " "," ", 0, 0)
    if sqlca.sqlcode = notfound or status < 0 then
    error sqlca.sqlcode using "-<<<<", " has occured.(x_mafmast)"
    attribute(yellow,blink)
    sleep 3
    rollback work
    else
    commit work
    end if

  13. #13
    PowerPoster Simply Me's Avatar
    Join Date
    Aug 2003
    Posts
    2,732

    Re: how to use BeginTrans, CommitTrans, & RollBackTrans?

    Quote Originally Posted by jeanette_db
    how can you rollback a transaction if has not yet been executed?
    dont you think there's something wrong with the code??

    in my program... i need to execute a statement first then if it has an error, the program will display the error and rollback the transaction...
    You read what was posted in post #2 about the rollback and look at the edited sample in post#11 particularly the error handler part
    To give is always to be NOBLE...
    To received is always to be BLESSED....
    Each day strive to be NOBLE
    Each day strive to be BLESSED

    If this post has helped you. Please take time to rate it.

    >=|+|=< Simply Me >=|+|=<

    ----------------------------------------
    Connection Strings | Number Only in Textbox | Splash Screen with Progress Bar | Printing to 1/2 of perforated bond paper |
    Freeze 2005 DataGridView Column

  14. #14
    Fanatic Member r0ach's Avatar
    Join Date
    Dec 1999
    Location
    South Africa
    Posts
    722

    Re: how to use BeginTrans, CommitTrans, & RollBackTrans?

    I don't know anything UNIX related, but this is (sortof) what it will look like in vb:

    VB Code:
    1. On Error Goto Error_Handler
    2.  
    3. oConn.BeginTrans
    4.  
    5. '// funny statement goes here
    6. sSQL = "... statement ..."
    7. oConn.Execute sSQL
    8.  
    9. oConn.CommitTrans
    10. On Error Goto 0
    11. '// exit the sub/function here
    12.  
    13. Error_Handler:
    14. oConn.RollbackTrans
    15. '// notify user of error
    16.  
    17. '// end sub/function
    You don't need an If statement because if there's an error, the code will immediately go to the error handler label and start executing from there.

    Oh, I just thought of something else; If you have an error handler, and you get the message mentioned above, it might be that the error occurs between On Error and BeginTrans somewhere (if there's code). So you try to rollback a transaction that's not started yet.
    Last edited by r0ach; May 30th, 2006 at 03:56 AM.

    r0achâ„¢
    Don't forget to rate the post

  15. #15
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: how to use BeginTrans, CommitTrans, & RollBackTrans?

    what's used if there is only one sql statement to be executed or aborted?
    If there is only one SQL statement, is is effectively in a transaction anyway (either it all works, or it all fails), so you don't actually need to write any code to implement it.

  16. #16
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: how to use BeginTrans, CommitTrans, & RollBackTrans?

    To further what Si just said (and others as well)...

    Yes - ADO executes all statements in an IMPLIED transaction - if an error occurs the statement is rolled back (a SQL level error that is).

    The only reason to BEGINTRANS on the client side is because you are going to be executing a series of SQL statements from that client and want to insure that all of them go in cleanly. The banking example given describes that situation.

    There is huge danger in using BEGINTRANS on the client side. If for instance you have a MSGBOX in your error handler - before the ROLLBACKTRANS - then you leave an expensive "lock" on the table/DB waiting for the user to click OK. Also - the goal is to have transactions that last milliseconds - so take care when starting a TRANSACTION that might last for some time...

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  17. #17
    New Member
    Join Date
    Jun 2022
    Location
    Switzerland
    Posts
    12

    Re: how to use BeginTrans, CommitTrans, & RollBackTrans?

    Hello all,
    In the same frame as this question, here is a collateral issue. I am using Excel 365 and its VBA.
    I am trying to write a function which executes 4 SQL for each line in a text file, containing between 10 and 100 lines. I would like to wrap these 4 SQLs into ONE transaction, so they either pass or fail all together.
    Therefore, can I include BeginTrans and CommitTrans into a loop wrapping my 4 SQLs?
    Actually I tried this but it fails at the second iteration of the loop, which makes me think that one can use BeginTrans/CommitTrans only ONCE per procedure execution! Is this true??? Should I call a proc into my loop so that the whole BeginTrans/SQL1/SQL2/SQL3/SQL4/CommitTrans/IfErrorRollback gets executed only once per procedure call? Is this required?
    I hope my question is understandable.
    Last edited by stefano.gatto; Jun 15th, 2022 at 04:03 AM.

  18. #18
    Addicted Member
    Join Date
    Aug 2013
    Posts
    236

    Re: how to use BeginTrans, CommitTrans, & RollBackTrans?

    With DAO you could have nested transactions but I think ADO only allows one open transaction.
    If you have one global connection object that you use for everything and keep open while you're program is running you need to be even more especially careful that every begintrans is inevitably followed by a commit or a rollback.
    Also don't have any user interaction in the middle of a transaction or any call to Doevents.
    Basically you don't want the possibility for any other code to execute except what is explicitly between the begin and the commit/rollback.
    Don't put any calls to stuff like a modal form where user could wander off leaving a transaction open indefinitely.
    If you have background stuff going on in a timer that's, say, firing off emails to a list of people and updating the database then if the user later comes back and cancels the transaction, all the stuff that happened in the timer gets rolled back too.
    So the emails have been sent but there's no record of it.
    Last edited by vbrad; Jun 15th, 2022 at 04:17 AM.

  19. #19
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,415

    Re: how to use BeginTrans, CommitTrans, & RollBackTrans?

    Wow... a 16 year old thread revived.
    Talk about necromancy........

    Why in blazes would you need a loop for the SQL?
    You just need a Loop for the Lines of the Textfile
    Start Loop
    Read a Line from textfile
    Start the Transaction
    Concat all 4 SQL's into one single line, delimited with the Statement-Delimiter of your Database, and execute it.
    If No Error Then Commit Else Rollback
    End loop --> Exit Loop when EOF of TextFile
    Last edited by Zvoni; Jun 15th, 2022 at 04:53 AM.
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  20. #20
    Addicted Member
    Join Date
    Aug 2013
    Posts
    236

    Re: how to use BeginTrans, CommitTrans, & RollBackTrans?

    Crikey, I thought it was only asked yesterday!

  21. #21
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,871

    Re: how to use BeginTrans, CommitTrans, & RollBackTrans?

    @vbrad, you are correct, the thread was resurrected from the dead by stefano.gatto

  22. #22
    New Member
    Join Date
    Jun 2022
    Location
    Switzerland
    Posts
    12

    Re: how to use BeginTrans, CommitTrans, & RollBackTrans?

    Hello vbrad and thank you very much for your answer! Yes, i am using DAO.

    I never meant I needed to nest transactions... All I am doing looks like the code below (nothing is nested, ie only ONE transaction happens at every single second, never more):

    As you can see there nothing that gets into the game between BeginTrans and CommitTrans, except SQLs!


    Code:
    Dim gwksCRM As Workspace
    Dim gdbsCRM As Database
    
    Sub LoadRecords()
    
        Open "C:\toto.txt" For Input As 1
        
        Do Until EOF(1)
            On Error GoTo ErrorTrans
            Line Input #1, strLine
            gwksCRM.BeginTrans
            gdbsCRM.Execute "INSERT INTO activity (duedate, comment) VALUES (#3/4/2022#,'This is test1')"
            gdbsCRM.Execute "INSERT INTO activity (duedate, comment) VALUES (#3/5/2022#,'This is test2')"
            gdbsCRM.Execute "INSERT INTO activity (duedate, comment) VALUES (#3/6/2022#,'This is test3')"
            gdbsCRM.Execute "INSERT INTO activity (duedate, comment) VALUES (#3/7/2022#,'This is test4')"
            gwksCRM.CommitTrans
            GoTo EndOfLoop
    ErrorTrans:
            gwksCRM.Rollback
    EndOfLoop:
        Loop
    End Sub

    At the second iteration I am getting error 3034 when executing "gwksCRM.CommitTrans", saying that I should not try to Commit something that was never "Begintrans"ed !!!

    Name:  3034.jpg
Views: 864
Size:  15.6 KB
    Last edited by stefano.gatto; Jun 15th, 2022 at 12:49 PM.

  23. #23
    New Member
    Join Date
    Jun 2022
    Location
    Switzerland
    Posts
    12

    Re: how to use BeginTrans, CommitTrans, & RollBackTrans?

    Well sorry about that, but I am using fresh Excel 365, from 2022, and VBA which comes with it! Is DAO not current anymore?

  24. #24
    New Member
    Join Date
    Jun 2022
    Location
    Switzerland
    Posts
    12

    Re: how to use BeginTrans, CommitTrans, & RollBackTrans?

    Hello Zvoni and thank you for your answer!
    I am going to test what you say, although it's not that different from what I do and let you know.
    Have a nice rest of the day!

  25. #25
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,415

    Re: how to use BeginTrans, CommitTrans, & RollBackTrans?

    Quote Originally Posted by stefano.gatto View Post
    Well sorry about that, but I am using fresh Excel 365, from 2022, and VBA which comes with it! Is DAO not current anymore?
    It's not "current" since a decade and more
    Use ADO

    Quote Originally Posted by stefano.gatto View Post
    Hello Zvoni and thank you for your answer!
    I am going to test what you say, although it's not that different from what I do and let you know.
    Have a nice rest of the day!
    No, you're not, since you mention a "second" iteration of the loop
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  26. #26
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,040

    Re: how to use BeginTrans, CommitTrans, & RollBackTrans?

    Quote Originally Posted by stefano.gatto View Post
    Hello all,
    In the same frame as this question, here is a collateral issue. I am using Excel 365 and its VBA.
    I am trying to write a function which executes 4 SQL for each line in a text file, containing between 10 and 100 lines. I would like to wrap these 4 SQLs into ONE transaction, so they either pass or fail all together.
    Therefore, can I include BeginTrans and CommitTrans into a loop wrapping my 4 SQLs?
    Actually I tried this but it fails at the second iteration of the loop, which makes me think that one can use BeginTrans/CommitTrans only ONCE per procedure execution! Is this true??? Should I call a proc into my loop so that the whole BeginTrans/SQL1/SQL2/SQL3/SQL4/CommitTrans/IfErrorRollback gets executed only once per procedure call? Is this required?
    I hope my question is understandable.
    Excel is the wrong Tool for that
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  27. #27
    New Member
    Join Date
    Jun 2022
    Location
    Switzerland
    Posts
    12

    Re: how to use BeginTrans, CommitTrans, & RollBackTrans?

    Quote Originally Posted by ChrisE View Post
    Excel is the wrong Tool for that
    You are right, but it's not my choice...
    Last edited by stefano.gatto; Jun 16th, 2022 at 02:42 AM.

  28. #28
    New Member
    Join Date
    Jun 2022
    Location
    Switzerland
    Posts
    12

    Re: how to use BeginTrans, CommitTrans, & RollBackTrans?

    Yes, it's the second iteration, happening because there is a second line in the text file opened (#1)

  29. #29
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: how to use BeginTrans, CommitTrans, & RollBackTrans?

    Thing is ... Excel may not support transactions. Sure, the method is there, but it's for supporting transactions on databases. I don't think the Excel provider supports it... and that may be (may) the reason it isn't working.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  30. #30
    New Member
    Join Date
    Jun 2022
    Location
    Switzerland
    Posts
    12

    Re: how to use BeginTrans, CommitTrans, & RollBackTrans?

    Quote Originally Posted by Zvoni View Post
    Wow... a 16 year old thread revived.
    Talk about necromancy........

    Why in blazes would you need a loop for the SQL?
    You just need a Loop for the Lines of the Textfile
    Start Loop
    Read a Line from textfile
    Start the Transaction
    Concat all 4 SQL's into one single line, delimited with the Statement-Delimiter of your Database, and execute it.
    If No Error Then Commit Else Rollback
    End loop --> Exit Loop when EOF of TextFile
    I did exactly this and am getting error 3034 at the second iteration of the loop you are describing above. The step where the error occurs is where you wrote "If No Error Then Commit Else Rollback". It does not want to Commit!

    Did you personally ever experienced BeginTrans/CommitTrans into a loop?

  31. #31
    New Member
    Join Date
    Jun 2022
    Location
    Switzerland
    Posts
    12

    Re: how to use BeginTrans, CommitTrans, & RollBackTrans?

    Hello all - I just found out what was wrong in my code. I commented out one line and every thing runs smoothly. It is:

    Code:
       Set rstLastID = gdbsCRM.OpenRecordset("SELECT @@IDENTITY")
    This line is between my first and my 2nd SQL, because I need the id created by the 1st SQL to feed my 2nd SQL (it's an audit trail).

    So, apparently, I shouldn't use OpenRecordset() in the middle of the 4 Execute(INSERT) which are committed all together. I have no idea why! Actually it works in the first loop but not in the second!

  32. #32
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,040

    Re: how to use BeginTrans, CommitTrans, & RollBackTrans?

    this is how to use ADO BeginTrans.... etc..
    with the ID's of other Tables

    Code:
    Private Sub Command1_Click()
    
       Dim sSQL As String
       Dim KT_ID As Long
       Dim AD_ID As Long
       
          On Error GoTo Fehler
       
          Cn.BeginTrans
          
          'Insert to first Table
          sSQL = "Insert Into Kontakt (KT_Ort) Values ('Hamburg1')"
          Cn.Execute sSQL
          KT_ID = GetNewID(Cn)
          
          'Insert to second Table
          sSQL = "Insert Into Adresse (AD_Strasse) Values ('Hafengasse1')"
          Cn.Execute sSQL
          AD_ID = GetNewID(Cn)
          
          
          'insert to 3rd Table and use the ID's from the first and second Insert's
          sSQL = "Insert Into Person (PE_Name, PE_KT_ID, PE_AD_ID) " & _
                 "Values ('Willi', " & KT_ID & ", " & AD_ID & ")"
          
          Cn.Execute sSQL
          
          Cn.CommitTrans
          Exit Sub
          
    Fehler:
    'Note if the Rollback occurs the ID's are gone !!
          Cn.RollbackTrans
    End Sub
    
    
    Public Function GetNewID(Cn As ADODB.Connection) As Long
    
       Dim Rs As ADODB.Recordset
       Dim NewID As Long
          
          Set Rs = New ADODB.Recordset
          Rs.Open "Select @@Identity As ID", Cn, , , adCmdText
          NewID = Rs.Fields("ID").Value
          Rs.Close
          Set Rs = Nothing
          
          GetNewID = NewID
    End Function
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  33. #33
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: how to use BeginTrans, CommitTrans, & RollBackTrans?

    Quote Originally Posted by stefano.gatto View Post
    Hello all - I just found out what was wrong in my code. I commented out one line and every thing runs smoothly. It is:

    Code:
       Set rstLastID = gdbsCRM.OpenRecordset("SELECT @@IDENTITY")
    This line is between my first and my 2nd SQL, because I need the id created by the 1st SQL to feed my 2nd SQL (it's an audit trail).

    So, apparently, I shouldn't use OpenRecordset() in the middle of the 4 Execute(INSERT) which are committed all together. I have no idea why! Actually it works in the first loop but not in the second!
    I'm guessing you never closed the recordset afterwards either.
    Because it left open a line... so when you then try to use the transaction again, it's getting a busy signal. If all you're after is the new number, get it, then close the underlying recordset. That should then allow the transaction to happen on the next go around. OR... get your ID inside your transaction (can't guarantee it'llwork, but it can't hurt at this point either).


    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  34. #34
    New Member
    Join Date
    Jun 2022
    Location
    Switzerland
    Posts
    12

    Re: how to use BeginTrans, CommitTrans, & RollBackTrans?

    Hello ChrisE,

    Thank you very much for your constructive answer and solution! It's true that, by isolating the OpenRecordSet, which I forgot to Close, into a dedicated procedure would clean up everything I left open, even if by mistake!
    Vielen Dank!
    Stefano
    Last edited by stefano.gatto; Jun 16th, 2022 at 02:46 AM.

  35. #35
    New Member
    Join Date
    Jun 2022
    Location
    Switzerland
    Posts
    12

    Re: how to use BeginTrans, CommitTrans, & RollBackTrans?

    Hello techgnome and thank you very much for the heads up about closing Recordsets... I am going to test your suggestion, but it looks great!
    What did you mean by "get your ID inside your transaction"?? Can I avoid opening a Recordset to get that ID?

  36. #36
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: how to use BeginTrans, CommitTrans, & RollBackTrans?

    No, but if you should still be able to get it from inside the transaction... but still remember to close the recordset.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  37. #37
    New Member
    Join Date
    Jun 2022
    Location
    Switzerland
    Posts
    12

    Re: how to use BeginTrans, CommitTrans, & RollBackTrans?

    Quote Originally Posted by techgnome View Post
    No, but if you should still be able to get it from inside the transaction... but still remember to close the recordset.

    -tg
    Done! Thank you so much!

    Code:
                                    Set rstLastID = gdbsCRM.OpenRecordset("SELECT @@IDENTITY")
                                    lngLastId = rstLastID.Fields(0).value
                                    rstLastID.Close

  38. #38
    New Member
    Join Date
    Jun 2022
    Location
    Switzerland
    Posts
    12

    Re: how to use BeginTrans, CommitTrans, & RollBackTrans?

    Quote Originally Posted by stefano.gatto View Post
    Hello techgnome and thank you very much for the heads up about closing Recordsets... I am going to test your suggestion, but it looks great!
    What did you mean by "get your ID inside your transaction"?? Can I avoid opening a Recordset to get that ID?
    Oh ok I just got what you mean by "get your ID inside your transaction". You mean between BeginTrans and CommitTrans!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width