Data insertion loop does not work

Question

Related to this question : I'm trying to write an "INSERT" loop : I have got the following query:

CommandText = "INSERT into sample2Prot(timestp,idq,idz,prot,Lhowmany,Rhowmany) VALUES(@timestp,@idq,@idz,@prot,@Lhowmany,@Rhowmany)";

When I execute my code (which can be found just below) I get the following error:

'@timestp' cannot be handle by SqlParameterCollection. ("timestp" = tableNames[0], of string type)

for (int j = 0; j < tableNames.Count; j++)

// tableNames contains the name of the columns, tableTypes the types of the columns
// tableTypes contains
{
     if (tableTypes[j] == "INTEGER")
     {
           myCommand3.Parameters.Add("@" + tableNames[j], System.Data.SqlDbType.Int);
           Console.WriteLine("@" + tableNames[j]);
     }
     else
     {
           myCommand3.Parameters.Add("@" + tableNames[j], System.Data.SqlDbType.VarChar);
           Console.WriteLine("@" + tableNames[j]);
     }

}

Console.WriteLine(myCommand3.CommandText);
for (int f = 0; f < total.Count(); f++)
{
     for (int k = 0; k < tableNames.Count; k++)
     {
           myCommand3.Parameters.Clear();
           myCommand3.Parameters["@" + tableNames[k]].Value = total[f][k];
     }

     myCommand3.ExecuteNonQuery();
}

Has someone an idea ? Don't mind asking for more precision.

That doesn't look like a C# error message. Can you copy and paste the EXACT and ENTIRE error message? Also what is the datatype of the `timestp` column in the database? — Tab Alleman, Aug 10 '17 at 13:56
Which type is your myCommand3? SqlCommand? I guess you might want to try something like this: CommandText = "INSERT into sample2Prot(timestp,idq,idz,prot,Lhowmany,Rhowmany) VALUES(?,?,?,?,?,?)"; — Tyron78, Aug 10 '17 at 13:59
Well I personally would check the value of all those parameters at the time of execution. Then insert them into your sql statement and try to run that directly on the server. I would also run Profiler and see what if anything is being sent to the database. It may be that there is a null value causing a problem. — HLGEM, Aug 10 '17 at 14:01
Erm, you _clear_ the Params in the same loop where you set their values? Is that right? — Fildor, Aug 10 '17 at 14:02
@Tyron78 I hope german doesn't bother you ;P "Ein Ausnahmefehler des Typs "System.IndexOutOfRangeException" ist in System.Data.dll aufgetreten. Zusätzliche Informationen: SqlParameter mit ParameterName '@timestp' ist nicht in SqlParameterCollection enthalten." — Doe Jowns, Aug 10 '17 at 14:03
There you go ... `myCommand3.Parameters.Clear();` is your problem. — Fildor, Aug 10 '17 at 14:03
@Fildor That's right.. I did it because this was advised in [this question](https://stackoverflow.com/questions/8747066/executenonquery-inside-loop) :) (but I maybe understood it wrong. Can you provide more details x) ?) — Doe Jowns, Aug 10 '17 at 14:04
In the question you linked, he did not clear them at all. You on the contrary are clearing too often. Try moving the clear statement after the `ExecuteNonQuery()`. — Fildor, Aug 10 '17 at 14:05
Oh ok !!! I'm stupid ! I got the point, `myCommand3.Parameters.Clear()` as I used it clears everything ! — Doe Jowns, Aug 10 '17 at 14:06
Correct, so you were trying to set a value on a non existing param, because just before you cleared all params. But I have to correct myself again: move it outside both for-loops. — Fildor, Aug 10 '17 at 14:08

score 1 · Accepted Answer · answered Aug 10 '17 at 14:25

for (int f = 0; f < total.Count(); f++)
{
     for (int k = 0; k < tableNames.Count; k++)
     {
           myCommand3.Parameters.Clear(); // < ==== PROBLEM is here!
           // After clearing the Parameters, there is no Parameter "["@" + tableNames[k]]"
           // hence "SqlParameter mit ParameterName '@timestp' ist nicht in SqlParameterCollection enthalten."
           // (eng: SqlParameterCollection does not contain SqlParameter with name '@timestp' )
           myCommand3.Parameters["@" + tableNames[k]].Value = total[f][k];
     }

     myCommand3.ExecuteNonQuery();
}

In above code, move the Clear() statement like so:

for (int f = 0; f < total.Count(); f++)
{
     for (int k = 0; k < tableNames.Count; k++)
     {
           myCommand3.Parameters["@" + tableNames[k]].Value = total[f][k];
     }
     myCommand3.ExecuteNonQuery();
}
myCommand3.Parameters.Clear();

Data insertion loop does not work

1 Answers1