Problems on commit transaction

[Former Member]

I'm trying to use transactions in SAP B1 SDK but I have a problem, I start the transaction, I make the document normally for example a Purchase Order, the document doesn't retrieve any errors at creation but SDK saids than can't commit the transaction, somebody knows why it happends...

[Former Member]

Hi,

Correction to the previous post, copy & paste removed the '<>' tag

If (oSalesOrder.Add() <> 0) Then

Tai

[Former Member]

Hi,

I'm trying to add a sales order and i am having the same issue like Karla. Below are the codes:

Dim oSalesOrder As SAPbobsCOM.Documents

Try

_oSBO.SboCompany.StartTransaction()

oSalesOrder = _oSBO.SboCompany.GetBusinessObjectSAPbobsCOM.BoObjectTypes.oOrders)

oSalesOrder.DocDueDate = DateTime.Now

oSalesOrder.CardCode = "C00003"

oSalesOrder.Lines.ItemCode = "CO42ZL"

If (oSalesOrder.Add() <> 0) Then

_oSBO.SboCompany.GetLastError(ErrCode, ErrMsg)

Else

Dim x As String = _oSBO.SboCompany.GetNewObjectKey()

If (_oSBO.SboCompany.InTransaction) Then

oSBO.SboCompany.EndTransaction(SAPbobsCOM.BoWfTransOpt.wfCommit)

End If

End If

Catch ex As Exception

oSBO.SboApplication.SetStatusBarMessage("The following error has occurred: " + ex.Message.ToString(), SAPbouiCOM.BoMessageTime.bmtLong, True)

Finally

oSalesOrder = Nothing

End Try

The Add() function is successful but when commiting it threw an exception "Invalid Index".

If i don't commit then SQL is stuck with an incomplete transaction

If i do not use Transaction when adding the above codes then i get

ErrCode = -1105

ErrMsg = "Could not commit transaction"

Any help will be appreciated!!!!!

Thanks

Tai

Any

[former_member209699]

HI,

I also faced this type of problem in SAP b1 2005 PL 25

When we use transactions in PL-25 then this occurs due to improper spacing in queries. I resolved this by providing spaces in my queries.

May be this will help you in tracing the cause of error.

regards:

SANDY

Edited by: | Sandeep Saini | Roorkee | India on Sep 25, 2008 3:23 PM

[Former Member]

Hi Karla...

This is your code:

+++++++++++++++++++ CODE ++++++++++++++++++++++++++++

There´s an example of my code:

Dim conexion As SAPbobsCOM.Company

Dim document As SAPbobsCOM.Documents

Dim retval

document = conexion.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oProductionOrders)

conexion.StartTransaction()

....

document.DocDate = Today

document.DocDueDate = Today

document.CardCode = "P0001"

document.DocTotal = 250

document.Lines.ItemCode = "Inv0001"

document.Lines.Price = 0.01

document.Lines.Quantity = 2500

....

retval = document.Add

If retval 0 Then

conexion.GetLastError(ErrCode, ErrMsg)

MsgBox(ErrCode & " " & ErrMsg)

conexion.EndTransaction(SAPbobsCOM.BoWfTransOpt.wf_RollBack

Else

conexion.EndTransaction(SAPbobsCOM.BoWfTransOpt.wf_Commit)

End If

+++++++++++++++++++ CODE ++++++++++++++++++++++++++++

In this last "IF" is your error...

You see, when a document is successfully added, your is 0 (zero), so you commit, not rollback... (I suppose that you already fix this in your code)

Another interesting thing in SBO, is that when one of your documents fails, you shouldn't use the rollback, because, SBO already do that. But, if all your documents are added successfully, you have to use the Commit.

You can also check the property... The way that this property works is:

When using the StartTransaction method, this property is set to TRUE,

When using the EndTransaction(Commit) method is set to FALSE

When a document.Add method fails is set to FALSE automatically by SBO.

I hope it helps...

Greetings

Gabriel

[Nussi]

Hi,

i remember the same problem with documents and transaction:

when you add the document and it has no errors SAP sends automatically the commit !

lg David

[Former Member]

Hi

Here an example with transaction.

Hope it can help you.

Regards

Sierdna S.

Dim oSqLTrans As SqlTransaction
Dim sTransactionName As String
Try
  ' BEGIN TRANSACTION +++++
  sTransactionName = SBO_Company.UserSignature.ToString("0000") & _
                                   "_" & DateTime.Now.Ticks.ToString
  oSqLTrans = oSqlConn.BeginTransaction(sTransactionName)
  ' +++++
  ' DO SOMETHING ON DATABASE 
  ' +++++
  ' COMMIT TRANSACTION +++++
  'DEBUG: oSqLTrans.Rollback(sTransactionName)
  oSqLTrans.Commit()
Catch ex As Exception
  If Not oSqLTrans Is Nothing Then
    Try
      ' ROLLBACK TRANSACTION
      oSqLTrans.Rollback(sTransactionName)
    Catch sqlex As SqlException
      If Not oSqLTrans.Connection Is Nothing Then
        ' log exception
      End If
    End Try
  End If
Finally
  oSqLTrans = Nothing
  sTransactionName = Nothing
End Try

[Former Member]

Thank´s for your example, but my problems are on SDK transactions, actually I use both Transactions Sql and SDK but only SDK transactions give me errors

[Nussi]

What patch level do you have ?

because i had the same problem in past and it got solved later.

lg David

[Former Member]

SBO 2007 SP:00 PL:38

[Nussi]

ok - didn't try this with 2007 so far.

i think you should just comment the "commit" in the if when the document added successfully.

the rollback leave enabled.

lg David

[Former Member]

I think that it depends what David said already, after adding document succesfuly, the transaction is commited already. Try to check first, is is some transaction active as

If oCompany.InTransaction Then

oCompany.EndTransaction(SAPbobsCOM.BoWfTransOpt.wf_Commit)

End If

BTW, David, do you know about some way, how to disable the auto commit in document creation?

[YatseaLi]

Hello all,

I found the following transation problem in Latest 2007 A PL42 (2007 B PL08):

Global Transaction has some problem in the following case:

Before_Action
       StartTransaction
      Add BP via DI
B1 action
       Add Sales Order
After Action
        Commit/rollback

But this scenario works fine in 2007 A PL30

Any one is facing this problem, please create a message to SAP support.

And In Karla's scenario, it is unnecessary to wrapper the purchaorder.Add() in a global Transation.

Because DIObject.Add(), update() etc itselft is a single transation by default in B1.

You don't need to care about it. Then the global transation is used when like this:

StartTransaction
      Add BP via DI
      Add a SalseOrder via DI
EndTransation

Kind Regards, Yatsea

[Former Member]

Yatsea,

your scenario works fine, but when in your sceanrion

Before_Action

StartTransaction

Add BP via DI

B1 action

Add Sales Order

After Action

Commit/rollback

for example Add BP via DI faills, the transaction is roolbacked and from next step you dont work inside transaction, so you create normaly sales order and rollback isnt possible already.

[Former Member]

Hi Petr,

You need to change the model to work like this:

StartTransaction

if (AddBP() && AddSalesOrder() && WhatEverElseYouNeedToDo())

CommitTransaction

else

RollBack

You must check that each method call is successful, that way as soon as one fails then the rollback is performed. This way you get the desired "All" or "Nothing" in terms of data saving.

Regards, Lita

[rajesh_khater]

But from what I have read, as soon as you use add method in DI API, if it is successful, it commits the transaction. So if sales order fails, BP will still get created !!

I have posted 2 separate threads here:

[Former Member]

Hi,

Please post the code that u have written so that someone could pinpoint the error.

Vasu Natari.

[Former Member]

There´s an example of my code:

Dim conexion As SAPbobsCOM.Company

Dim document As SAPbobsCOM.Documents

Dim retval

document = conexion.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oProductionOrders)

conexion.StartTransaction()

....

document.DocDate = Today

document.DocDueDate = Today

document.CardCode = "P0001"

document.DocTotal = 250

document.Lines.ItemCode = "Inv0001"

document.Lines.Price = 0.01

document.Lines.Quantity = 2500

....

retval = document.Add

If retval <> 0 Then

conexion.GetLastError(ErrCode, ErrMsg)

MsgBox(ErrCode & " " & ErrMsg)

conexion.EndTransaction(SAPbobsCOM.BoWfTransOpt.wf_RollBack

Else

conexion.EndTransaction(SAPbobsCOM.BoWfTransOpt.wf_Commit)

End If