February 22, 2005 at 1:56 pm
I am new to SQL and to asp.net.
I have three tables:
1. Users
>userId
>Password
2. UsersRoles
>userId (joined with Users)
>RoleID
3. Roles
>RoleID (joined with UsersRoles)
>RoleName
I have created a new user page in asp.net. The form inserts all the information in the form into the Users table. Should it also auto populate the new user into the UserRoles table since they are joined? Should I expect the userId to be added to the UsersRoles table?
If not what is the best way to accomplish this? I can't add it from the new user form because the UserId will not exist until the record has been created.
Any help or suggestions would be greatly appreciated.
February 22, 2005 at 2:28 pm
Curtis, this will depend on lots of things. First of all, do you have FK relationships and constraints on the SQL tables?
I would suggest doing a search on google for "Updateable Views". This information might be helpful for what you are doing.
You could keep it simple... I'm not familiar with asp.net, but I'm assuming there are either variables or .net objects storing your values.
Therefore, In a subroutine that has access to your form level variables:
Update Users
Set userId = @user-id, Password = @Password
Update UsersRoles
Set userId = @user-id, RoleID = @RoleID
Update Roles
Set RoleID = @RoleID, RoleName = @RoleName
February 24, 2005 at 8:02 am
Is it safe to assume each user can have multiple roles, hence the UsersRoles table? If not then your table structure is too complex. If each user only has one role then you can put RoleID in the Users table and join directly to the Roles table.
If each User can have multiple roles then I assume you have a Multiple Select listbox control on your form to choose the roles? When you update the Users table you also have to update the UsersRoles table. For an Add it's easy: Insert the user then Insert an entry to UsersRoles for each Roles selected. For an Update it's actually easier to Update the User record, then Delete all UsersRoles where Userid=[userid from form] and then Insert a record into UsersRoles for each Roles selected in the Multiple Select control.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply