Transaction Handling VB.Net

Question

I have a program that inserting records to DB in 2 queries.And I'm handling the transaction there. But my transaction is not working. After executing first statement , if I close the program records will insert the first table and not inserting to the second.

But that should rollback from first table. What is the issue here.

Try
objBLlCommonFunction.BeginTransaction()
For j As Integer = 0 To dgstkReceivd.VisibleRowCount - 1
objBllStcTransaction.InsertStockTransferExcelDetail(InvNo, lblDateI.Text)      
Next

objBllStcTransaction.InsertStockTransferExcelHeader(InvNo, dbId)

ScriptManager.RegisterClientScriptBlock(btnSave, btnSave.GetType(), "message", "alert('" + "Successfully Saved" + "');", True)
objBLlCommonFunction.CommitTransaction()
Catch ex As Exception
        objBLlCommonFunction.RollbackTransaction()
        objerror.AddToErrorLog(ex.StackTrace, ex.Message)
        ScriptManager.RegisterClientScriptBlock(btnSave, btnSave.GetType(), "message", "alert('" + ex.Message + "');", True)
End Try

score 0 · Answer 1 · answered Mar 26 '15 at 06:29

You should be using a transaction scope like this.

`
Try ' Create the TransactionScope to execute the commands, guaranteeing ' that both commands can commit or roll back as a single unit of work. Using scope As New TransactionScope() Using connection1 As New SqlConnection(connectString1) ' Opening the connection automatically enlists it in the
' TransactionScope as a lightweight transaction. connection1.Open()

            ' Create the SqlCommand object and execute the first command. 
            Dim command1 As SqlCommand = New SqlCommand(commandText1, connection1)
            returnValue = command1.ExecuteNonQuery()
            writer.WriteLine("Rows to be affected by command1: {0}", returnValue)

            ' If you get here, this means that command1 succeeded. By nesting 
            ' the using block for connection2 inside that of connection1, you 
            ' conserve server and network resources as connection2 is opened 
            ' only when there is a chance that the transaction can commit.    
            Using connection2 As New SqlConnection(connectString2)
                ' The transaction is escalated to a full distributed 
                ' transaction when connection2 is opened.
                connection2.Open()

                ' Execute the second command in the second database.
                returnValue = 0
                Dim command2 As SqlCommand = New SqlCommand(commandText2, connection2)
                returnValue = command2.ExecuteNonQuery()
                writer.WriteLine("Rows to be affected by command2: {0}", returnValue)
            End Using 
        End Using 

    ' The Complete method commits the transaction. If an exception has been thrown, 
    ' Complete is called and the transaction is rolled back.
    scope.Complete()
    End Using 
Catch ex As TransactionAbortedException
    writer.WriteLine("TransactionAbortedException Message: {0}", ex.Message)
Catch ex As ApplicationException
    writer.WriteLine("ApplicationException Message: {0}", ex.Message)
End Try `

Transaction Handling VB.Net

1 Answers1