Within Transaction: This SQLTransaction has completed... RRS feed

  • Question

  • When calling dataset.UpdateAll, I somtimes get: "This SQLTransaction has closed, it is no longer usable."

    I am not entirely certain what conditions cause this error, though it seems to be when a large amount of data is being transmitted.

    What do I need to do to prevent this?

     corpusTableAdapter.Adapter.UpdateBatchSize = BATCH_SIZE;
     corpusTableAdapter.Adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.None;
     corpusTableAdapter.Adapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
     corpusTableAdapter.Adapter.DeleteCommand.UpdatedRowSource = UpdateRowSource.None;
    //...Plus about a dozen more tables set like this and added to tableAdapterManager...
    //Now, try to update to database...
       int rowsChanged = tableAdapterManager.UpdateAll(dataSet1);
     catch (Exception ex)
       //This SQLTransaction has closed, it is no longer usable 

    Saturday, April 9, 2011 1:30 PM


  • It does seem like it may be a time-out, because it happens on attempting to update a large volume of data, though I don't see where I can adjust this parameter.

    Hi A.Russell,


    Sorry for my carelessness. If it only happens when you update a large volume of data, the reason what you mentioned is very possible. So, could you try to set the CommandTimeOut value manually? Like this:

     corpusTableAdapter.Adapter.InsertCommand.CommandTimeout = <fill inyour value here>; 

     corpusTableAdapter.Adapter.UpdateCommand.CommandTimeout = <fill inyour value here>; 

     corpusTableAdapter.Adapter.DeleteCommand.CommandTimeout = <fill inyour value here>; 

    i hope this time it can work fine.


    Good day,

    Jackie Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by A.Russell Tuesday, April 12, 2011 10:23 AM
    Tuesday, April 12, 2011 7:21 AM

All replies