Transaction with tableAdapter

[Thomas]

I am trying to use the dataset+tableadapter to implement the 3-tier
programming.

However, the generated update cannot be used with transaction.

I have tried using transaction scope <-- not succeed

I have tried to follow the approach:
http://weblogs.asp.net/ryanw/archive/2006/03/30/441529.aspx

the following error return:

System.InvalidOperationException: ExecuteReader requires the command to have
a transaction when the connection assigned to the command is in a pending
local transaction. The Transaction property of the command has not been
initialized.

Please help as I am sticking with this problematic update because it is not
transactional. Thanks.

[t-j...@prcvap.microsoft.com]

Dear Thomas,

I understand that you have some problems with tableadapter in transaction.
It is not difficult to implement transactions so that they can be used with a
DataAdapter.Update() call, whether you choose to create your
INSERT/UPDATE/DELETE statements manually or use a CommandBuilder. You just need to set the Command.Transaction property to the appropriate Transaction object.

Following is just an example:
Dim conn As SqlConnection = New SqlConnection()
Dim connString As String = "Server=
SqlInstance;Database=test;Integrated Security=SSPI"
conn.ConnectionString = connString
conn.Open()

' Create the DataAdapters.
Dim cmdString As String = "Select empid,emailalias from tblngemp"
Dim daWarehouse As SqlDataAdapter = New SqlDataAdapter(cmdString,
conn)

' Create the DataSet.
Dim ds As DataSet = New DataSet()

' Create the CommandBuilders and generate
' the INSERT/UPDATE/DELETE commands.
Dim cbWarehouse As SqlCommandBuilder = New
SqlCommandBuilder(daWarehouse)
Dim warehouseDelete As SqlCommand = cbWarehouse.GetDeleteCommand()
Dim warehouseInsert As SqlCommand = cbWarehouse.GetInsertCommand()
Dim warehouseUpdate As SqlCommand = cbWarehouse.GetUpdateCommand()

' Fill the DataSet.
daWarehouse.Fill(ds, "WarehouseInventory")

' Begin the transaction and enlist the commands.
Dim tran As SqlTransaction = conn.BeginTransaction()
warehouseDelete.Transaction = tran
warehouseInsert.Transaction = tran
warehouseUpdate.Transaction = tran

' Modify data to move inventory
' from WarehouseInventory to SiteInventory.

Try
'Execute the commands
daWarehouse.Update(ds, "WarehouseInventory")

'Commit the transaction
tran.Commit()
Catch ex As SqlException
'Roll back the transaction.
tran.Rollback()

'Additional error handling if needed.
Finally
' Close the connection.
conn.Close()
End Try

I hope the information above can help.
If anything is unclear, please feel free to let us know.

John Xu
Microsoft Online Support
Microsoft Global Technical Support Center

Get Secure! - www.microsoft.com/security
=====================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

[Thomas]

Dear John,

I am sorry that what I meant is tableAdapter but not sqldataadapter.
In the visual studio 2005, you can right click the App_code folder and then
new dataset.
There is methods of Insert/Update/Delete which you can customise, and they
are all inside the tableadapter of the dataset. My problem is such method of
insert/update/delete cannot be inside the transaction.

Thanks.

Thomas

[Thomas]

Dear John,

I have solved the problem.
The table adapter can use the following command to get the dataadapter:
adapter = CType(type.GetProperty("Adapter", BindingFlags.NonPublic Or
BindingFlags.Instance).GetValue(tableAdapter, Nothing), SqlDataAdapter)

then I change the command 's transaction to be a specific transaction

adapter.UpdateCommand.Transaction = transaction

Thanks anyway

[t-j...@prcvap.microsoft.com]

Dear Tomas,

Sorry for my misunderstanding.
Anyway, I am glad that you have solved your problem quickly.
And thank you for your code and the feedback.
You are welcome.

John Xu
Microsoft Online Support
Microsoft Global Technical Support Center

Get Secure! - www.microsoft.com/security
=====================================================

When responding to posts, please &quot;Reply to Group&quot; via your newsreader so

that others may learn and benefit from your issue.
=====================================================

This posting is provided &quot;AS IS&quot; with no warranties, and confers no rights.