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
– apomene, Commented Jan 31, 2017 at 14:44

kgzdev · Accepted Answer · 2017-01-31 14:45:20Z

4

Add this

cmd.Parameters.Clear();

before

cmd.Parameters.AddRange(new OleDbParameter[]

answered Jan 31, 2017 at 14:45

kgzdev

2,9312 gold badges21 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

gbianchi · Accepted Answer · 2017-01-31 14:47:21Z

1

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();

answered Jan 31, 2017 at 14:47

gbianchi

2,14827 silver badges37 bronze badges

Collectives™ on Stack Overflow

c# Oledb add list of parameters in a foreach loop

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related