Recently I have faced a problem when insert multiple row insert at single call. After first row insert then problem arise from second row insert. After goggling I have found a solution : it need to clear parameter after each row insert. I want to share that example with you:
Code :C#
// Arranging Data in an Array.
const int no_of_values = 2;
int[] val1 = new int[no_of_values];
int[] val2 = new int[no_of_values];
val1[0] = val1;
val2[0] = val2;
val1[1] = val11;
val2[1] = val22;
// Do the inserts using Parameterized queries.
Connection.Open();
SqlCeCommand command = Connection.CreateCommand();
command.CommandText = "Insert INTO [Table] (col1, col2) Values (...val1, ...val2)";
for (int i = 0; i < no_of_values; i++)
{
command.Parameters.Clear(); //it needs to clear parameter after each row inserted.
command.Parameters.Add("...val1", val1[ i ]);
command.Parameters.Add("...val2", val2[ i ]);
command.ExecuteNonQuery();
}
Connection.Close();
I have fixed this problem for MS SQL Server, it is also same for oracle too.
Hope that it may helpful for developers.