x

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, 2010 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, 2010 at 02:26 AM

Scot Hauder gravatar image

Scot Hauder
6k 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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x476
x60
x21

asked: Mar 15, 2010 at 12:06 AM

Seen: 2113 times

Last Updated: Mar 15, 2010 at 09:31 AM