Transactions + multiple table adapters

I am using SQL2000 and .NET 2.0

I have a dataset, with two DataTables each having a TableAdapter. I need to
use the insert command on the first TableAdapter and then execute a stored
procedure on the second table adapter - all within a transaction.

Now I want to use datasets, because they auto generate the code which is
exactly the same as I intend to manually create anyway and they have the
advantage that I can quickly refresh them if any columns change etc

All of my database operations are in stored procedures in the server, but
accessed through the TableAdapter.

Problem is that each TableAdapter uses it's own connection. I have tried
creating my own connection and transaction and assigning the connection to
the TableAdapter but I just get an error when executing a stored
procedure...

[System.InvalidOperationException] = {"ExecuteScalar 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."}

Anyway, I have tried to wrap these operations in a TransactionScope but this
promotes to a DTC transaction with SQL2000 which isn't acceptable.

Surely there is straightforward way in which you can utilize datasets to
load data from the database and then tableadapters to run any operations
through predetermind stored procedures.

Any help much appreciated...
Mark


.

Relevant Pages

  • Re: Transactions + multiple table adapters
    ... TA's connection by using the Connection property. ... Public Shared Sub SetTransaction(ByVal tableAdapter As Object, ... For Each command As SqlCommand In commandCollection ... stored procedure on the second table adapter - all within a transaction. ...
    (microsoft.public.dotnet.framework.adonet)
  • RE: Visual Studio 2008 TableAdapters and Transactions
    ... As for the "Transaction" property of the VS generated TableAdapter, ... As you can see, when you set its value, it will update all the Command ... Microsoft MSDN Online Support Lead ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Strongly typed dataset and transactions
    ... The problem is that the automatically generated TableAdapter from the xsd ... to the InsertCommand, so you cannot set its transaction! ... I am using VB.NET to write to an Access database, ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Microsoft: Making a simple process impossible to use in .NET 2.0
    ... I don't see why you need in this to use the command from the tableadapter. ... The connection you can get very easy by getting that from whatever generated ... able to get a count of items contained in a SQL table by creating a ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: TableAdapter with Transactions
    ... bet is to simply open the connection, wrap it in a TransactionScope and run ... sure your transaction isn't promoting the MSDTC. ... SqlTransaction TableAdapter.BeginTransaction ... This way, you can use the TableAdapter to start/end transactions, and ENSURE ...
    (microsoft.public.dotnet.framework.adonet)