CODEWITHSUNDEEP
sqlvb.net
k80sg
k80sg

Reputation: 2473

SQL transaction rollback failure

I have 2 tables which I want to insert data into, table "CAM" and table "COut", after inserting data in "CAM" it shall return a retAutoID which will be hand to "COut" as one of the parameter required for it's insertion. I implemented a rollback in my Business Logic such that if "Cout" hits an exception error while inserting, the previous insertion for "CAM" will rollback even if successful.

With the code below, I am getting this error after trying to do perform insertion: "ExecuteNonQuery requires the command to have a transaction when the connection assigned to the command is in a pending local transaction. The Transaction property of the command has not been initialized."

I think the problem may to due to:

dbCommand = GetStoredProcedureCommand("COut_Add") dbCommand.Connection = dbConnection

If I remove the second data insertion of "Cout" entirely, I will not have a problem. Please kindly advice. Thanks.

Public Function InsertCM(ByVal objCMBLL As CMBLL, ByVal dbTrans As DbTransaction, ByVal dbConnection As DbConnection, ByVal COut As DataSet) As Boolean

  Dim dbCommand As DbCommand = Nothing
  Dim retAutoID As Int32 = Nothing
  Dim bol_Success As Boolean = False

  Try
    If dbConnection.State <> ConnectionState.Open Then
      dbConnection.Open()
    End If

    dbCommand = GetStoredProcedureCommand("CAM_Add")
    dbCommand.Connection = dbConnection
    dbCommand.Transaction = dbTrans

    With objCMBLL 
      AddInParameter(dbCommand, "@Code", DbType.String, 50, DBNull.Value)
      If Not String.IsNullOrEmpty(.CamCode) Then
        dbCommand.Parameters("@Code").Value = .CamCode
      End If
      retAutoID = CType(ExecuteScalar(dbCommand), Integer)

      dbCommand = GetStoredProcedureCommand("COut_Add")
      dbCommand.Connection = dbConnection

      For i As Integer = 0 To COut.Tables("COut").Rows.Count - 1
        dbCommand.Parameters.Clear()

        AddInParameter(dbCommand, "@Ol_Code", DbType.String, 50, DBNull.Value)$
        dbCommand.Parameters("@Ol_Code").Value = COut.Tables("CampaignOutlets").Rows(i).Item(0).ToString

        AddInParameter(dbCommand, "@Campaign_AutoID", DbType.Int32, 0, DBNull.Value)
        dbCommand.Parameters("@Campaign_AutoID").Value = retAutoID

      Next i
      ExecuteNonQuery(dbCommand)
    End With  

    bol_Success = True

  Catch ex As Exception
    Throw New DALException(ex, dbCommand, Caching.GetCustomerCodeCookie(), "InsertCampaignManagementTable")
  Finally
    If Not dbCommand Is Nothing Then
      dbCommand.Dispose()
    End If
  End Try
  Return bol_Success
End Function

Upvotes: 0

Views: 1179

Answers (2)

k80sg
k80sg

Reputation: 2473

Ok turn out that I didn't include 'dbCommand.Transaction = dbTrans' for tbl "COut".

Upvotes: 1

Thinhbk
Thinhbk

Reputation: 2214

I see in your code:

dbCommand.Connection = dbConnection (the 2nd time).

Do you really need to re-assign connection for command? I suppose you should not. We should re-assign sql text of Command object only. It should be like this:

dbCommand.text = GetStoredProcedureCommand("COUt")

instead of

dbCommand = GetStoredProcedureCommand("COut_Add") dbCommand.Connection = dbConnection

And

For i As Integer = 0 To COut.Tables("COut").Rows.Count - 1 dbCommand.Parameters.Clear()

I suppose you must call dbCommand.Parameters.Clear() before the loop.

HTH.

Upvotes: 0

Related Questions