locked
the sqlparameter is already contained by another sqlparametercollection RRS feed

  • Question

  • User-617859429 posted

    SqlParameter[] parameters = new SqlParameter[2];

    parameters[0] = new SqlParameter("@UserName", DBNull.Value);

    parameters[0].Value = UserName;

    parameters[1] = new SqlParameter("@B", DBNull.Value);

    parameters[1].Value = "Test1";

    SqlHelper.ExecuteNonQuery(AppConnectionString.Get(ConnectionAliasName.Get()), "SP1", parameters);

    parameters[1].Value = "Test2";

    SqlHelper.ExecuteNonQuery(AppConnectionString.Get(ConnectionAliasName.Get()), "SP1", parameters); over here o get the above error in subject line. the value does get change to Test2 in parametere1. but get error --> the sqlparameter is already contained by another sqlparametercollection. I want to use same parameter with different value....

    Tuesday, September 18, 2018 4:53 PM

All replies

  • User753101303 posted

    Hi,

    As pointed by the error message it seems you are passing an array of parameters which behind the scene is likely used by a SqlCommand (which is not disposed properly ?)

    Then you are using the same array of parameters for another call which tries to add them to ANOTHER SqlCommand.Parameters collection.

    More likely you have to fix code found in your SqlHelper.ExecuteNonQuery method... SqlCommand.Dispose could be enough. At worst you'll have to call SqlCommand.Parameters.Clear();

    Tuesday, September 18, 2018 5:17 PM
  • User-893317190 posted

    Hi muhammadazeemazam ,

    As PatriceSc  has said,two SqlParamterCollections has the same sqlparameter reference when you execute your ExecuteNonQuery method the second time.

    I have the code below which  has the same problem as yours.

    private static string constr = ConfigurationManager.ConnectionStrings["supplyModel"].ConnectionString;
            protected void Page_Load(object sender, EventArgs e)
            {
                string sql = "update Suppliers set city=@city where Sid=@id";
                SqlParameter[] sqlParameters = new SqlParameter[]
                {
                    new  SqlParameter("city",SqlDbType.NVarChar,50){Value="Heui"},
                    new SqlParameter("id",SqlDbType.Int){Value=6}
                };
                using (SqlConnection con = new SqlConnection(constr))
                {
                    using (SqlCommand com = new SqlCommand(sql, con))
                    {
                      
                        // here the first SqlParamterCollection owns sqlParameters
                        com.Parameters.AddRange(sqlParameters);
                       
                        con.Open();
                        com.ExecuteNonQuery();
                     
                    }
                }
       
                SqlParameter[] sqlParameters1 = new SqlParameter[]
              {
                    new  SqlParameter("city",SqlDbType.NVarChar,50){Value="Heui"},
                    new SqlParameter("id",SqlDbType.Int){Value=6}
              };
               
                using (SqlConnection con = new SqlConnection(constr))
                {
                    using (SqlCommand com = new SqlCommand(sql, con))
                    {
                       // here the second SqlParameterCollection owns sqlParameters
                        com.Parameters.AddRange(sqlParameters);
                        con.Open();
                        com.ExecuteNonQuery();
                      
    
                    }
                }
    
            }

    So you should  clear the parameters  after you runs com.ExecuteNonQuery to make that only one SqlParamterCollection onws your sqlParamters just as PatriceSc has suggested.

    using (SqlConnection con = new SqlConnection(constr))
                {
                    using (SqlCommand com = new SqlCommand(sql, con))
                    {
                       // here the second SqlParameterCollection
                        com.Parameters.AddRange(sqlParameters);
                        con.Open();
                        com.ExecuteNonQuery();
                        com.Parameters.Clear();
    
                    }
                }

    Or If you don't want to change your SqlHelper, you could use different sqlParameter every time you exccute SqlHelper.ExecuteNonQuery

     string sql = "update Suppliers set city=@city where Sid=@id";
                SqlParameter[] sqlParameters = new SqlParameter[]
                {
                    new  SqlParameter("city",SqlDbType.NVarChar,50){Value="Heui"},
                    new SqlParameter("id",SqlDbType.Int){Value=6}
                };
                using (SqlConnection con = new SqlConnection(constr))
                {
                    using (SqlCommand com = new SqlCommand(sql, con))
                    {
                      
                        // here the first SqlParamterCollection owns sqlParameters
                        com.Parameters.AddRange(sqlParameters);
                       
                        con.Open();
                        com.ExecuteNonQuery();
                    
    
                    }
                }
               
                sqlParameters = new SqlParameter[]
                 {
                    new  SqlParameter("city",SqlDbType.NVarChar,50){Value="Heui"},
                    new SqlParameter("id",SqlDbType.Int){Value=6}
                 };
                using (SqlConnection con = new SqlConnection(constr))
                {
                    using (SqlCommand com = new SqlCommand(sql, con))
                    {
                       // here the second SqlParameterCollection
                        com.Parameters.AddRange(sqlParameters);
                        con.Open();
                        com.ExecuteNonQuery();
                      
    
                    }
                }

    Best regards,

    Ackerly Xu

    Wednesday, September 19, 2018 4:19 AM