?
Solved

How to use BeginTrans, CommitTrans, and RollbackTrans in vb6?

Posted on 2007-10-17
13
Medium Priority
?
4,531 Views
Last Modified: 2012-08-13
Hi,

I want to use BeginTrans, CommitTrans, and RollbackTrans in my app.

My questions:

1. Is there any rules to use it.
2. How to do it.

I have tried to use it, but after the conapp.CommitTrans, the table is still empty.

For example:

I have transaction that call "Part Out", which save Header and Detail of Transaction, Updating Stock Table and Increasing AutoNumber Table for Transactio Number.
I use module and class module to save those datas.
I use recordset to add/update/delete data.

Thank you.
0
Comment
Question by:emi_sastra
  • 5
  • 5
  • 3
13 Comments
 
LVL 53

Expert Comment

by:Dhaest
ID: 20092601
Here you can find an example of the use of transactions
http://www.vbexplorer.com/VBExplorer/wrox/sample1061.asp
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 20092783
The link just describe simple transaction. Do not use module or class module.

I think I should ask specific question:

What happened between the begintrans and CommitTrans, I call a class module that update stock.
    conapp.BeginTrans

    set clsStock = new clsStock
          clsStock.UpdateStock(paremeters)
    set clsStock = nothing

    conapp.CommitTrans

   At clsStock I:
    1. Open Stock Table using recordset.
    2. Update the Stock Table.
    3. Close the Recordset.

Is there anything wrong with those code?

Thank you.





0
 
LVL 53

Expert Comment

by:Dhaest
ID: 20092846
Are you sure that you are using the same connection-object (conapp) in your module ?

(can you show what you do in UpdateStock)
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 1

Author Comment

by:emi_sastra
ID: 20092975
Yes, the same connection.

Public Function AddSubStockQty(strPartNo As String, _
                                  strLocCode As String, _
                                  curQty As Currency, _
                                  curQtyOrigin As Currency, _
                                  strUnitQty As String, _
                                  blnAdd As Boolean, _
                                  conName As ADODB.Connection)
     
Dim lngQtyNow As Long
Dim intPointer As Integer
Dim strFields As String
Dim strCriteria As String

strFields = "*"
strCriteria = "StockPartNo='" & Trim(strPartNo) & "'"
If Trim(strLocCode) <> "" Then strCriteria = strCriteria & " and StockLocCode='" & Trim(strLocCode) & "'"

Set clsTMSTOCK = New clsTMSTOCK
Set rstSource = clsTMSTOCK.OpenData(strCriteria, strFields, "", False, conName)
Set clsTMSTOCK = Nothing

With rstSource
     If .EOF Then
        .AddNew
        !StockPartNo = strPartNo
        !StockLocCode = strLocCode
        !StockCode = !StockPartNo & !StockLocCode
        !StockQty = 0
        !StockUnitQty = ERV_Get_Field_Data("SvcPartUnit1", "TMSVCPART", "SvcPartNo", strPartNo, conName)
           
        !CrtId = strUserId
        !CrtDate = ERV_Get_Server_Date(conApp)
       
        !UpdId = strUserId
        !UpdDate = ERV_Get_Server_Date(conApp)
        .Update
        .Sort = "StockCode"
     End If
End With


Set clsTMSTOCK = New clsTMSTOCK
Set rstSource = clsTMSTOCK.OpenData(strCriteria, strFields, "", False, conName)
Set clsTMSTOCK = Nothing

lngQtyNow = Get_Stock_Unit_Conversion(strPartNo, curQty, strUnitQty, conName)
intPointer = 0

With rstSource
     While Not .EOF
           intPointer = intPointer + 1
           If blnAdd = True Then
              !StockQty = !StockQty + curQty '- curQtyOrigin
           Else
               If lngQtyNow > !StockQty Then
                  If intPointer < .RecordCount Then
                     lngQtyNow = lngQtyNow - !StockQty
                     !StockQty = 0
                  Else
                     !StockQty = !StockQty - lngQtyNow
                     lngQtyNow = 0
                  End If
               Else
                  !StockQty = !StockQty - lngQtyNow
                  lngQtyNow = 0
               End If
           End If
           
           !UpdId = strUserId
           !UpdDate = ERV_Get_Server_Date(conApp)
           
           .Update
           
           If !StockQty = 0 Then .Delete
           .MoveNext
     Wend
End With
End Function
                                 


0
 
LVL 53

Expert Comment

by:Dhaest
ID: 20092998
Are you sure you don't close your connection in one of the calling procedures, like Get_Stock_Unit_Conversion, ...
0
 
LVL 3

Expert Comment

by:Canadean
ID: 20093067
As Dhaest says you must use the same connection object. Make sure that if you pass a connection object between procedures that you pass it ByRef and not by value.

Also, Do you have any nested transactions in your VB code or maybe in any stored procedures you may be calling?

Another thing you may want to do (just while you debug it) is to return the nesting value from the Begintrans statement.

i = conapp.BeginTrans

If it returns 1 then it means it's a top level transaction, any higher means you have nested transactions. You could begin and end a nested transaction somewhere in your module temporarily to check the connection still thinks it has a transaction open.

One more thing to check is your connection string. Make sure you are using the SQL Server driver (SQLOLEDB) and not ODBC.

Hope this helps....
0
 
LVL 3

Expert Comment

by:Canadean
ID: 20093101
Just to eliminate the possiblitiy of the connection being close or prematurely, try adding a watch on the connection object and get VB to break when it changes. Can you post your connection string here too.
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 20093300
I never close connection until it is close when mdi form is closed.

Does close the recordset after open, add/update/delete before the CommitTrans is ok?

Set conApp = ERV_SQL_Open_Connection(strSQLCatalog, strSQLServerName)

Public Function ERV_SQL_Open_Connection(strSQLCatalog As String, _
                                    strSQLServerName As String, _
                                    Optional blnWithPassword As Boolean, _
                                    Optional blnUserId As Boolean) As ADODB.Connection

ERV_SQL_Open_Connection.Open "Provider=SQLOLEDB.1;integrated security=sspi;Persist Security Info=False;User ID=sa;Initial Catalog=" & strSQLCatalog & ";Data Source=" & strSQLServerName

End Function
0
 
LVL 3

Expert Comment

by:Canadean
ID: 20093510
Try setting a watch on the connection anyway, there maybe something going on that is not obvious at first.

Closing the recordset should not cause a problem as far as I am aware.

If you run the code without the transaction, does your table get populated as you would expect?

If you don't get anywhere, I would start from basics and work your way up.
I would create a testtable, and write a simple VB routine to open a transaction, update some data and commit the transaction. Keep it really simple and avoid using any routines from your existing code. So create the connection string by hand and dont use any of your existing routine (e.g. ERV_SQL_Open_Connection)
If that works then slowly add parts of your existing routine until it fails.

If even the most basic code fails then you may need to check that you are not trying to update tables on your SQL server that are linked to another server etc.
0
 
LVL 3

Expert Comment

by:Canadean
ID: 20093522
Also, what version of ADO are you using?
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 20093633
If you run the code without the transaction, does your table get populated as you would expect?
Yes.

I will try using simple routines. But I think there is nothing wrong with the conneciton.

Also, what version of ADO are you using?
ADO 2.5

Thank you.

0
 
LVL 3

Accepted Solution

by:
Canadean earned 2000 total points
ID: 20094075
OK, well I would still put a watch on the connection as that is the most likely cuase of the the problem, but you may want to try ADO 2.8 unless you have any real issues with that. I use transactions successfully all the time using VB6 against a SQL Server 2000 database using ADO 2.8 with no problems.
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 20094094
Thank you very much.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to get certified? Check out some courses that help you prepare for third-party exams.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Viewers will learn how the fundamental information of how to create a table.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question