1

I have developed a simple Windows application. To reuse code I have created a separate class file for database management named as DM.

All is working fine except I am getting this error which is not being solved.

The error looks similar to the link The SqlParameter is already contained by another SqlParameterCollection - Does using() {} cheat?

but the solution is not working for me.

My code:

SqlParameter[] SQL_Params =
        {
            new SqlParameter("@app_srno", textBox1.Text.Trim()),
            new SqlParameter("@resetBy", username_Form)
        };

queryString = "insert into Record_Reset (app_srno, resetBy) values(@app_srno, @resetBy)";
DM.execute_query(queryString, SQL_Params);

// sqlParams = null;

queryString = "update [KYC_Index] set [transform_int] = 'N',[transform_int_by] = null,[transform_pic] = '',[transform_poi] = '',[transform_poa] = '',[transform_by]=null,[qc_int]='N',[qc_int_by]=null,[qc]='N',[qc_by]=null where [srno] = @app_srno";

DM.execute_query(queryString, SQL_Params);  // error happens here 

The code in my class file DM

public void execute_query(string query, SqlParameter[] sqlparams = null)
{
    using (SqlConnection connection = new SqlConnection(connectString))
    {
        using (SqlCommand sqlcmd = new SqlCommand(query))
        {
            sqlcmd.Connection = connection;

            if (sqlparams == null)
            {
                sqlcmd.CommandType = CommandType.Text;
            }
            else
            {
                sqlcmd.CommandType = CommandType.StoredProcedure;

                foreach (SqlParameter p in sqlparams)
                {
                    sqlcmd.Parameters.Add(p);
                }
            }

            try
            {
                connection.Open();
                sqlcmd.ExecuteNonQuery();
                sqlparams = null;
                sqlcmd.Parameters.Clear();
                connection.Close();
            }
            catch (Exception)
            {
                //MessageBox.Show(ex.Message);
            }
        }
    }
}  
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Danish Khan
  • 63
  • 1
  • 7
  • 2
    You can't reuse parameters in that way - they "remember" the command they were associated with. – stuartd May 23 '17 at 11:05
  • @stuartd I should create a new parameter and pass? – Danish Khan May 23 '17 at 11:06
  • If clearing the command parameters isn't working, then yes. – stuartd May 23 '17 at 11:07
  • Why you want to reuse sql-parameters? That's just a source for errors, f.e. multi threading issues or just wrong values assigned. Really, i advise strongly against reusing of ADO.NET objects like connections, ccommand or parameters. Create and use them in methods with a meaningful name and don't use `execute_query` methods. – Tim Schmelter May 23 '17 at 11:27

0 Answers0