Clearing the existing collection and adding but still says - The SqlParameter is already contained by another SqlParameterCollection

Question

I am not able to figure out whats wrong with this code. i am clearing all the parameters and then adding them but it still gives me the error saying "The SqlParameter is already contained by another SqlParameterCollection."

Please help

            using (SqlConnection m_Connection = Class_SetApplicationEnviroment.Get_Sql_Connection())
            {
                m_Connection.Open();


                SqlCommand oSqlCommand = new SqlCommand(m_spName, m_Connection);
                oSqlCommand.CommandType = CommandType.StoredProcedure;

                if (m_Parameters.Length > 0)
                {
                    //SQLDataAdapter.SelectCommand.Parameters.Clear();
                    oSqlCommand.Parameters.Clear();

                    foreach (SqlParameter oParam in m_Parameters)
                    {
                        if (oParam != null)
                        {
                            // Check for derived output value with no value assigned
                            if ((oParam.Direction == ParameterDirection.InputOutput ||
                                 oParam.Direction == ParameterDirection.Input) &&
                                (oParam.Value == null))
                            {
                                oParam.Value = null;
                            }
                            oSqlCommand.Parameters.Add(oParam);
                            //SQLDataAdapter.SelectCommand.Parameters.Add(oParam);
                        }
                    }
                }

                //Execute the Stored Procedure
                //SQLDataAdapter.Fill(myTable);
                strReturnValue = oSqlCommand.ExecuteNonQuery().ToString();

                m_Connection.Close();
            }
        }

I guess this is the duplicate you haven't found: http://stackoverflow.com/questions/7837762/the-sqlparameter-is-already-contained-by-another-sqlparametercollection-does-u The problem is not that the parameter is already in this command's parameter-collection but in a different. — Tim Schmelter, Jan 27 '15 at 14:45
inspect what is in m_parameters? My guess is there is a duplicate in there. — Jeremy, Jan 27 '15 at 14:47
@Jeremy: then the exception wouldn't be _"is already contained by another"_ but _"is already contained in this"_. — Tim Schmelter, Jan 27 '15 at 14:49
I did check if the parameters were returning any duplicates.Its not returning any duplicates. But its happening for the very first parameter that is being added(after clearing the parameters). — Surya Garimella, Jan 27 '15 at 14:51
Thanks guys for all the help. I just re-ran the application by closing visual studio and it seems to have worked. Appreciate all your help..!!! — Surya Garimella, Jan 27 '15 at 14:58

score 0 · Answer 1 · answered Jan 27 '15 at 16:06

As I can see your m_Parameters is a private collection of parameters in your class.

When you instance this class, and run this code for the first time, you create a command, and add the parameters of this collection to a newly created command Parameters collection.

On the second run, you create a new different command, and try to add the parameters to the new, fresh command. And, as your parameters were already attached to the old command's collection, you get taht error.

You can create a single command as a member of your class, attach the parameters to its collection and reuse it as many times as you want. Or you can create a new collcetion of parameter for each command execution. Perhaps (I'm not sure) if you run this line oSqlCommand.Parameters.Clear(); just after closing the connection, they'll be "freed up" and can be used on the next execution. You can try this first

Clearing the existing collection and adding but still says - The SqlParameter is already contained by another SqlParameterCollection

1 Answers1