Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    New Coder
    Join Date
    Apr 2006
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Is there a way to insert a record into SQL Server DB and retrieve unique ID?

    Hi all - I have an asp page that inserts records into the sql server database but I need to retrieve the unique (auto-incremented) ID for that record and display on the results page for the user. Is there a way to do this? Any help much appreciated!

  • #2
    Regular Coder
    Join Date
    Mar 2007
    Posts
    505
    Thanks
    1
    Thanked 19 Times in 19 Posts
    Easiest way is to do the INSERT then run a SELECT. However, it requires that you use a TIMESTAMP field to track your records.

    If you are using MySQL, then you can use LAST_INSERT_ID(). MSSQL and Access to not have this feature.

    Code:
    strSQL = "INSERT INTO your_table (col1,col2,col3) VALUES ('test1','test2','test3');"
    
    strSQL2 = "SELECT RecordID FROM your_table WHERE col1 = 'test1' AND col2 = 'test2' ORDER BY LastEdit DESC LIMIT 1;"
    To say my fate is not tied to your fate is like saying, 'Your end of the boat is sinking.' -- Hugh Downs
    Please, if you found my post helpful, pay it forward. Go and help someone else today.

  • #3
    Senior Coder
    Join Date
    Jun 2002
    Location
    Zwolle, The Netherlands
    Posts
    1,120
    Thanks
    2
    Thanked 31 Times in 31 Posts
    Quote Originally Posted by Daemonspyre View Post
    Easiest way is to do the INSERT then run a SELECT. However, it requires that you use a TIMESTAMP field to track your records.

    If you are using MySQL, then you can use LAST_INSERT_ID(). MSSQL and Access to not have this feature.
    MSSQL does have this feature. In fact, there are different ways to get the identity, depending of your needs.

    SELECT @@IDENTITY will return the last inserted value in an identity column of the database. Databasewide. So if your insert fires a trigger, which does an insert also, you will get the identity of the insert by the trigger.

    SELECT SCOPE_IDENTITY() AS [IDENTITY] will give you the last inserted identity value in the current scope, but also table independant

    SELECT IDENT_CURRENT('tablename') AS [IDENTITY] will give the last inserted value in the identity column of the mentioned table.

    So choose well and be happy with MSSQL also.
    I am the luckiest man in the world

  • #4
    Regular Coder
    Join Date
    Mar 2007
    Posts
    505
    Thanks
    1
    Thanked 19 Times in 19 Posts
    Then I happily stand corrected.

    Thanks!
    To say my fate is not tied to your fate is like saying, 'Your end of the boat is sinking.' -- Hugh Downs
    Please, if you found my post helpful, pay it forward. Go and help someone else today.

  • #5
    New Coder
    Join Date
    Apr 2006
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you both for your help! Now I am trying to figure out how to display the SELECT IDENT_CURRENT('tablename') AS [IDENTITY] results in the asp page. Sorry, everything I try does not seem to work Here is the code:

    SQL="INSERT INTO tablename(column1, column2) VALUES ('" & value1 & "', '" & value2 & "'); SELECT IDENT_CURRENT('tablename') AS ['IDENTITY']"

    'execute the SQL
    Conn.Execute(SQL)

  • #6
    Senior Coder
    Join Date
    Jun 2002
    Location
    Zwolle, The Netherlands
    Posts
    1,120
    Thanks
    2
    Thanked 31 Times in 31 Posts
    the second part of the query returns a recordset (of one record). So you cannot execute with conn.execute the way you do, but this call returns a recordset object. Catch it in a variable and use it.

    Set rs = conn.Execute(SQL)

    then you can access the id by doing


    id = rs("IDENTITY")
    I am the luckiest man in the world


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •