Send variables to SP with foreach

Question

Send variables to SP with foreach

Navigation

#1 by (1 votes)
#2 by (1 votes)

0

I have a method that reads from a table (X) 3 values and sends them to a StoreProceduces as many times as records have the table (x)

so what I do is the following:

foreach (var item in resultado)
            {
                cmd.Parameters.Add("@Id", SqlDbType.Int).Value = item.Id;

                cmd.Parameters.Add("@Valor", SqlDbType.Decimal).Value = item.Valor;
                cmd.Parameters.Add("@LastUpdate", SqlDbType.DateTime).Value = DateTime.Now;
            }
             cmd.ExecuteNonQuery();

The problem is that I get an error:

The sp_UPDATE function or procedure has too many arguments.

sql c#

asked by lanshare 14.08.2017 в 16:49

source

2 answers

1

I already solved it. I just had to add the following line to "Clean" the collection of parameters. ( cmd.Parameters.Clear(); ) staying like this, working perfect.

            foreach (var item in resultado)
            {
                cmd.Parameters.Clear();//Esto soliciono mi problema
                cmd.Parameters.Add("@Id", SqlDbType.Int).Value = item.Id;
                cmd.Parameters.Add("@Valor", SqlDbType.Decimal).Value = item.Valor;
                cmd.Parameters.Add("@LastUpdate", SqlDbType.DateTime).Value = DateTime.Now;

                cmd.ExecuteNonQuery();
            }

answered by 14.08.2017 в 17:41

problem with routes? Repeat Data in an Arrangement

score 1 · Accepted Answer

Try this way:

cmd.Parameters.Add("@Id", SqlDbType.Int)
cmd.Parameters.Add("@Valor", SqlDbType.Decimal)
cmd.Parameters.Add("@LastUpdate", SqlDbType.DateTime)

foreach (var item in resultado)
{
    cmd.Parameters["@Id"].Value = item.Id;
    cmd.Parameters["@Valor"].Value = item.Valor;
    cmd.Parameters["@LastUpdate"].Value = DateTime.Now;
    cmd.ExecuteNonQuery();            
}