Hi Bonnie,

Can you try calling the DeriveParameters method before assigning the transaction property? The CommandBuilder is actually making a call to the database to retrieve the information and it is trying to do that in a transaction. Try it and see if it makes a difference.

>I have two custom methods in my DataAccess base class for setting the SqlCommand Parameters for StoredProc calls. The first method, SetBasicParameters(), works without a problem ... because all the columns in a table correspond to the parameters in the SP, I can simply spin through the columns in the DataTable being updated and Add the parameters to the SqlCommand.Parameters collection.
>
>However, in the case where the table's columns differ from the expected parameters in the SP, a second method, which I call SetNamedParameters(), must be used that will make use of the SqlCommandBuilder.DeriveParameters(MySqlCommand) method. What my SetNamedParameters() method does is after using the .DeriveParameters() method (which automatically Adds a given SP's parameters to the SqlCommand.Parameters collection), for each Parameter in the collection, it spins through all the columns in the DataTable to see if that Parameter name exists as a Column Name ... if not, it removes it from the ParameterCollection. This works fine when the table being updated is the only thing being updated. But, when the whole thing is wrapped up in a Transaction, I get an error when it attempts to execute the .DeriveParameters() method. Here's the error message:
>
>"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."
>
>But it *does* have a transaction object, that's why I can't figure out what the heck the problem is.
>
>Here's some sample (simplified) code:
>

>// First the connection, transaction and command is set up:
>this.oConnection.Open();
>SqlTransaction oTrans = this.oConnection.BeginTransaction();
>SqlCommand Command = new SqlCommand();
>Command.CommandType = CommandType.StoredProcedure;
>Command.Connection = this.oConnection;
>Command.Transaction = oTrans;
>
>// Then each individual table in the DataSet is updated like this:
>Command.CommandText = "MyStoredProcForTable1";
>this.SetBasicParamters(Command, Table1, Table1.Rows[0]);
>Command.ExecuteNonQuery();
>
>Command.CommandText = "MyStoredProcForTable2";
>this.SetBasicParamters(Command, Table2, Table2.Rows[0]);
>Command.ExecuteNonQuery();
>
>// the above work fine because I'm using the SetBasicParameters() method
>// it's when it hits this next one (which uses the other method, SetNamedParameters)
>// that it crashes:
>Command.CommandText = "MyStoredProcForWeirdTable";
>this.SetNamedParameters(Command, WeirdTable, WeirdTable.Rows[0]);
>Command.ExecuteNonQuery();
>
>// it crashed on the command to DeriveParameters:
>protected void SetNamedParameters(SqlCommand Command, DataTable Table, DataRow Row)
>{
>  Command.Parameters.Clear();
>  SqlCommandBuilder.DeriveParameters(Command);
>
>  // ... rest of method
>}
>

>
>Does anyone have *any* idea what's going on and how I can get around this?
>
>TIA!!
>~~Bonnie