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.
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.
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.