Question 86152 in ASP.NET Question posted in
The official documentation can be found here.

Why do I get the Exception SqlException IDENTITY_INSERT is set to OFF – Asp.net

HeuteDb heute = new HeuteDb();
string filePath = @"C:UsersEliasDesktopASPlearningDATAData.csv";

using (StreamReader reader = new StreamReader(filePath))
{
    string line;

    while ((line = reader.ReadLine()) != null)
    {
        List<string> items = line.Split(';').ToList();

        heute.Uhrzeit = items[0];
        heute.Energie = items[1];

        _context.HeutesDb.Add(heute);
        _context.SaveChanges();
    }
}

I have a CSV file which I want to store into my database. There is an ID which is set as a primary key and the Uhrzeit and the Energie values.

After every line of the CSV file, I save the changes to the database. It only saves the first line in the database, then it throws an error.

What is the best way to save a CSV file into a database?

Tags:

2

Answers


  1. 0

    You might need to either set autoincrement of your Identity column in the database (value is increased by 1 automatically) – or you need to specify a value on your identity Column while inserting,

    the first entry is saved successfully because the int datatype has an default value of 0, since 0 does not exist as an entry it will allow to be saved (next record needs different id)

    Login or Signup to reply.
  2. 0

    Look at your loop

    while ((line = reader.ReadLine()) != null)
{
    List<string> items = line.Split(';').ToList();

    heute.Uhrzeit = items[0];
    heute.Energie = items[1];

    _context.HeutesDb.Add(heute);
    _context.SaveChanges();
}

    You add again and again the same instance heute to your database. But the instance is the same, so after the first successful insert, EF will automatically set the property ID, supposed to contain the IDENTITY value to the value obtained performing the first insert.
    At the next loop, the same instance (with the ID property still set from the previous loop) is passed again for adding, but, at this time, the engine rejects the insert because you are not supposed to set a value for ID unless you call SET IDENTITY_INSERT ‘tablename’ ON for your table.

    You fix it simply moving the initialization for the heute variable inside the loop.

    while ((line = reader.ReadLine()) != null)
{
    
    List<string> items = line.Split(';').ToList();

    HeuteDb heute = new HeuteDb();
    heute.Uhrzeit = items[0];
    heute.Energie = items[1];

    _context.HeutesDb.Add(heute);
    _context.SaveChanges();
}

    Now, at each loop, the heute instance and its ID property are new without any leftover from the previous loop and the db engine will insert your records.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search