multiple stored procedure, single transaction

[scias23]

i get an "This SqlTransaction has completed; it is no longer usable." error everytime i run my code:

the main code

If DBOpen() = True Then
            Try
                'begin transaction
                BeginTranscation("Clearance")

                'receipt
                CallSP("InsertReceipt")
                InsertParam("@ORNo", Trim(txtOR.Text))
                InsertParam("@FACode", "***")
                InsertParam("@Amount", txtAmount.Text)
                InsertParam("@Nature", "Business Clearance")
                InsertParam("@PaymentType", "Cash")
                InsertParam("@DateIssued", dateDocIssue.Text)
                ExecSP()

                'document
                CallSP("InsertDocument")
                InsertParam("@PersonID", gridNames.SelectedRows(0).Cells(0).Value)
                InsertParam("@Orno", Trim(txtOR.Text))
                InsertParam("@OfficerID", ActiveOfficerID)
                InsertParam("@DateIssued", dateDocIssue.Text)
                InsertParam("@PlaceIssued", "Lias, Marilao, Bulacan")
                ExecSP()

                FillData("SELECT TOP(1) DocNo FROM Document WITH(NOLOCK) ORDER BY DocNo DESC")
                Dim temp As Integer = dset.Tables(0).Rows(0)("DocNo").ToString

                'clearance
                CallSP("InsertClearance")
                InsertParam("@DocID", temp)
                InsertParam("@Findings", Trim(txtFindings.Text))
                InsertParam("@Purpose", Trim(txtPurpose.Text))

                If CTCSelectFromList = True Then
                    InsertParam("@CTCNo", CTCFromList)
                Else
                    InsertParam("@OCTCNo", txtCTC.Text)
                    InsertParam("@OCTCDIssued", dateIssue.Text)
                    InsertParam("@OCTCPIssued", Trim(txtCTCPlace.Text))
                End If
                CommitTransaction()
            Catch ex As Exception
                RollbackTransaction("Clearance")
                MsgBox("Payment Fields Incomplete", MsgBoxStyle.Critical)
                panelctr -= 1
                PanelSwitch(panelctr)
                'Finally
                If flag = True Then
                    DBClose()
                End If
            End Try
        End If

the module

Public Sub BeginTranscation(ByVal tr As String)
        trans = conn.BeginTransaction(tr)
        cmd.Transaction = trans
    End Sub

    Public Sub InsertParam(ByVal par As String, ByVal val As String)
        cmd.Parameters.AddWithValue(par, val)
    End Sub

    Public Sub ExecSP()
        cmd.ExecuteNonQuery()
    End Sub

    Public Sub CommitTransaction()
        trans.Commit()
    End Sub

    Public Sub RollbackTransaction(ByVal tr As String)
        trans.Rollback(tr)
    End Sub

    Public Sub CallSP(ByVal sp As String)
        cmd.CommandText = sp
        cmd.Connection = conn
        cmd.CommandType = CommandType.StoredProcedure
    End Sub

i specified the transaction of sqlcommand named cmd via the Public Sub BeginTranscation. but, is this correct?

also, the error points to the Public Sub RollbackTransaction if the insert to the stored procedure fails. : "This SqlTransaction has completed; it is no longer usable."

how can i perform multiple stored procedure calls within a single transaction correctly? thanks

[kvprajapati]

Double thread - http://www.daniweb.com/forums/post1145480.html

Thread Closed.