still having problems with transactions

  • Thread starter Thread starter VR
  • Start date Start date
V

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
 
VR said:
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."
Click to expand...

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 = "]";
Click to expand...
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;
Click to expand...

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;
Click to expand...

David
 
Thanks, David.

It worked.

VR
-----Original Message-----

VR said:
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."
Click to expand...

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 = "]";
Click to expand...
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 =
Click to expand...
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;
Click to expand...

David



.
Click to expand...
 
Back
Top