Transactions

[jeva39]

I know that SQL is no matter of this forum but as this is a important part of a DataBase management, can somebody explain me in a easy example code how to use Begin Transaction, Rollback and Commit? I am very gratefull with the person that help me. Thanks....

 

[jmcilhinney]

Dim connection As New OleDbConnection(<connection string>)
Dim command1 As New OleDbCommand(<SQL command>, connection)
Dim command2 As New OleDbCommand(<SQL command>, connection)
Dim transaction As OleDbTransaction

Try
	connection.Open()
	transaction = connection.BeginTransaction()

	command1.Transaction = transaction
	command2.Transaction = transaction

	command1.ExecuteNonQuery()
	command2.ExecuteNonQuery()
	transaction.Commit()
Catch ex As Exception
	If not transaction Is Nothing
		transaction.Rollback()
	End If

	MessaegBox.Show("Data Access Error.")
Finally
	connection.Close()
End Try

Note that transactions cannot be used in conjunction with CommandBuilders. If you are using DataAdapters you must explicitly code your Delete, Insert and Update commands.