CODEWITHSUNDEEP
c#.netasp.net-mvcvisual-studio
Matthew
Matthew

Reputation: 41

There is a "DbUpdateException was unhandled by user code" error when running code

When I run my DbInitializer there is an error displayed by the "context.SaveChanges", it says "An exception of type 'Microsoft.EntityFrameworkCore.DbUpdateException' occurred in Microsoft.EntityFrameworkCore.dll but was not handled in user code"

I have downloaded several different NuGet packages and still the error is still there. Any ideas ?

 using System;
 using System.Collections.Generic;
 using System.Linq;
 using Microsoft.EntityFrameworkCore;
 using Microsoft.Extensions.DependencyInjection;
 using FinalTeamProject.Data;

namespace FinalTeamProject.Models
{
public static class DbInitializer
{
    public static void Initialize(BookingContext context)
    {
        context.Database.EnsureCreated();

        // Look for any students.
        if (context.Customers.Any())
        {
            return;   // DB has been seeded
        }

        var customers = new Customer[]
        {
        new Customer{ID=201,FirstName="Joe",LastName="Gatto",Telephone="07580043213"},
        new Customer{ID=202,FirstName="Sal",LastName="Vulcano",Telephone="0758243454"},
        new Customer{ID=203,FirstName="James",LastName="Murray",Telephone="07580043290"},
        new Customer{ID=204,FirstName="Brian",LastName="Quinn",Telephone="075800432800"},
        new Customer{ID=205,FirstName="Joe",LastName="Gato",Telephone="0758004313"},
        new Customer{ID=206,FirstName="Sal",LastName="Vulcno",Telephone="075823454"},
        new Customer{ID=207,FirstName="James",LastName="Muray",Telephone="0750043290"},
        new Customer{ID=208,FirstName="Brian",LastName="Quin",Telephone="07500432800"}

        };
        foreach (Customer s in customers)
        {
            context.Customers.Add(s);
        }
        context.SaveChanges();

The Inner Exception:

{System.Data.SqlClient.SqlException: Cannot insert explicit value for identity column in table 'Customer' when IDENTITY_INSERT is set to OFF.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, String executeMethod, IReadOnlyDictionary`2 parameterValues, Boolean closeConnection)
at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteReader(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute(IRelationalConnection connection)
ClientConnectionId:a4073037-3f12-4a01-9019-ab632c3325a0
Error Number:544,State:1,Class:16}

Upvotes: 0

Views: 664

Answers (2)

Chetan
Chetan

Reputation: 6891

Customer.Id column marked as Identity column in the database. You can not set value for such column explicitly. You are setting value of Customer.ID property. That is why you are seeing that exception.

SQL server will assigned value to the IDENTITY column while inserting the record to the table.

So you have following solutions.

One - Remove IDENTITY constraint from the column in the database. This way your current code will work fine.

Two - You don't assigned values to the Customer.ID property

var customers = new Customer[]
{
    new Customer{FirstName="Joe",LastName="Gatto",Telephone="07580043213"},
    new Customer{FirstName="Sal",LastName="Vulcano",Telephone="0758243454"},
    new Customer{FirstName="James",LastName="Murray",Telephone="07580043290"}
};
foreach (Customer s in customers)
{
    context.Customers.Add(s);
}
context.SaveChanges();

Three - You assign the value to Customer.ID property but you need to set the indentity_insert ON on the table before inserting the rows with explicit IDs and then set if OFF after the insertion.

var customers = new Customer[]
{
    new Customer{ID=201,FirstName="Joe",LastName="Gatto",Telephone="07580043213"},
    new Customer{ID=202,FirstName="Sal",LastName="Vulcano",Telephone="0758243454"},
    new Customer{ID=203,FirstName="James",LastName="Murray",Telephone="07580043290"}
};

context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT Customer ON")

foreach (Customer s in customers)
{
    context.Customers.Add(s);
}

context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT Customer OFF")

the problem with 1st and 3rd approach is you need to keep track of the latest inserted ID for the customer so that you don't get primary key violation error when you try to insert the next customer.

For IDENTITY column, it's best to leave it to the SQL server to generate the values for it.

Upvotes: 2

Alex
Alex

Reputation: 23300

You have the explanation in your inner exception

{System.Data.SqlClient.SqlException: Cannot insert explicit value for identity column in table 'Customer' when IDENTITY_INSERT is set to OFF.

Either turn ON IDENTITY_INSERT in the database or remove the ID from the data you are inserting (the database will take care of generating it)

Upvotes: 0

Related Questions