x
login about faq Site discussion (meta-askssc)

Sql Transaction Has completed;its is no longer usable.

Hi,

I m getting this error when I m running update & insert query in transaction. while inserting,in middle of transaction throw this error and few records insert in table. while update,getting this error every time and records not updating. please help me if any one have solution for this problem.

Here is the code:

if(con!=null)                    
                {                    
                    try{                     
                    
                    
                         cmd = new SqlCommand();                    
                         cmd.Connection = con;                    
                         trans = con.BeginTransaction();                    
                         msg="Begin Transaction";                    
                         cmd.Transaction = trans;                    
                    
                        for(int i = 0; i<arry.Count ; i++)                    
                         {                    
                             try                    
                             {                    
                                 cmd.CommandText = arry[i].ToString() + "";                    
                                 //if(trans!=null && con!=null)                        
                                 re += cmd.ExecuteNonQuery();                    
                                                                  }                    
                             catch (Exception ex)                    
                             {                    
                                 re = -1;                    
                                 trans.Rollback();                    
                                 throw new Exception("Catched ExecuteNonQuery exception : " + ex.Message+"Message:"+msg);                    
                             }                    
                    
                         }                    
                         try                    
                         {                    
                             msg = "Commit";                    
                             trans.Commit();                    
                         }                    
                         catch (Exception ex)                    
                         {                    
                             re = -1;                    
                             trans.Rollback();                    
                             throw new Exception("Catched Transaction commit exception : " + ex.Message + "Message:" + msg);                    
                         }                    
                    
                    }                     
                    catch (SqlException sqlexception)                    
                    {                    
                        trans.Rollback();                    
                        re = -1;                    
                        throw new Exception("SQLBatch.executeBatch :" + sqlexception.Message);                    
                    
                    }                    
                    catch (Exception ex)                    
                    {                    
                        trans.Rollback();                    
                        re = -1;                    
                        throw new Exception("SQLBatch.executeBatch : " + ex.Message);                    
                    }                    
                    finally                    
                    {                    
                        try                    
                        {                    
                            con.Close();                    
                            con = null;                    
                            trans.Dispose();                    
                            trans = null;                    
                        }                    
                        catch (Exception ex)                    
                        {                    
                    
                            re = -1;                    
                            throw new Exception("Catched an exception when trying to rollback : " + ex.Message);                    
                        }                    
                    }                    
                }                       
            }                    
            catch (Exception ex)                    
            {                    
                re = -1;                    
                throw new Exception("Catched exception : " + ex.Message+ "Message: " +msg);                    
            }                    
        }                    
more ▼

asked Mar 15 '10 at 12:06 AM in Default

kirti 1 gravatar image

kirti 1
1 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Kirti, you need to get rid of all the try/catch blocks except for ONE that wraps the entire transaction. Either the transaction is going to succeed or it isn't, you can still set the msg to what you are about to attempt, but you only need one try block. In your code, if the for/loop fails, it rolls back the transaction, then drops down and tries to commit/rollback when the transaction doesn't exist anymore.

more ▼

answered Mar 15 '10 at 02:26 AM

Scot Hauder gravatar image

Scot Hauder
5.7k 13 15 18

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments



Facebook logo Follow Ask SSC on Facebook
Find Ask SSC on Google+
linkedin logo Find us on LinkedIn

Topics:

x454
x51
x17

asked: Mar 15 '10 at 12:06 AM

Seen: 1436 times

Last Updated: Mar 15 '10 at 09:31 AM

Copyright © 2002-2012 Simple Talk Publishing. All Rights Reserved. If you have any queries, please contact the site administrators.
Ask SQL Server Central is a community service provided by Red Gate.