Results 1 to 11 of 11
  1. #1
    Join Date
    May 2004
    Posts
    5

    Unhappy Unanswered: How to get identity field value after recordset update

    I want to add a record and then get the automatically generated identifier(ReqChildID in this case). Is there a better way? Why won't this one work.

    This is what I have:

    rsRec.AddNew
    rsRec("RequisitionID") = intReqNum
    rsRec("CreatedBy") = cint(session("empno"))
    rsRec("DateCreated") = Now
    rsRec.Update
    intItemId = rsRec("ReqChildID")
    rsRec.Close



    Forgive me if this has already been posted, but I couldn't find it.

    [edit to add:]

    rsRec is an ADODB.Recordset in case that wasn't clear.

  2. #2
    Join Date
    Feb 2004
    Location
    Poland
    Posts
    32
    Look at @@IDENTITY and SCOPE_IDENTITY in BOL.

    INSERT INTO T_Cup SELECT coffee, sugar, hot_water, milk
    EXEC sp_stir
    EXEC sp_drink
    GO

  3. #3
    Join Date
    May 2004
    Location
    Moscow, Russia
    Posts
    29
    1 intItemId set identity for replication
    2 use keyset recordset type

  4. #4
    Join Date
    May 2004
    Posts
    5
    I appreciate the quick replies.

    I am using @@Identity and it works well enough for my purposes.

    [edit] I found the BOL. Never even looked at it before. Thanks for pointing it out

    Thanks again all.
    Last edited by robot99; 05-19-04 at 12:07.

  5. #5
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    Look at SCOPE_IDENTITY... @@IDENTITY is global to all transactions on the table, where SCOPE_IDENTITY is limited to the scope of a single transaction. You could potentially get the ID for the wrong transaction with @@IDENTITY.
    That which does not kill me postpones the inevitable.

  6. #6
    Join Date
    Feb 2004
    Location
    Poland
    Posts
    32
    Quote Originally Posted by Seppuku
    Look at SCOPE_IDENTITY... @@IDENTITY is global to all transactions on the table, where SCOPE_IDENTITY is limited to the scope of a single transaction. You could potentially get the ID for the wrong transaction with @@IDENTITY.
    Agree. But it's good to know what differs those cases. Let the user choose what he needs. Give him fishing rod, not fish .

    INSERT INTO T_Cup SELECT coffee, sugar, hot_water, milk
    EXEC sp_stir
    EXEC sp_drink
    GO

  7. #7
    Join Date
    May 2004
    Posts
    5
    Due to time constraints I went with @@Identity. The database hits aren't very common so I will assume a certain small probability that there will be an error from time to time.

    That being said, I would prefer Scope_Identity for 100% accuracy. I am continuing to work with SCOPE_IDENTITY when I can to try to make it work for me. As of this morning I got it to supply the correct ID using enterprise manager to test. I'll eventually get it to work in my code.

    Thank you guys for helping me solve this problem.

  8. #8
    Join Date
    Sep 2003
    Posts
    522
    @@identity may acquire a value of anything else only when the target table contains a trigger that performs an insert into another table with identity field. if this is the case then scope_identity() needs to be used.

    on another thought, you should put dml statements into a stored procedure and call it from your front end.

  9. #9
    Join Date
    May 2004
    Posts
    5
    I inherited this app. I would love to convert it to using stored procedures. I just need to get this section done so I can have a completed app, and a happy boss. Then I can convert as I go to make it all easier to work with.

    Grrrr.... I can't seem to get SCOPE_IDENTITY() to happen on the page.

    I have:

    Code:
    	set cn      = Server.CreateObject("ADODB.Connection")
    	set rsRec   = Server.CreateObject("ADODB.Recordset")
    	set rsIdent = Server.CreateObject("ADODB.Recordset")
    	cn.Open dbConnection
    
    		rsRec.Open sSQL,cn, 3 ,3 
    		rsRec.AddNew 
    		rsRec("ReqID") = intReqNum
    		rsRec("CreatedBy")     = cint(session("empno"))
    		rsRec("DateCreated")   = Now
    		rsRec.Update 
    		rsRec.Close 
    
    
    		sSQL = "SELECT SCOPE_IDENTITY() as Ident"
    		rsIdent.Open sSQL,cn, 3 ,3
    		intItemId = rsIdent("Ident")
    		rsIdent.Close
    I have no idea why this doesn't work. I thought as long as I keep the connection(cn) open then this would work. Guess I'm wrong? Do I need to use the same recordset?

  10. #10
    Join Date
    Sep 2003
    Posts
    522
    sSQL="insert tbl (...) values (...) select ident=scope_identity()"
    set rsRec=cn.execute(sSQL)

  11. #11
    Join Date
    May 2004
    Posts
    5

    Talking

    I have simplified the statements that are actually in the code. Although that suggestion would likely make it work(as it works when I test it in the Query Analyzer), there are quite a few conditions that would affect the SQL statement and at this point I don't have the time to switch it to that style.

    Its' done for the time being. I pushed it out for beta.

    I'm only sorry I couldn't get it all to work fast enough, but that's what updates are for.

    Thanks again for the help and pointing me in the right direction.

Posting Permissions

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