Cannot insert duplicate key

问题:

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();

回答1:

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.

回答2:

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.

回答3:

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

回答4:

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 :)

回答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")
                }
            }

回答6:

I am a newbie at this but I am going to give this a try, sorry if it doesn't work for you.

I think that instead of using,

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

You should use the following (if you are updating the database):

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

And if the above code doesn't work, try this (if you are adding to the database):

// First, search for the particular obj you want to insert
var checkModels = member.Groups.Find(new Models.Groups(roles));

// If the obj doesn't already exist, add it to the database
if(checkModels == null){
        member.Groups.Add(new Models.Group(role));
}
// If the obj does exist already, then update it
else{
        member.Groups.Entry(new Models.Group(role)).State = EntityState.Modified;
}