System.ArgumentException: The SqlParameter is already contained by another SqlParameterCollection

 

Error:

System.ArgumentException: The SqlParameter is already contained by another SqlParameterCollection

 

Solution:

In .Net Applications if you are using the ADO.Net Objects and have to use SQL Query to talk to your database, it’s always better to use Parameterized Queries over dynamic normal queries. Parameterized are more secure than dynamic queries. Also Parameterized query can help you avoid your SQL Query from breaking in case your field values have single quotes or apostrophes (’) in it.

So if you have decided to use Parameterized Query and get the above error then this is how you can solve it. The reason you are getting the above error is because you are not clearing the existing Parameters from you SQL Command after executing it, so you need to add the following code to clear the SQL Command’s  existing parameters.

cmd.Parameters.Clear();

 

See Code example below.

 

public DataTable GetData(DataTable myExtract)

        {

 

            using (SqlConnection conn = new SqlConnection(connString))

            {

                SqlParameter param1 = new SqlParameter("@Address", SqlDbType.VarChar, 50);

                

                        using (SqlCommand cmd = new SqlCommand(SQLQuery, conn))

                        {

                            conn.Open();

 

                            foreach (DataRow row in myExtract.Rows)

                            {

                               

 

                                SQLQuery = "Update MyTableName";

                                SQLQuery += @" Set address_line1 = @Address

                                            Where Account_Number = '1234'";

 

                                param1.Value = Address1; cmd.Parameters.Add(param1);

                                cmd.CommandText = SQLQuery;

                                cmd.ExecuteNonQuery();

                                cmd.Parameters.Clear();

 

                            }

                        }

 

            }

        }

 

 

Hope that helps someone out there