Unable to insert records in SQLite database using Entity Framework 5

Question

I was trying to use SQLite database using ADO.NET Entity Framework 5 and I was able to connect to the SQLite database and was able to query records from a table in database. But When I am trying to insert a record into table the SaveChanges throws an exception

"An error occurred while updating the entries. See the inner exception for details."

Code looks like this:

var connectionStr = string.Format("data source={0};Version=3;UseUTF16Encoding=True;",
    fileName);
var conn = new SQLiteConnection(connectionStr);

this.personDataContext = new PersonDataContext(conn);

var persons = (from person in File.ReadAllLines(fileName)
               let p = person.Split(new[] { ',', ' ' },
                                    StringSplitOptions.RemoveEmptyEntries)
               select new Person
               {
                   ID = Convert.ToInt32(p[0]),
                   Name = p[1],
                   Address = p[2],
                   MobNo = Convert.ToInt32(p[3]),
                   PhNo = Convert.ToInt32(p[4]),
                   Designation = p[5]
               }).ToList();

if (this.personDataContext != null && this.personDataContext.Persons != null)
{
    foreach (var person in persons)
    {
      this.personDataContext.Persons.Add(person);
    }

    var saveFailed = false;

    do
    {
        try
        {
            this.personDataContext.SaveChanges();
        }
        catch (DbUpdateConcurrencyException ex)
        {
            saveFailed = true;
            ex.Entries.Single().Reload();
        }
    }
    while (saveFailed);
}

and when I check the inner exception, then it says that primary key is not defined. So, after defining the primary key into the table, same exception comes every time.

I am attaching the sample application here. Use Open Db to open the sample database first and then try to import the CSV file.

I am using the Code First approach in which I have designed a Model class and connected it with the table in the SQLite database.

Please help in fixing the problem. Thanks!

Can you please update your question with some more details? How is your create code looks like, what is the error exact error message? It's nice to have a repro solution but only a very few of the users will donwload it. And please think about the the later users: how somebody else will find your question when they have a similar problem. — nemesv
– nemesv, Commented Jul 18, 2012 at 10:51
Can you post the exact text of the inner exception message? — Slauma
– Slauma, Commented Jul 18, 2012 at 12:48
Inner exception is :- {"Abort due to constraint violation\r\nPerson.ID may not be NULL"}. I don't understand why this code acts weird with SQLite where as the code code works OK with SQL Server. — Pankaj
– Pankaj, Commented Jul 18, 2012 at 13:29

Slauma · Accepted Answer · 2012-07-18 14:08:10Z

2

Try to disable database generated identity for your key property in the Person class:

[DatabaseGenerated(DatabaseGeneratedOption.None)]
public int ID { get; set; }

To me it looks like you want to supply key values manually (ID = Convert.ToInt32(p[0])) but EF won't send this value to the database because by default it expects that the ID value is generated in the database. If it isn't generated in the DB you get the exception. By setting the DatabaseGeneratedOption to None EF will take the value you supply and send it in the INSERT statement to the database.

answered Jul 18, 2012 at 14:08

Slauma

177k60 gold badges407 silver badges424 bronze badges

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

Collectives™ on Stack Overflow

Unable to insert records in SQLite database using Entity Framework 5

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related