insert SQL

[cb3431]

I am not sure if I am posting in the right area, but perhaps some one can answer my question.

I am very new to SQL and asp.net. I have built a registration page for new users which works to update the main table. I have a separate table to house my role information. I joined the main table and the role table using visual studio.net and made sure to check the cascade update and delete fields. When I add a new user it does not add the user ID from the main table to the role table. Is this supposed to happen automatically since the tables are joined or do I need to add the user ID to the role table myself?

If i have to add the user Id myself what is the best way to do this? The user ID will not be created until the user has sent the form back to the server, so I can't add the user ID to the role table at the same time can I?

Any help or suggestions would be greatly appreciated.

Thank you

[kashif_82]

What kind of database are you using?

[brouse]

Cascading usually only works for deleting records. If you want to add the ID to the roles table, you could create a stored procedure that performs 3 steps:

1) Create new user in the Users table
2) Get ID for new user
3) Add ID to roles table

Even better - the three steps should be inside a transaction so that if any one step fails, the whole thing gets rolled back. Example (using MS-SQL... your syntax may vary):

Declare @Err int

BEGIN TRANSACTION NEWUSER

-- SQL To Add User to Users table
-- Check for error
Set @Err = @@ERROR
If @Err <> 0 then goto NewUserFailed

-- SQL to get UserID
-- Check for error
Set @Err = @@ERROR
If @Err <> 0 then goto NewUserFailed

-- SQL to add to Roles table
-- Check for error
Set @Err = @@ERROR
If @Err <> 0 then goto NewUserFailed

--Done
COMMIT TRANSACTION NEWUSER
Return 0

--Failed
NewUserFailed:
ROLLBACK TRANSACTION NEWUSER
Return @Err