0

I get this error:

SqlException: Cannot insert duplicate key row in object 'dbo.AspNetRoles' with unique index 'RoleNameIndex'. The duplicate key value is (Teacher). The statement has been terminated.

I am trying to add multiple roles for single users and in my database table AspNetRoles, I use unique RoleNameIndex key. Below is a screenshot of the AspNetRoles table:

AspNetRoles Table Design

Now, each user in my AspNetUsers table is assigned a role. I want to add another role to a user, but keep getting that error. What should I do? Help me with the code please.

My code uses input textbox for User ID and combobox for loading the Role Names. My code,on button click, is as follows:

var newRole = RolecomboBox.Text;
var idnum = Int32.Parse(IdTxtArea.Text);
var item = _db.AspNetUserRoles.FirstOrDefault(w => w.UserId == idnum);

item.AspNetRole.Name = newRole;
Improve this question
1

1 Answer 1

Reset to default
2

The information you need for the solution to your question is all in the SqlException that you were given.

SqlException: Cannot insert duplicate key row in object 'dbo.AspNetRoles' with unique index 'RoleNameIndex'. The duplicate key value is (Teacher). The statement has been terminated.

I bolded the 3 pieces of information you need.

  1. dbo.AspNetRoles is the table where you're attempting to insert a duplicate key row.
  2. RoleNameIndex is the name of the unique index you have on the table that you're violating with your insert.
  3. Teacher is the value of your unique key.

In order to insert additional rows into your dbo.AspNetRoles table for "Teacher", you need to delete the unique index RoleNameIndex, as this is REQUIRING the Name column to be unique.

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

3 Comments

I didn't design the database in question so i don't know if i can delete the unique index RoleNameIndex . So is it not possible to assign multiple roles to a user without changing that?
It's not possible to insert multiple rows into that table that have the same [Name] value while RoleNameIndex exists. After looking at your table and your question more in depth, are you sure this is the table you're needing to insert another row into? You say you're wanting to add multiple roles for a single user, but I don't see how you're going to do that by using this table. I don't know your Database Architecture, but I would imagine there to be some xref table lying around that links UserIds to these RoleIds.
Thanks for the help! it turns out i was trying to add into the wrong table after all. Yes there is a table connecting UserId and RoleID, and i had to use that to add new role

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.