c# Oledb add list of parameters in a foreach loop

Question

i write an application that creates an excel file with OleDb, the problem is that when the program runs a foreach loop it writes always the same data in the row, not the different value of the object in the loop.

The code is:

foreach(var user in userList)
{
   cmd.CommandText = @"INSERT INTO [User](Id , Name , Code, Username, Location, 
                  CompanyCode, MyDate, Email, MyNote)
                  VALUES(@Id, @Name, @Code, @Username, @Location)";


  cmd.Parameters.AddRange(new OleDbParameter[]
  {
    new OleDbParameter{ ParameterName = "@Id", Value = user.ID},
    new OleDbParameter{ ParameterName = "@Name", Value = user.NAME  ?? string.Empty},
    new OleDbParameter{ ParameterName = "@Code", Value = user.CODE ??  string.Empty},
    new OleDbParameter{ ParameterName = "@Username", Value = user.USERNAME ?? string.Empty},
    new OleDbParameter{ ParameterName = "@Location", Value = user.LOCATION ?? string.Empty},

  });

cmd.ExecuteNonQuery();
}

I have 12 user but the excel file has 12 rows with the same user. Example:

ExcelRow   ID NAME CODE USERNAME LOCATION
  1        1  Fra   15    Fra       NY
  2        1  FRA   15    Fra       NY
  3        1  FRA   15    Fra       NY
  4        1  FRA   15    Fra       NY
  5        .......

Instead if i write normal query like:

cmd.CommandText = @"INSERT INTO [User](Id , Name , Code, Username, Location, CompanyCode, MyDate, Email, MyNote) VALUES(" + company.ID + ", '" + company.NAME + "', '" + company.CODE + "', '" + company.USERNAME + "', '" + company.CODE_LOCATION + "')";

It works and write 12 different user in the file. How can i solve this problem?? Thank you and sorry for my bad english.

In your parameters example you use object variable user where as on your concatenate string query you use variable company. — apomene, Jan 31 '17 at 14:44

score 4 · Accepted Answer · answered Jan 31 '17 at 14:45

4

Add this

cmd.Parameters.Clear();

before

cmd.Parameters.AddRange(new OleDbParameter[]

answered Jan 31 '17 at 14:45

kgzdev

2,770
2
18
35

score 1 · Answer 2 · answered Jan 31 '17 at 14:47

You are adding a new range of parameter to an object that already has a set of parameters. The query is using only the first set, overriding the other ones you added after that.

You need to clear the parameters before each Loop.

Use something like:

cmd.Parameters.Clear();

c# Oledb add list of parameters in a foreach loop

2 Answers2