Cannot insert duplicate key.

Question

I am getting this error ...

Violation of PRIMARY KEY constraint 'PK_Members'. Cannot insert duplicate key in object 'dbo.Members'.
The statement has been terminated.

When I try to use the Membership and Role Providers in ASP.NET MVC. It happens when calling the GetUser method from inside the RoleProvider.

        var member = System.Web.Security.Membership.GetUser(email) as Models.Member;
        //var member = (
        //    from m in DataContext.Members
        //    where m.Email == email
        //    select m).Single();

        var role = (
            from r in DataContext.Roles
            where r.Name == roleName
            select r).Single();

        member.Groups.Add(new Models.Group(role)); 

        DataContext.SubmitChanges();

Answer 1

I suspect it's because you are adding a group that exists already.

Maybe you should check for the existance of the role before trying to add it.

Hope this helps.

Answer 2

A good way to debug this is to use SQL profiler to determine what SQL code is being run against the database.

I would suspect you are trying to save a record somewhere that has the same primary key already in the database.

SQL Profiler = http://msdn.microsoft.com/en-us/library/ms181091.aspx

Answer 3

Are you sure you are not trying to enter a number into the PRIMARY KEY field that is already there? If it is auto_increment, just enter 0 and it will make the value of that field, the last number+1

Hope this helps :)

Answer 4

It looks like the problem is in the code

member.Groups.Add(new Models.Group(role));

Based on the error message returned by the sql, Read operation like GetUser won't throw this type of error.

Answer 5

If the exception is an SqlException you might get its error number for duplicate records which is 2627. You might catch the exception and verify it and display and manage any error accordingly. I Hope this helps.

 catch (SqlException ex)
            {
                if (ex.Number == 2627)
                {
                    MessageBox.Show("This record exists: "+ex.Message, "Error");
                }
                else
                {
                    MessageBox.Show(ex.Message, "Error")
                }
            }