The CommandBuilder does not actually generate the updating logic when you instantiate it. If you instantiate a CommandBuilder object and later call DataAdapter.Update, the CommandBuilder will not actually build the updating logic until you call the DataAdapter object's Update method. When you call DataAdapter.Update, the CommandBuilder will fetch the required metadata from the database using the DataAdapter object's SelectCommand. You have not associated the Command object in the SelectCommand property with the newly created transaction. As a result, the CommandBuilder cannot use the SelectCommand and the CommandBuilder throws an exception. To get around this, you need to force the CommandBuilder to generate updating logic before starting the transaction. You can do this by calling the CommandBuilder object's GetUpdateCommand, GetInsertCommand, and GetDeleteCommand method. You can then associate the Command objects that the CommandBuilder generated with the new Transaction object using the following sample code, and the DataAdapter will submit the updates within the transaction:

string strConn, strSQL;
...
DataTable tbl = new DataTable();
OleDbConnection cn = new OleDbConnection(strConn);
OleDbDataAdapter da = new OleDbDataAdapter(strSQL,cn);
OleDbCommandBuilder cb = new OleDbCommandBuilder(da);
cn.Open();
cb.GetUpdateCommand();
da.fill(tbl);
OleDbTransaction txn = cn.BeginTransaction();
cb.GetUpdateCommand().Transaction = txn;
cb.GetInsertCommand().Transaction = txn;
cb.GetDeleteCommand().Transaction = txn;
da.Update(tbl);
txn.Commit();
cn.Close();

>Cathi,
>
>Yes I am.
>
>Doug,
>
>Are you using the CommandBuilder to generate the Update and Insert statements?
>
>>I am trying to get nested transactions to work using 2 sql command objects
>>
>>SqlTransaction myTrans = SqlConn.BeginTransaction();
>>
>>HeaderCmd = poh.InsertCommand;
>>HeaderCmd.Transaction = myTrans;
>>
>>if(poh.UpdateInsertdata(HeaderCmd, Header))
>>{
>> DetailsCmd = pod.InsertCommand;
>> DetailsCmd.Transaction = myTrans;
>>
>> if(pod.UpdateInsertData(DetailsCmd, Details))
>> GoodTrans= true;
>>}
>>if(GoodTrans)
>> myTrans,Commit();
>>else
>> myTrans.Rollback();
>>
>>
>>Both of these command share the same transaction.
>>
>>I am forcing an error to occur during the detail portion of the code. But when I hit
>>myTrans.Rollback() I am greeted with:
>>
>>The ROLLBACK TRANSACTIOn request has no corresponding BEGIN TRANSACTION.
>>
>>Anyone have a clue?
>>
>>Doug
>-----------------------------------------
>
>Cathi Gero, CPA
>Consultant and Development Director
>Prenia Software & Consulting Services
>Microsoft MVP
>cgero@prenia.com
>www.prenia.com
>www.foxcentral.net