Transactions and calling Update on DataAdapters 
Author Message
 Transactions and calling Update on DataAdapters

I'm trying to implement Transactions in a method that saves to the data
source changes in several DataTables in a DataSet.

To update the database, I'm using a CommandBuilder on each DataAdapter.
After calling BeginTransaction on the Connection, I'm trying to set the
Transaction property of each Command object of the CommandBuilder to the
Transaction object returned by BeginTransaction, as the associated help
screen's example demonstrates.

For some reason, though, the method always throws a
System.InvalidOperationException on the command:

cb.GetInsertCommand.Transaction = _transaction

The message displayed is:

"Execute requires the command to have a transaction object when the
connection assigned to the command is in a pending local transaction.  The
Transaction property of the command has not been initialized."

Why does the code think I'm trying to Execute a command when I'm only trying
to set its Transaction property?

Any insight will be very much appreciated.

Orion Carrier
Canadian Forest Service



Sat, 29 Jan 2005 09:07:11 GMT  
 Transactions and calling Update on DataAdapters
Dim conn As New SqlConnection(ConnectionString)

conn.Open()

Dim Trans As SqlTransaction = conn.BeginTransaction

cb.GetInsertCommand.Connection = conn
cb.GetInsertCommand.Transaction = Trans

I have not used the command builder, but I'm assuming that GetInsertCommand
returns a Command object I usally use the above with

myAdapter.updatecommand


Quote:
> I'm trying to implement Transactions in a method that saves to the data
> source changes in several DataTables in a DataSet.

> To update the database, I'm using a CommandBuilder on each DataAdapter.
> After calling BeginTransaction on the Connection, I'm trying to set the
> Transaction property of each Command object of the CommandBuilder to the
> Transaction object returned by BeginTransaction, as the associated help
> screen's example demonstrates.

> For some reason, though, the method always throws a
> System.InvalidOperationException on the command:

> cb.GetInsertCommand.Transaction = _transaction

> The message displayed is:

> "Execute requires the command to have a transaction object when the
> connection assigned to the command is in a pending local transaction.  The
> Transaction property of the command has not been initialized."

> Why does the code think I'm trying to Execute a command when I'm only
trying
> to set its Transaction property?

> Any insight will be very much appreciated.

> Orion Carrier
> Canadian Forest Service



Sat, 29 Jan 2005 20:18:26 GMT  
 Transactions and calling Update on DataAdapters
Hi, Robert.  Thank you for your reply.

Now, the only difference between my code and yours was that you explicitly
set the CommandBuilder's command's connection property.  So I tried that,
and now I get the same error as before, but on the line where I set the
connection object (sample code below).

It doesn't seem to want me to do *anything* with the CommandBuilder's
Command objects once I BeginTransaction on this connection object.  Any
ideas?  Thank you very much for your help.  :)

Here's my code.  I hope I'm not giving you too much detail.  I just want to
demonstrate the context of the thrown exception:

'existing instance variable:

    'Dim _cn as ODBCConnection

    'Dim _adapterList as ArrayList   'of ODBCDataAdapters

Dim cb as ODBCCommandBuilder

Dim da as ODBCDataAdapter

Dim enumr as IEnumerator

If _cn.State <> ConnectionState.Open Then

    _cn.Open()

End If

_transaction = _cn.BeginTransaction

enumr = Me._adapterList.GetEnumerator

While enumr.MoveNext

    da = CType(enumr.Current, OdbcDataAdapter)

    cb = New OdbcCommandBuilder(da)

    With cb

        .GetInsertCommand.Connection = _cn   '***EXCEPTION THROWN HERE***

        .GetInsertCommand.Transaction = _transaction

        .GetUpdateCommand.Connection = _cn

        .GetUpdateCommand.Transaction = _transaction

        .GetDeleteCommand.Connection = _cn

        .GetDeleteCommand.Transaction = _transaction

    End With

    With da.TableMappings(0)

        da.Update(_ds, .SourceTable)

    End With

End While

Thanks again, Robert.

Orion Carrier
Canadian Forest Service


Quote:
> Dim conn As New SqlConnection(ConnectionString)

> conn.Open()

> Dim Trans As SqlTransaction = conn.BeginTransaction

> cb.GetInsertCommand.Connection = conn
> cb.GetInsertCommand.Transaction = Trans

> I have not used the command builder, but I'm assuming that
GetInsertCommand
> returns a Command object I usally use the above with

> myAdapter.updatecommand



> > I'm trying to implement Transactions in a method that saves to the data
> > source changes in several DataTables in a DataSet.

> > To update the database, I'm using a CommandBuilder on each DataAdapter.
> > After calling BeginTransaction on the Connection, I'm trying to set the
> > Transaction property of each Command object of the CommandBuilder to the
> > Transaction object returned by BeginTransaction, as the associated help
> > screen's example demonstrates.

> > For some reason, though, the method always throws a
> > System.InvalidOperationException on the command:

> > cb.GetInsertCommand.Transaction = _transaction

> > The message displayed is:

> > "Execute requires the command to have a transaction object when the
> > connection assigned to the command is in a pending local transaction.
The
> > Transaction property of the command has not been initialized."

> > Why does the code think I'm trying to Execute a command when I'm only
> trying
> > to set its Transaction property?

> > Any insight will be very much appreciated.

> > Orion Carrier
> > Canadian Forest Service



Sun, 30 Jan 2005 02:07:43 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Error with dataadapter.update(dataset) in vb

2. An ADO.Net DataAdapter Update Philosophy Question for the MS gurus

3. DataAdapter.Update and SQL Server Default Values

4. DataAdapter updating

5. Update queries in transactions

6. Update Indexed Field In Transaction

7. DAO Bug? Updates in transactions

8. OleDB - DataRow update and Transaction

9. Updating 2 Oracle tables in one transaction?

10. Transaction Processing in Explicit updates

11. transaction problem while update

12. Transaction Processing in Explicit updates

 

 
Powered by phpBB® Forum Software