insert SQL


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: insert SQL

  1. #1
    Join Date
    Feb 2005
    Posts
    2

    insert SQL

    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

  2. #2
    Join Date
    Jun 2004
    Location
    Pakistan
    Posts
    292
    What kind of database are you using?
    new to programming but getting ther

  3. #3
    Join Date
    Jan 2004
    Location
    Alexandria, VA
    Posts
    392
    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
    Bob Rouse
    Dimension Data

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center