CODEWITHSUNDEEP
c#entity-frameworkasp.net-mvc-4simplemembership
NeoDeveloper
NeoDeveloper

Reputation: 61

Error using AddUserToRole of SimpleMembership: INSERT statement conflicted with the FOREIGN KEY constraint

I'm using asp.net mvc4, c# and Entity Framework 5. I am trying to create a user and assign role to the user at the same time using simple membership (database first approach), but getting the following error at Roles.AddUserToRole("nuser", "Admin");

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_webpages_UsersInRoles_webpages_Roles". The conflict occurred in database "DbClick2Eat", table "dbo.webpages_Roles", column 'RoleId'. The statement has been terminated.

Here is my code:-

[HttpPost]
        [ValidateAntiForgeryToken]
        public ActionResult CreateUserByAdmin(RegisterModel register, string role)
        {
            try
            {
                if (Roles.RoleExists(role))
                {
                    var model = register ?? new RegisterModel();
                    WebSecurity.CreateUserAndAccount(model.UserName, model.Password);
                    if (WebSecurity.UserExists(model.UserName))
                        Roles.AddUserToRole("admin1", "Admin");
                }
                else
                   ModelState.AddModelError("NoRole", "Please select a role.");

                var roles = Roles.GetAllRoles().ToList();
                ViewBag.DeliveryArea = new SelectList(roles.Select(x => new { Value = x, Text = x }), "Value", "Text");
                return View();
            }
            catch (MembershipCreateUserException e)
            {
                ModelState.AddModelError("", AccountController.ErrorCodeToString(e.StatusCode));
            }
            return View();
        }

DB Schema

CREATE TABLE [dbo].[webpages_Roles](
    [RoleId] [int] IDENTITY(1,1) NOT NULL,
    [RoleName] [nvarchar](256) NOT NULL,
 CONSTRAINT [PK_webpages_Roles] PRIMARY KEY CLUSTERED 
(
    [RoleId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[webpages_UsersInRoles](
    [RoleId] [int] NOT NULL,
    [UserId] [int] NOT NULL,
 CONSTRAINT [PK_webpages_UsersInRoles] PRIMARY KEY NONCLUSTERED 
(
    [RoleId] ASC,
    [UserId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[webpages_UsersInRoles]  WITH CHECK ADD  CONSTRAINT [FK_webpages_UsersInRoles_UserProfile] FOREIGN KEY([UserId])
REFERENCES [dbo].[UserProfile] ([UserId])
GO
ALTER TABLE [dbo].[webpages_UsersInRoles] CHECK CONSTRAINT [FK_webpages_UsersInRoles_UserProfile]
GO
ALTER TABLE [dbo].[webpages_UsersInRoles]  WITH CHECK ADD  CONSTRAINT [FK_webpages_UsersInRoles_webpages_Roles] FOREIGN KEY([RoleId])
REFERENCES [dbo].[webpages_Roles] ([RoleId])
GO
ALTER TABLE [dbo].[webpages_UsersInRoles] CHECK CONSTRAINT [FK_webpages_UsersInRoles_webpages_Roles]

-> "Admin" is a role name

RoleId =1, RoleName = Admin

"Admin" Role exists in table.I can't fix it... Need Help

Upvotes: 0

Views: 998

Answers (3)

Kevin Junghans
Kevin Junghans

Reputation: 17540

If you are using SimpleMembershp then you are using the WebMatrix.WebData.SimpleRoleProvider. If you look at the definition for this class there is no AddUserToRole method defined and I am not sure what kind of behavior you will get by using it. The reason this method is available to you is because you are using System.Web.Security.Roles without casting it to the actual provider used, which is SimpleRoleProvider. Ahhh, the frustrations of the provider model. Here is the safe way to do this when using SimpleMembership.

var roles = (WebMatrix.WebData.SimpleRoleProvider)Roles.Provider;
if (!roles.GetRolesForUser(model.UserName).Contains("Admin"))
{
    roles.AddUsersToRoles(new[] { model.UserName }, new[] { "Admin" });
}

In your code you are just checking whether the user exists before you map them to a role. What you really want to check is if the mapping is already there before adding a user to a role, as shown in the code snippet above. This will eliminate any foreign key violations.

Upvotes: 0

rustam
rustam

Reputation: 154

Swap order of UserId and RoleId in webpages_UsersInRoles table. UserId shall be the first column.

Upvotes: 0

woutervs
woutervs

Reputation: 1510

At the line:

if (WebSecurity.UserExists(model.UserName)) roles.AddUserToRole("admin1", "Admin")

Did you set "admin1" hardcoded on purpous? Because if you did this might be the reason you are getting foreign key errors as this combination might already be in the database.

So changing this to:

if (WebSecurity.UserExists(model.UserName)) roles.AddUserToRole(model.Username, "Admin")

Should resolve the problem.

Next to that from the context CreateUserByAdmin i'm not really sure what you are trying to do. Are you trying to add a user to the DB by an administrator, or a user that has to become an administrator?

Update

Can you change your code like this? try {

            var model = register ?? new RegisterModel();
            if (!WebSecurity.UserExists(model.UserName))
            {
                WebSecurity.CreateUserAndAccount(model.UserName, model.Password);
            }
            if (Roles.RoleExists(role))
            {
                Roles.AddUserToRole(model.UserName, role);
            }
            else ModelState.AddModelError("NoRole", "Please select a role.");

            var roles = Roles.GetAllRoles().ToList();
            ViewBag.DeliveryArea = new SelectList(roles.Select(x => new { Value = x, Text = x }), "Value", "Text");
            return View();
        }
        catch (MembershipCreateUserException e)
        {
            ModelState.AddModelError("", AccountController.ErrorCodeToString(e.StatusCode));
        }
        return View();

This way we at least exclude fk errors on users already existing. If this fails to work can you please include the entire Exception stack?

Upvotes: 1

Related Questions