ASP, The SqlParameter is already contained by another SqlParameterCollection

Question

I have a problem when executing a SQL Server stored procedure in my application. I've not had this problem before.

My data class is:

public static string conStr
{
    get { return ConfigurationManager.ConnectionStrings["conn"].ConnectionString; }
}

public static string Provider
{
    get { return ConfigurationManager.ConnectionStrings["conn"].ProviderName; } //Proveedor de DB
}

//DB PROVIDER FACTORY: Objetos de Conexion
public static System.Data.Common.DbProviderFactory dpf
{
    get { return DbProviderFactories.GetFactory(Provider); }
}

private static int ejecutaNonQuery(string StoredProcedure, List<DbParameter> parametros)
{//execute nonquery retorna el numero de columnas afectadas en una dB 
    int Id = 0; //variable para saber cuantos elementos se modificaron despues del ejecutenonquery

    try
    {
        using (DbConnection con = dpf.CreateConnection()) //al usar using la conexion se cerrara automaticamente
        {
            con.ConnectionString = conStr;
            using (DbCommand cmd = dpf.CreateCommand())
            {
                cmd.Connection = con;
                cmd.CommandText = StoredProcedure;
                cmd.CommandType = CommandType.StoredProcedure;

                foreach (DbParameter param in parametros)
                    cmd.Parameters.Add(param);

                con.Open();
                Id = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear(); 
            }
        }
    }
    catch (Exception ex)
    {
        throw ex;
    }
    finally
    { }

    return Id;
}

This is the execution:

public int Lotes_Crear_Consolidado(string cliCodigo, string cajCodigo, int cajNumero, 
                                   int cajConNumero, string cajConCodigo,
                                   string id1Desde, string id1Hasta, 
                                   string id2Desde, string id2Hasta,
                                   string id3Desde, string id3Hasta,
                                   string id4Desde, string id4Hasta,
                                   string id5Desde, string id5Hasta,
                                   string altaPor, string lotEstado, 
                                   DateTime fecEmiLot, DateTime fecCadLot)
{
    List<DbParameter> paramLotesConsol = new List<DbParameter>();

    DbParameter param1 = dpf.CreateParameter();
    param1.DbType = DbType.String;
    param1.Value = cliCodigo;
    param1.ParameterName = "CLIENTE_CODIGO";
    paramLotesConsol.Add(param1);

    DbParameter param2 = dpf.CreateParameter();
    param2.DbType = DbType.String;
    param2.Value = cajCodigo;
    param2.ParameterName = "CAJA_CODIGO";
    paramLotesConsol.Add(param2);

    DbParameter param3 = dpf.CreateParameter();
    param3.DbType = DbType.Int16;
    param3.Value = cajNumero;
    param3.ParameterName = "CAJA_NUMERO";
    paramLotesConsol.Add(param3);

    DbParameter param4 = dpf.CreateParameter();
    param4.DbType = DbType.Int16;
    param4.Value = cajConNumero;
    param4.ParameterName = "CAJA_CONTENIDO_NUMERO";
    paramLotesConsol.Add(param4);

    DbParameter param5 = dpf.CreateParameter();
    param5.DbType = DbType.String;
    param5.Value = cajConCodigo;
    param5.ParameterName = "CAJA_CONTENIDO_CODIGO";
    paramLotesConsol.Add(param5);

    DbParameter param6 = dpf.CreateParameter();
    param6.DbType = DbType.String;
    param6.Value = id1Desde;
    param6.ParameterName = "ID1_DESDE";
    paramLotesConsol.Add(param6);

    DbParameter param7 = dpf.CreateParameter();
    param7.DbType = DbType.String;
    param7.Value = id1Hasta;
    param7.ParameterName = "ID1_HASTA";
    paramLotesConsol.Add(param7);

    DbParameter param8 = dpf.CreateParameter();
    param8.DbType = DbType.String;
    param8.Value = id2Desde;
    param8.ParameterName = "ID2_DESDE";
    paramLotesConsol.Add(param8);

    DbParameter param9 = dpf.CreateParameter();
    param9.DbType = DbType.String;
    param9.Value = id2Hasta;
    param9.ParameterName = "ID2_HASTA";
    paramLotesConsol.Add(param9);

    ........

    DbParameter param26 = dpf.CreateParameter();
    param26.DbType = DbType.String;
    param26.Value = altaPor;
    param26.ParameterName = "ALTA_POR";
    paramLotesConsol.Add(param26);

    DbParameter param27 = dpf.CreateParameter();
    param27.DbType = DbType.String;
    param27.Value = lotEstado;
    param27.ParameterName = "ESTADO";
    paramLotesConsol.Add(param27);

    DbParameter param28 = dpf.CreateParameter();
    param28.DbType = DbType.Date;
    param28.Value = fecEmiLot;
    param28.ParameterName = "FECHA_EMISION_LOTE";
    paramLotesConsol.Add(param28);

    DbParameter param29 = dpf.CreateParameter();
    param29.DbType = DbType.Date;
    param29.Value = fecCadLot;
    param29.ParameterName = "FECHA_CADUCID_LOTE";
    paramLotesConsol.Add(param29);

    return ejecutaNonQuery("LOTES_CREAR_CONSOLIDADO", paramLotesConsol);
}

Then I call it like this:

Caja_Contenido_BL loteBL = new Caja_Contenido_BL();

I must insert data from a gridview to my database, so I run on every row the stored procedure for inserting the data.

And the error I get is:

The SqlParameter is already contained by another SqlParameterCollection

Code:

protected void Lotes_Crear_Consolidado(object sender, EventArgs e)
{
    string altaPor = User.Identity.Name.ToString().ToUpper();

    foreach (GridViewRow grdCajUpd_Row in this.gvwLotConsol_Plant.Rows)
    {
        string cliCod = Convert.ToString(grdCajUpd_Row.Cells[0].Text);
        string cajCod = Convert.ToString(grdCajUpd_Row.Cells[1].Text);
        int cajNum = Convert.ToInt16(grdCajUpd_Row.Cells[2].Text);
        int cajConNum = Convert.ToInt16(grdCajUpd_Row.Cells[3].Text);
        string cajConCod = Convert.ToString(grdCajUpd_Row.Cells[4].Text);

        string id1Desde = Convert.ToString(grdCajUpd_Row.Cells[5].Text);
        string id1Hasta = Convert.ToString(grdCajUpd_Row.Cells[6].Text);
        string id2Desde = Convert.ToString(grdCajUpd_Row.Cells[7].Text);
        string id2Hasta = Convert.ToString(grdCajUpd_Row.Cells[8].Text);
        string id3Desde = Convert.ToString(grdCajUpd_Row.Cells[9].Text);
        string id3Hasta = Convert.ToString(grdCajUpd_Row.Cells[10].Text);
        string id4Desde = Convert.ToString(grdCajUpd_Row.Cells[11].Text);
        string id4Hasta = Convert.ToString(grdCajUpd_Row.Cells[12].Text);
        string id5Desde = Convert.ToString(grdCajUpd_Row.Cells[13].Text);
        string id5Hasta = Convert.ToString(grdCajUpd_Row.Cells[14].Text);
        string id6Desde = Convert.ToString(grdCajUpd_Row.Cells[15].Text);
        string id6Hasta = Convert.ToString(grdCajUpd_Row.Cells[16].Text);
        string id7Desde = Convert.ToString(grdCajUpd_Row.Cells[17].Text);
        string id7Hasta = Convert.ToString(grdCajUpd_Row.Cells[18].Text);
        string id8Desde = Convert.ToString(grdCajUpd_Row.Cells[19].Text);
        string id8Hasta = Convert.ToString(grdCajUpd_Row.Cells[20].Text);
        string id9Desde = Convert.ToString(grdCajUpd_Row.Cells[21].Text);
        string id9Hasta = Convert.ToString(grdCajUpd_Row.Cells[22].Text);
        string id10Desde = Convert.ToString(grdCajUpd_Row.Cells[23].Text);
        string id10Hasta = Convert.ToString(grdCajUpd_Row.Cells[24].Text);
        string estado = Convert.ToString(grdCajUpd_Row.Cells[25].Text);
        DateTime fecEmiLot = Convert.ToDateTime(grdCajUpd_Row.Cells[26].Text);
        DateTime fecCadLot = Convert.ToDateTime(grdCajUpd_Row.Cells[27].Text);

        loteBL.Lotes_Crear_Consolidado(cliCod, cajCod, cajNum, cajConNum, cajConCod,
            id1Desde, id1Hasta, id2Desde, id2Hasta, id3Desde, id3Hasta, id4Desde, id4Hasta,
            id5Desde, id5Hasta, id6Desde, id6Hasta, id7Desde, id7Hasta, id8Desde, id8Hasta,
            id9Desde, id9Hasta, id10Desde, id10Hasta, altaPor, estado, fecEmiLot, fecCadLot);

    }
    Page.ClientScript.RegisterStartupScript(this.GetType(), "AlertScript", "alert('Lote(s) Creado(s)!');", true);
}

I clear command parameters with cmd.Parameters.Clear().

In SQL Server, I use the same name in SQL parameters for other stored procedures. I don't know what the problem could be.

Please, if anyone can help me, I wold be grateful

Best regards

Answer 1

Thanks guys for helping.

It was my mistake, I'm handling almost 30 parameters, one of them was duplicate.

Answer 2

If I recall correctly, you need to edit the post in order to delete it. To avoid this sort of issue, I would recommend creating a helper function like this:

private void AddParameter(DbProviderFactory dpf, List<DbParameter> params, DbType type, object value, string name)
{
    DbParameter param = dpf.CreateParameter();
    param.DbType = type;
    param.Value = value;
    param.ParameterName = name;
    params.Add(param);
}

Then call it like this:

 AddParameter(dpf, paramLotesConsol, DbType.String, cliCodigo, "CLIENTE_CODIGO");
 AddParameter(dpf, paramLotesConsol, DbType.String, cajCodigo, "CAJA_CODIGO");
// etc.

Answer 3

You can only use a SqlParameter once. Since you're passing SqlParameters into the method, I assume they are also used somewhere else?

If you wish to keep your call signature and want a quick fix, try this. Instead of

foreach (DbParameter param in parametros)
    cmd.Parameters.Add(param);

Try

foreach (ICloneable param in parametros)
    cmd.Parameters.Add(param.Clone() as SqlParameter);