IDENTITY_INSERT is set to OFF - Visual Studio

asp.net-mvc c# database entity-framework visual-studio-2012

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.

1
2
12/18/2014 11:20:29 AM

Accepted Answer

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

6
12/18/2014 11:33:11 AM

Popular Answer

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.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow