none
Exception - The operation is not valid for the state of the transaction - occurring on second update of database. RRS feed

  • Question

  • On the second call to Update on a TableAdapter object for a typed dataset I am receiving the exception shown in the title of the post. The inner exception is a Transaction Timeout.

    Here is the code within the Transaction:

    // Start database transaction.
            using (System.Transactions.TransactionScope transaction = new System.Transactions.TransactionScope())
            {
    
                // Create new customer.
                CustomersBLL customerOperations = new CustomersBLL();
                customerOperations.InsertCustomer(quote.CurrentDate, null, null, null, null, null, null, null, null, null);
    
                // Prepare to insert Quote.
                // Retrieve CustomerID. 
                CustomerTable.Constraints.Clear();
    
                CustomerTable = customerOperations.GetNewestCustomer();
                quote.CustomerID = (int)CustomerTable.Rows[0][0];
    
                // Get word count and quote amount.
                quote.GetWordCount();
                quote.GetQuoteAmount();
    
                // Insert Quote
                QuotesBLL quoteOperations = new QuotesBLL();
                quoteOperations.InsertQuote(quote.CustomerID, quote.QuoteAmount, quote.CurrentDate);
    

    The exception is occurring after calling the InsertQuote method of the quoteOperations object.

    Here is the code from the Business Logic Layer where the actual database table is updated:

    [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Insert, true)]
    public bool InsertQuote(int customerID, decimal quoteAmount, DateTime quoteDate)
    {
        // Create new QuoteRow instance.
        Job.QuotesDataTable quotes = new Job.QuotesDataTable();
        Job.QuotesRow quote = quotes.NewQuotesRow();
    
        // Assign values to QuotesRow instance.
        quote.CustomerID = customerID;
    
        quote.QuoteAmount = quoteAmount;
    
        quote.QuoteDate = quoteDate;
    
        // Add the new Quote
        quotes.AddQuotesRow(quote);
        int rowsAffected = Adapter.Update(quotes);
    
        // Return true if row was affected.
        return rowsAffected == 1;
    }
    


    The error occurs on this line in the BLL:


    int rowsAffected = Adapter.Update(quotes);
    

     

    Thursday, December 29, 2011 12:45 AM

Answers

  • Darren -- Allen means the TableAdapter, not the Typed DataSet.

    I don't use TableAdapters, but I have created a Typed DataSet such that TableAdapters are generated (there are ways to generate Typed DataSets without the TableAdapters) and I use it for testing stuff like forum questions.

    If you look at the designer-generated TableAdapter stuff, it looks like the .Update() method *does* do a check to see if the Connection is already open and opens it if it wasn't. It also looks like it only closes it after it's done updating if the Connection was not previously open.

    So, how do you "manually" open and close the connection? Well, it looks like you can do this with the TableAdapterManager.

    So basically, you'd open your connection first, then do all your inserts and updates, then close the connection.

    I haven't tried any of this using the TableAdapter and TableAdapterManager ... I just looked at the generated code. However, I have done this sort of thing myself all the time with DataAdapters (and really, a TableAdapter is simply a wrapper around DataAdapters). This *should* do the trick for you.

    UPDATE: Oh, and don't forget to close your TransactionScope with transaction.Complete().


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    • Edited by BonnieBMVP Sunday, January 1, 2012 12:48 AM
    • Proposed as answer by Allen_MSDNModerator Wednesday, January 4, 2012 1:51 AM
    • Marked as answer by darrenlc Wednesday, January 4, 2012 5:18 AM
    Sunday, January 1, 2012 12:40 AM

All replies

  • Hi darrenlc,

    Welcome to MSDN Forum.

    Based on the issue, you have inserted the customer successfully, but when you want to insert the quote, an exception was thrown. This problem may caused by the fact that strongly typed datasets open and close connections to the same database for every single operation. So please try to modify something on your connection.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Friday, December 30, 2011 3:06 AM
    Moderator
  • Modify what? I don't see where the typed dataset exposes the connection.
    • Edited by darrenlc Saturday, December 31, 2011 8:17 PM
    Saturday, December 31, 2011 8:02 PM
  • Darren -- Allen means the TableAdapter, not the Typed DataSet.

    I don't use TableAdapters, but I have created a Typed DataSet such that TableAdapters are generated (there are ways to generate Typed DataSets without the TableAdapters) and I use it for testing stuff like forum questions.

    If you look at the designer-generated TableAdapter stuff, it looks like the .Update() method *does* do a check to see if the Connection is already open and opens it if it wasn't. It also looks like it only closes it after it's done updating if the Connection was not previously open.

    So, how do you "manually" open and close the connection? Well, it looks like you can do this with the TableAdapterManager.

    So basically, you'd open your connection first, then do all your inserts and updates, then close the connection.

    I haven't tried any of this using the TableAdapter and TableAdapterManager ... I just looked at the generated code. However, I have done this sort of thing myself all the time with DataAdapters (and really, a TableAdapter is simply a wrapper around DataAdapters). This *should* do the trick for you.

    UPDATE: Oh, and don't forget to close your TransactionScope with transaction.Complete().


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    • Edited by BonnieBMVP Sunday, January 1, 2012 12:48 AM
    • Proposed as answer by Allen_MSDNModerator Wednesday, January 4, 2012 1:51 AM
    • Marked as answer by darrenlc Wednesday, January 4, 2012 5:18 AM
    Sunday, January 1, 2012 12:40 AM