Need some help from someone out there. I cannot seem to get Commit/Rollback to work for me in my code. I finally gave up and wrote a simple routine, shown below, and I still cannot get this to work. There are no errors but if you tell
it to rollback the record still appears in the database (SQL Express 2008). I've done the transaction by hand in SQL Studio with a BEGIN and ROLLBACK statement and it works correctly - the record is gone after the rollback. This is part of something
larger but I put it into a very simple routine just to try to bug fix my transaction problem. I've put a breakpoint on the MsgBox line. I can see the open transaction and the open connection. When I press No on the MsgBox and step through
the action it execute the _transaction.rollback and _connection.close - I can see the status change. There are no errors but the record is in the database - not rolled back as it should have been.
I'm using the Enterprise Library 4.1 and Visual Studio 2008. Any help will be greatly appreciated.
Private Sub SaveInTransaction()
Dim _db As Database
Dim _dbCommand As Common.DbCommand = Nothing
Dim _connection As DbConnection = Nothing
Dim _Transaction As DbTransaction = Nothing
'Setup Database Connection
_db = DatabaseFactory.CreateDatabase
_connection = _db.CreateConnection
_dbCommand = _connection.CreateCommand
'Setup the Transaction
_Transaction = _dbCommand.Transaction
_Transaction = _connection.BeginTransaction()
'Add my Parameters
CreateParameter("varCustomerID", DbType.Guid, ParameterDirection.Input, Guid.NewGuid, _dbCommand)
CreateParameter("varCustomerName", DbType.String, ParameterDirection.Input, "This Person", _dbCommand)
CreateParameter("varCustomerEmail", DbType.String, ParameterDirection.Input, "email@example.com", _dbCommand)
CreateParameter("varCustomerLogin", DbType.String, ParameterDirection.Input, "myaccount", _dbCommand)
CreateParameter("varCustomerPassword", DbType.String, ParameterDirection.Input, "mypassword", _dbCommand)
'Execute the SP
_dbCommand.CommandType = CommandType.StoredProcedure
_dbCommand.CommandText = "Customer_Insert"
If MsgBox("Procedure Complete: Do you want to commit?", MsgBoxStyle.YesNo) = MsgBoxResult.Yes Then
Public Sub CreateParameter(ByVal ParamName As String, _
ByVal dbType As DbType, _
ByVal Direction As ParameterDirection, _
ByVal Value As Object, _
ByRef _dbCommand As Common.DbCommand)
Dim _param As System.Data.Common.DbParameter = _dbCommand.CreateParameter()
_param.ParameterName = ParamName
_param.DbType = dbType
_param.Direction = Direction
_param.Value = Value
Try enclosing your code in a using statement.
using (DbConnection connection = db.CreateConnection())
DbTransaction transaction = connection.BeginTransaction();
// Credit the first account.
// Commit the transaction.
result = true;
// Roll back the transaction.
Global Technology & Solutions
I appreciate the suggestion but I'm trying to avoid Using. It would work easily in my demo but not in the actual application. Any logical reason that I would have to use
Using to make the transaction work?
This might explain, Read the "Community Content" Part:
Global Technology and Solutions