Violation of PRIMARY KEY constraint . Cannot insert duplicate key

Question

I am quite new to ASP.Net MVC. I have just started learning it so kindly help me as I am stuck at a place. Early response will be highly appreciated. I am making a registration page. Added a SQL database using App_data .Below is the table definition :

CREATE TABLE [dbo].[RegisterModel]
(
    [Id] INT NOT NULL primary key , 
    [UserName] VARCHAR(50) NOT NULL, 
    [Email] VARCHAR(50) NOT NULL, 
    [Password] VARCHAR(50) NOT NULL, 
    [ConfirmPassword ] VARCHAR(50) NOT NULL,

)

Then added ADO.Net Entity data model for the same. then I created a controller Account with an action method Index as below:

public class AccountController : Controller
    {
        //
        // GET: /Account/
    public ActionResult Index()
    {
        //using (dbRegisterModelEntities dc = new dbRegisterModelEntities())
        //{
            return View();

        }

    [HttpPost]
    public ActionResult Index(RegisterModel user)
    {
        if (ModelState.IsValid)
        {
            using (dbRegisterModelEntities dc = new dbRegisterModelEntities())
            {
                dc.RegisterModels.Add(user);
                dc.SaveChanges();
                return RedirectToAction("Index", "Account");

            }
        }
          return View(user);

      }

After creating one user , now I am getting error :

Violation of PRIMARY KEY constraint 'PK__tmp_ms_x__3214EC0778D493BE'. Cannot insert duplicate key in object 'dbo.RegisterModel'. The duplicate key value is (0). The statement has been terminated.

This error is coming when I am trying to add a second user.

You must either set value of Id before inserting to Db or set auto increment to 1 for Id field in db — Sid M
– Sid M, Commented Oct 5, 2015 at 13:55
Side note - you should never need to store a password confirmation. Also, you should be salting and hashing passwords, not storing them directly. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Oct 5, 2015 at 14:31

Simone · Accepted Answer · 2015-10-05 13:56:18Z

2

You should declare the id as an identity or set a different value for each user

CREATE TABLE [dbo].[RegisterModel]
(
    [Id] INT NOT NULL primary key  IDENTITY(1,1) , 
    [UserName] VARCHAR(50) NOT NULL, 
    [Email] VARCHAR(50) NOT NULL, 
    [Password] VARCHAR(50) NOT NULL, 
    [ConfirmPassword ] VARCHAR(50) NOT NULL,
)

edited Oct 5, 2015 at 13:56

answered Oct 5, 2015 at 13:55

Simone

1,9441 gold badge14 silver badges22 bronze badges

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

5 Comments

Monika Over a year ago

Thanks Simone! But I already tried the above thing. If I try this , I am getting another error : Cannot insert explicit value for identity column in table 'RegisterModel' when IDENTITY_INSERT is set to OFF.

Simone Over a year ago

Then you should put in your adding procedure the following command: "SET IDENTITY_INSERT RegisterModel ON".The alternative is the one I mentioned in the answer: keep the table unchanged and in your procedure set the id as a unique number (SELECT MAX(id)+1 FROM RegisterModel)

Monika Over a year ago

Thanks ! How I can change the procedure , I didn't create any .If I try running this "SET IDENTITY_INSERT RegisterModel ON" in my db , it says statement is not recognized. Please help !!

Simone Over a year ago

When you call dc.RegisterModels.Add(user); in some place the system is diong things on SQL, that would be the place, just trrack it down. In any case at this link you can get some more info regarding the identity_insert features: msdn.microsoft.com/it-it/library/ms188059%28v=sql.120%29.aspx

Monika Over a year ago

I deleted my edmx file and again created a ado.net entity model and the issue is resolved.Thanks Simone !

Mark Sinkinson · Accepted Answer · 2015-10-05 13:56:14Z

0

Your Primary Key should probably be an identity column, incrementing by 1 each time.

CREATE TABLE [dbo].[RegisterModel]
(
    [Id] INT NOT NULL IDENTITY(1,1) , 
    [UserName] VARCHAR(50) NOT NULL, 
    [Email] VARCHAR(50) NOT NULL, 
    [Password] VARCHAR(50) NOT NULL, 
    [ConfirmPassword ] VARCHAR(50) NOT NULL,
    CONSTRAINT PK_RegisterModel PRIMARY KEY
    (
       Id
    ) 
);

answered Oct 5, 2015 at 13:56

Mark Sinkinson

9767 silver badges18 bronze badges

1 Comment

Monika Over a year ago

Thanks Mark! But I already tried the above thing. If I try this , I am getting another error : Cannot insert explicit value for identity column in table 'RegisterModel' when IDENTITY_INSERT is set to OFF

Paolo · Accepted Answer · 2015-10-05 14:29:48Z

0

simply remove the ID column both from table and model:

CREATE TABLE [dbo].[RegisterModel]
(
    [UserName] VARCHAR(50) NOT NULL PRIMARY KEY, 
    [Email] VARCHAR(50) NOT NULL, 
    [Password] VARCHAR(50) NOT NULL, 
    [ConfirmPassword ] VARCHAR(50) NOT NULL
);

the username must be unique so you already have a key field.

answered Oct 5, 2015 at 14:29

Paolo

2,2731 gold badge17 silver badges20 bronze badges

1 Comment

Monika Over a year ago

Yes I can do this for particular scenario as I can use username as my key field but I have to apply same thing in other scenarios and I am getting same problem.

Collectives™ on Stack Overflow

Violation of PRIMARY KEY constraint . Cannot insert duplicate key

3 Answers 3

5 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related