still having problems with transactions

[VR]

Hi,

I am getting the following exception when trying to update
a dataset using data adapater and a command builder:

"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."

Here is the code:

DataSet oDS = ...;
OleDbConnection oConnection = ...;
OleDbTransaction oTransaction = ...;


// without this line it works...
oTransaction = oConnection.BeginTransaction();

OleDbDataAdapter oDA = null;
OleDbCommandBuilder oCmdBuilder = null;


oDA = new OleDbDataAdapter("SELECT * FROM " + oDS.Tables
[0].TableName, oConnection);

DataSet oChangesDS = oDS.GetChanges();

if (oChangesDS != null)
{
oCmdBuilder = new OleDbCommandBuilder(oDA);
oCmdBuilder.QuotePrefix = "[";
oCmdBuilder.QuoteSuffix = "]";


oDA.Update(oDS, oDS.Tables[0].TableName);
oDS.AcceptChanges();
}

.....


If I don't start transaction, everything works.

I tried setting the transaction property of the command
builder:

oCmdBuilder.GetUpdateCommand().Transaction = oTransaction;

and/or Transaction of data adapter:

oDA.SelectCommand.Transaction = oTransaction;

No luck.

Please help.

Thanks,
VR

 

[David Browne]

Hi,

I am getting the following exception when trying to update
a dataset using data adapater and a command builder:

"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."

The answer here is the answer to allmost all questions about the
CommandBuilders.
Get the commandBuilder to spit out the DML commands using .GetXXXXCommand,
alter them in some way, and assign them to the DataAdapter's DML commands
manually.

(see inline)

Here is the code:

DataSet oDS = ...;
OleDbConnection oConnection = ...;
OleDbTransaction oTransaction = ...;


// without this line it works...
oTransaction = oConnection.BeginTransaction();

OleDbDataAdapter oDA = null;
OleDbCommandBuilder oCmdBuilder = null;


oDA = new OleDbDataAdapter("SELECT * FROM " + oDS.Tables
[0].TableName, oConnection);

DataSet oChangesDS = oDS.GetChanges();

if (oChangesDS != null)
{
oCmdBuilder = new OleDbCommandBuilder(oDA);
oCmdBuilder.QuotePrefix = "[";
oCmdBuilder.QuoteSuffix = "]";

oDa.InsertCommand = oCmdBuilder.GetInsertCommand();
oDA.UpdateCommand = oCmdBuilder.GetUpdateCommand();
oDA.DeleteCommand = oCmdBuilder.GetDeleteCommand();

oDa.InsertCommand.Transaction = oTransaction;
oDa.UpdateCommand.Transaction = oTransaction;
oDa.DeleteCommand.Transaction = oTransaction;

oDA.Update(oDS, oDS.Tables[0].TableName);
oDS.AcceptChanges();
}

....


If I don't start transaction, everything works.

I tried setting the transaction property of the command
builder:

oCmdBuilder.GetUpdateCommand().Transaction = oTransaction;

This doesn't work because the DataAdapter will call GetUpdateCommand again
unless you assign a command to the DataAdapters UpdateCommand before the
update.

and/or Transaction of data adapter:

oDA.SelectCommand.Transaction = oTransaction;

David

 

[VR]

Thanks, David.

It worked.

VR

-----Original Message-----

Hi,

I am getting the following exception when trying to update
a dataset using data adapater and a command builder:

"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."

The answer here is the answer to allmost all questions about the
CommandBuilders.
Get the commandBuilder to spit out the DML commands using .GetXXXXCommand,
alter them in some way, and assign them to the DataAdapter's DML commands
manually.

(see inline)

Here is the code:

DataSet oDS = ...;
OleDbConnection oConnection = ...;
OleDbTransaction oTransaction = ...;


// without this line it works...
oTransaction = oConnection.BeginTransaction();

OleDbDataAdapter oDA = null;
OleDbCommandBuilder oCmdBuilder = null;


oDA = new OleDbDataAdapter("SELECT * FROM " + oDS.Tables
[0].TableName, oConnection);

DataSet oChangesDS = oDS.GetChanges();

if (oChangesDS != null)
{
oCmdBuilder = new OleDbCommandBuilder(oDA);
oCmdBuilder.QuotePrefix = "[";
oCmdBuilder.QuoteSuffix = "]";

oDa.InsertCommand = oCmdBuilder.GetInsertCommand();
oDA.UpdateCommand = oCmdBuilder.GetUpdateCommand();
oDA.DeleteCommand = oCmdBuilder.GetDeleteCommand();

oDa.InsertCommand.Transaction = oTransaction;
oDa.UpdateCommand.Transaction = oTransaction;
oDa.DeleteCommand.Transaction = oTransaction;

oDA.Update(oDS, oDS.Tables[0].TableName);
oDS.AcceptChanges();
}

....


If I don't start transaction, everything works.

I tried setting the transaction property of the command
builder:

oCmdBuilder.GetUpdateCommand().Transaction =

oTransaction;

This doesn't work because the DataAdapter will call GetUpdateCommand again
unless you assign a command to the DataAdapters UpdateCommand before the
update.

and/or Transaction of data adapter:

oDA.SelectCommand.Transaction = oTransaction;

David



.