transaction and command builder

[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

 

[Stephen Muecke]

VR
I think you need to assign the transaction to all the commands
(Update,Insert and Delete)
After calling Update, commit the transaction and call RollBack if an
exception is thrown
Also, you don't need to call AcceptChanges (the DataAdaptor's Update method
does this automatically)
Stephen

 

[VR]

Thanks, Stephen.

I tried setting other data adapter's commands (before and
after I create command builder) -- no luck. Still the same
problem. Is there a sample code on how to do it?

Thanks.
VR

-----Original Message-----
VR
I think you need to assign the transaction to all the commands
(Update,Insert and Delete)
After calling Update, commit the transaction and call RollBack if an
exception is thrown
Also, you don't need to call AcceptChanges (the DataAdaptor's Update method
does this automatically)
Stephen

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]

Thanks, Kal.

I tried it, no luck.

VR

-----Original Message-----
I found one on target reference, but do not know how to apply it correctly.
Perhaps you do.
http://www.dotnet247.com/247reference/msgs/7/35271.aspx
It is a reference to a thread on this newsgroup.

Kal

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

.

 

[Sudha]

Just try this:

OleDbCommand cmd=new OleDbCommand(SELECT * FROM " +
oDS.Tables[0].TableName);
cmd.Connection=oConnection;
cmd.Transaction=ur transaction object here retreived from connection's
BeginTransaction method.
oDA = new OleDbDataAdapter();
oDA.SelectCommand=cmd;
DataSet oChangesDS = oDS.GetChanges();

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


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

HTH.