IDENTITY_INSERT is set to OFF - Visual Studio

Question

I'm trying to insert values into a table in a local database. I'm using the EntityFramework for this. My code is pretty simple and straight forward, however I'm getting this error, when I try to insert data into the table.

Cannot insert explicit value for identity column in table 'PUserDataTable' when IDENTITY_INSERT is set to OFF.

Update: I'm getting this error with the below code now.

Validation failed for one or more entities. See 'EntityValidationErrors' property for more details.

Here's my code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace SampleDatabase.Controllers
{
    public class PUserDataTableController : Controller
    {
        PUserDataTableContext db = new PUserDataTableContext();

        public ActionResult Index()
        {
            return View(db.PUserDataTables.ToList());
        }

        [HttpGet]
        public ActionResult Create()
        {
            return View();
        }
        [HttpPost]

        public ActionResult Create(PUserDataTable userdata)
        {
           if(ModelState.IsValid)
           {
            db.PUserDataTables.Add(userdata);
            try
            {
                db.SaveChanges();
                return RedirectToAction("Index");
            }

            catch (DbEntityValidationException e)
            {
                foreach (var eve in e.EntityValidationErrors)
                {
                    Console.WriteLine("Entity of type \"{0}\" in state \"{1}\" has the following validation errors:",
                        eve.Entry.Entity.GetType().Name, eve.Entry.State);
                    foreach (var ve in eve.ValidationErrors)
                    {
                        Console.WriteLine("- Property: \"{0}\", Error: \"{1}\"",
                            ve.PropertyName, ve.ErrorMessage);
                    }
                }
                throw;
            }

           }
            return View(userdata);
        }
    }
}

Here is my Model:

namespace SampleDatabase
{
    using System;
    using System.Collections.Generic;
    using System.ComponentModel.DataAnnotations.Schema;

    public partial class PUserDataTable
    {
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public Nullable<int> Id { get; set; }
        public string DeviceID { get; set; }
        public string TimeSpent { get; set; }
        public Nullable<int> NumPages { get; set; }
        public string History { get; set; }
        public string FinalPage { get; set; }

    }
}

Here is how I create my table.

CREATE TABLE [dbo].[PUserDataTable] (
    [Id]        INT           IDENTITY (1, 1) NOT NULL,
    [DeviceID]  VARCHAR (50)  NULL,
    [TimeSpent] VARCHAR (50)  NULL,
    [NumPages]  INT           NULL,
    [History]   VARCHAR (MAX) NULL,
    [FinalPage] VARCHAR (50)  NULL,
    CONSTRAINT [PK_PUserDataTable] PRIMARY KEY CLUSTERED ([Id] ASC)
);

How do I resolve this error? Any help will be appreciated.

The default for IDENTITY_INSERT is off, and for a good reason. Identity columns are intended to be populated by the database, not the caller. EF should handle this just fine, unless there's an issue with the MySQL EF provider. Can you post the code for the EF data model for that table? — Tim, Dec 18 '14 at 07:35
That's exactly my problem. I'm not force adding any data into the 'Id' field , and it's still complaining. — user2771150, Dec 18 '14 at 07:36
Have you stepped into the code with the debugger to see what `userdata` contains when it hits the controller? — Tim, Dec 18 '14 at 07:37
The 'userdata' object also contains an 'Id' field set to 0, trying to insert into the table. That's weird because I'm not adding anything to the 'Id' field. Is there a way to tell the program to ignore the 'Id' field, since it should be automatically incremented with every insert method call. — user2771150, Dec 18 '14 at 07:55
Default value for an `int` is 0, so even if you don't explicitly assign it a value, it'll still have one (0). — Tim, Dec 18 '14 at 07:57

score 6 · Accepted Answer · answered Dec 18 '14 at 11:33

6

Since you are using Entity Framework you must be using a data model. Once you drag and drop table into model just Right Click on the Id filed on model and from the properties set StoreGeneratedPattern into identity and save. Check again after save and it might fix your problem. But if you are using code first approach this method not valid.

enter image description here

answered Dec 18 '14 at 11:33

Damith

1,982
3
28
42

thanx dam helped me also – sibonile Feb 04 '15 at 08:23
this answer helped me – Tornike Shavishvili Sep 06 '16 at 09:03

StuartLC · Answer 2 · 2014-12-18T07:52:05.243

4

That looks like Sql Server, not MySql (MySql has AUTO_GENERATED columns, Sql Server has Identity).

Either way, your Entity PK property PUserDataTable.ID should be marked with :

public class PUserDataTable
{
   [DatabaseGenerated(DatabaseGenerationOption.Identity)]
   public int ID {get; set;}

, or if you are using the fluent api, use

.HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

This will stop EF trying to insert the column.

edited Dec 18 '14 at 07:52

answered Dec 18 '14 at 07:38

StuartLC

104,537
17
209
285

I added [DatabaseGenerated(DatabaseGenerationOption.Identity)] just after the 'Id' field in my Model. However, I'm still getting the same error. – user2771150 Dec 18 '14 at 07:50
I updated my code, cleaned the project and tried again, still getting the same error. I tried debugging the program and my 'userdata' object also contains an 'Id' field that is set to 0, when it's trying to call the 'SaveChanges()' method. – user2771150 Dec 18 '14 at 08:00
If you are using Database or Model first, also check to see that you haven't switched [`StoreGeneratedPattern`](http://stackoverflow.com/a/4669721/314291) in the EDMX designer? – StuartLC Dec 18 '14 at 08:24
changing the Id definition to Nullable fixed the problem, but now I'm getting the "Validation failed for one or more entities. See 'EntityValidationErrors' property for more details." error. I've updated my question and included new code that can cause this error. – user2771150 Dec 18 '14 at 08:31

score 1 · Answer 3 · answered Dec 18 '14 at 07:40

1

Assuming your PUserDataTable class has an internal structure like this:

public class PUserDataTable {
   public int Id {get;set;}
   //other properties
}

you should add this attribute [DatabaseGenerated(DatabaseGeneratedOption.Identity)] and that should solve the problem.

This attribute warns EF that the database is already generating a value for this property/column and no need to worry about that, otherwise EF seems to generate a value for the fields with primitive types. In int's case it's 0.

answered Dec 18 '14 at 07:40

Mikayil Abdullayev

12,117
26
122
206

I added the attribute and still getting the same error. – user2771150 Dec 18 '14 at 08:02
1

Make your property to be be int?(or Nullable), rather than just int. I'm sure this will solve the problem. – Mikayil Abdullayev Dec 18 '14 at 08:05
I agree that EF has a lot of things to consider revising. – Mikayil Abdullayev Dec 18 '14 at 08:06
That fixed the problem, but now I'm getting the error "Validation failed for one or more entities. See 'EntityValidationErrors' property for more details.". I've updated my question and added more code that this error may relate to. – user2771150 Dec 18 '14 at 08:23
Do you have any validation rules set up? – Mikayil Abdullayev Dec 18 '14 at 08:38
Not that I'm aware of. The above code is all of my code, excluding the View. – user2771150 Dec 18 '14 at 08:40
I doubt this has anything to do with this particular property. I think, now that the previous problem got away, another problem on the queue is shown. For this I would recommend to check this thread http://stackoverflow.com/questions/7795300/validation-failed-for-one-or-more-entities-see-entityvalidationerrors-propert – Mikayil Abdullayev Dec 18 '14 at 10:16

score 0 · Answer 4 · answered Aug 21 '22 at 17:57

In my case: My program uploads many files. Each file was processed and noted in a table on a database. So I used a loop for each file. My mistake was that I didn't reinstanciate the table inside the loop. After moving var table = new Table(); inside the loop the failure disappeared.

IDENTITY_INSERT is set to OFF - Visual Studio

4 Answers4

Linked