SQLParameter already contained...

[ultraman]

Hi guys !

 

I have this method in one of my class that executes SQLCommands within transaction or not.

 

public void ExecuteSqlCommand(string CommandText, params Object[] Parameters)
       {
           //Check if the connexion is active
           if(cSQLConnection.State != ConnectionState.Open)
           {
               string strErrMessage = Global.GetCulturedErrCode("sql001");
               throw new ConnectionIsClosed(Global.GetMessage(strErrMessage));
           }

           SqlCommand cmd = cSQLConnection.CreateCommand();
           //If the user previously called BeginTransaction();
           if(cTransaction != null)
           {
               cmd.Transaction = cTransaction;
           }
           cmd.CommandText = CommandText;

           //Loops thru all parameters and add them to the command object
           cmd.Parameters.Clear();
           try
           {
               foreach(SqlParameter p in Parameters)
               {
                   cmd.Parameters.Add(p);
               }
           }
           catch (Exception ex)
           {
               //The object have to be SqlParameter type....
               cmd.Dispose();
               throw ex;
           }

           //Prepare and execute the command
           try
           {
               cmd.Prepare();
               cmd.ExecuteNonQuery();
           }
           catch (Exception ex)
           {
               cmd.Dispose();
               throw ex;
           }


           cmd.Dispose();
       }

 

The problem is that if I called this function twice with the same parameter name, I have this exception thrown :

"The SqlParameter with ParameterName '@GunId' is already contained by another SqlParameterCollection".

 

Here's a call sample (objSQL is the instance of my class) :

 

SqlParameter pGun = new SqlParameter("@GunId", SqlDbType.Char, 2);
                   pGun.Value = GunId.Trim();
                   SqlParameter pDep = new SqlParameter("@DepId", SqlDbType.Int);
                   pDep.Value = DepId;
                   SqlParameter pPC = new SqlParameter("@PCId", SqlDbType.Int);
                   pPC.Value = intPCId;

strRequest = "INSERT INTO GUN_POSTE (GUN_ID, PC_UNIQUE_ID)" +
                           " VALUES(@GunId, @PCId)";
objSQL.ExecuteSqlCommand(strRequest, pGun, pPC);

strRequest = "INSERT INTO POSTE_DEPARTEMENT (POS_DEP_DEP_ID, POS_DEP_POSTE_ID)" +
                           " VALUES(@DepId, @GunId)";
objSQL.ExecuteSqlCommand(strRequest, pDep, pGun);

[Robby]

You're using the same instance of the object, you should create a NEW instance if you want to call it twice. (Don't forget to dispose the previous one if you're done with it)

[ultraman]

Thanx a lot ! It's working fine now. But I can't dispose SQLParameter objects since the class doesn't inherit from iDisposable. Therefore, there's no Dispose() method....