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

avatar 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

avatar image

Scot Hauder
6.4k 13 16 22

(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

SQL Server Central

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

Topics:

x502
x73
x25

asked: Mar 15, 2010 at 12:06 AM

Seen: 2450 times

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

Copyright 2016 Redgate Software. Privacy Policy