IlCapo

Im new with VB2005

Supose I have the following code:

Try

' Generar el registro base - Insert

Me.ReparMovtosTableAdapter.Insert(A, B, C, D) Insert

' Grabar los Accesorios

Dim CodAcces As String

Dim lineas As Integer = AccDataGridV.RowCount - 1

Dim i As Integer

For i = 0 To lineas

If Not AccDataGridV.Rows(i).Cells("Si").Value = False Then

CodAcces = AccDataGridV.Rows(i).Cells("CodAcc").Value

Me.ReparAccTableAdapter.Insert(A, E, F) Insert

End If

Next i

' grabar Observaciones

If Me.ObvsText.Modified = True Then

ReparObservTableAdapter.Insert(A, B, G, H) Insert

End If

Catch ex As Exception

MsgBox("Error de Insert Ingreso Reparaciones")

End Try

I need to Insert records in 3 differents tables in a transaction mode.

I Tried with TransactionScope but do not work with OLEDB & Access table.

I want to use the Me.TableAdapter.Insert() method

My OLEDB connection is "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=R:\Reparaciones.mdb"

I want to do a transaction like this:

BeginTrans

Try

Insert()

Insert()

Insert()

Conmit()

Catch ex as Exception

RollBack()

End Try

Dispose()

Can anyone tell me a way to do a transaction with schema

Thank you very much (sorry for my english)



Re: Transactions Programming OLEDB Transaction

Florin Lazar - MSFT

Hi,

Indeed Microsoft Jet doesn't support System.Transactions and TransactionScope.

If you still want to use transactions, you can use native OleDB transactions: http://msdn2.microsoft.com/en-us/library/93ehy0z8.aspx

Cheers,

Florin






Re: Transactions Programming OLEDB Transaction

Scotty

I have a similar TransactionScope framed routine working with four ExecuteNonQuery() sections talking to two databases (1 connection via SQL and the other via OLE).

I'm running under Visual Studio Express C# with databases under SQL Server Express 2005 & Access 2003.

At first I had the same error message re: Jet 4.0, then, due to a typing error, one of my many attempts to find a work-around started working...

It turns out that if you open the OleDbconnections to the OLE databases outside the frame

OleDbConnection connection1 = new OleDbConnection(connectString1);

if (connection1.State != ConnectionState.Open)
connection1.Open();


using (TransactionScope ts = new TransactionScope())

{

....

}

the transactions work correctly. The SqlConnections can be opened within the frame without causing any errors.






Re: Transactions Programming OLEDB Transaction

burton

however, your oledb operations will not participate in the TransactionScope. I have been toying with the idea of implementing a custom Resource Manager that will allow my OleDb connection to participate in the TransactionScope. If i have any success, i'll post here.




Re: Transactions Programming OLEDB Transaction

burton

I have implemented a basic resourcemanager that delegates to an OleDbTransaction. Note as Scotty mentioned, that you must create and open the OleDbConnection outside the TransactionScope to avoid the exception.

My OleDbResourceManager class has a CreateCommand method that returns a new OleDbCommand tied to the OleDbConnection and OleDbTransaction that was passed/created in the constructor.

so enlisting a OleDb connection in a transaction becomes as simple as creating a OleDbResourceManager, passing the connection, then using the CreateCommand method to create all of your commands inside a TransactionScope.

I've done only basic testing on this code and have not used this in a production environment yet, so all the standard disclaimers apply.

Imports System.Transactions

Imports System.Data.OleDb

Imports System.Data.SqlClient

Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Using oledbconn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=myaccessdatabase.mdb")

oledbconn.Open()

Using scope As New TransactionScope

Dim oledbrm As New OleDbResourceManager(oledbconn)

Using oleInsertGroup As OleDbCommand = oledbrm.CreateCommand("Insert Into Groups (GroupName, Description) values ('TRANSACTION TEST', 'TRANSACTION TEST')")

oleInsertGroup.ExecuteNonQuery()

End Using

Using sqlconn As New SqlConnection("Server=(local);Database=mysqldatabase;Integrated Security=True")

sqlconn.Open()

Using sqlInsertGroup As New SqlCommand("Insert Into Groups (Name, Description) values ('TRANSACTION TEST', 'TRANSACTION TEST')", sqlconn)

sqlInsertGroup.ExecuteNonQuery()

End Using

End Using

If MessageBox.Show("YES to commit; NO to rollback", "tran test", MessageBoxButtons.YesNo) = Windows.Forms.DialogResult.Yes Then

scope.Complete()

End If

End Using

End Using

End Sub

End Class

Public Class OleDbResourceManager

Implements IEnlistmentNotification

Private oledbconn As OleDbConnection

Private oledbtransaction As OleDbTransaction

Private enlisted As Boolean

Public Sub New(ByVal oledbconn As OleDbConnection)

If oledbconn.State <> ConnectionState.Open Then Throw New ArgumentException("The OleDbConnection must be open", "oledbconn")

Me.oledbconn = oledbconn

Dim tx As Transaction = Transaction.Current

If tx IsNot Nothing Then

tx.EnlistVolatile(Me, EnlistmentOptions.None)

oledbtransaction = oledbconn.BeginTransaction()

End If

End Sub

Public Function CreateCommand(ByVal cmdText As String) As OleDbCommand

Return New OleDbCommand(cmdText, oledbconn, oledbtransaction)

End Function

Private Sub Commit(ByVal enlistment As System.Transactions.Enlistment) Implements System.Transactions.IEnlistmentNotification.Commit

If oledbtransaction IsNot Nothing Then oledbtransaction.Commit()

Enlistment.Done()

End Sub

Private Sub InDoubt(ByVal enlistment As System.Transactions.Enlistment) Implements System.Transactions.IEnlistmentNotification.InDoubt

' do nothing

End Sub

Private Sub Prepare(ByVal preparingEnlistment As System.Transactions.PreparingEnlistment) Implements System.Transactions.IEnlistmentNotification.Prepare

preparingEnlistment.Prepared()

End Sub

Private Sub Rollback(ByVal enlistment As System.Transactions.Enlistment) Implements System.Transactions.IEnlistmentNotification.Rollback

If oledbtransaction IsNot Nothing Then oledbtransaction.Rollback()

End Sub

End Class