SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Enthusiast
    Join Date
    May 2004
    Location
    Cambs
    Posts
    70
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy retrieving an id of newly created record

    When a new record is inserted into access db it creates an id, how can I return this value for a record I have just created?

  2. #2
    SitePoint Zealot Biffy's Avatar
    Join Date
    Aug 2003
    Location
    UK
    Posts
    101
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try:

    set rs=Server.CreateObject("ADODB.Recordset")
    rs=objconn.execute("SELECT @@IDENTITY")
    recordID=rs(0)
    set rs=nothing
    Sitenetics - Lending webmasters a helping hand

  3. #3
    SitePoint Enthusiast
    Join Date
    May 2004
    Location
    Cambs
    Posts
    70
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    SO where would I put the code? below is what I have already and my record ID is called recID

    var MM_Q1B = String(Session("q1b"));
    var MM_Q2B = String(Session("q2b"));
    var MM_Q3B = String(Session("q3b"));
    var MM_Q4B = String(Session("q4b"));
    var MM_Q5B = String(Session("q5b"));
    var rsActivity = Server.CreateObject("ADODB.Command");
    rsActivity.ActiveConnection = MM_quickpoll_STRING;
    rsActivity.CommandText = "Insert INTO tblWeekOne(Q1B,Q2B,Q3B,Q4B,Q5B) Values ('"+MM_Q1B+"','"+MM_Q2B+"','"+MM_Q3B+"','"+MM_Q4B+"','"+MM_Q5B+"')";
    rsActivity.Execute();

  4. #4
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you could use the scope_identity() function, as well, which you really cant go wrong with, because it works in the scope of the last inserted transaction

    unlike the @@identity

  5. #5
    SitePoint Enthusiast Nawaz Ijaz's Avatar
    Join Date
    Feb 2005
    Location
    Lahore, Pakistan
    Posts
    96
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    But if some one else insert another row before the execution of my particular code that fetches my id say your fuction afrika ??

    What I do i query again with the inserted parameters and thats works fine at the cost of speed. Any better and reliable way to do that. Please share.
    Nawaz Ijaz [Lahore, Pakistan]
    Boy: He is big..i wont like to fight him...
    Achilles: Thatz Y no one wud remember u after thousand years...!!

  6. #6
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i really dont know about the inner workings of the computer, but i know there is somethign like message queueing. Such that it would pick each insert as its done. Thats why its called a mega hertz processor. processes info in milli seconds.

    identity_scope() works in each scope and shoudl be able to cater for your transactions

  7. #7
    SitePoint Zealot Biffy's Avatar
    Join Date
    Aug 2003
    Location
    UK
    Posts
    101
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The @@identity would go straight after your execute statement:

    rsActivity.Execute();

    I've always used this and have never had problems.
    Sitenetics - Lending webmasters a helping hand

  8. #8
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    SQL Books online

    @@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT are similar functions in that they return the last value inserted into the IDENTITY column of a table.

    @@IDENTITY and SCOPE_IDENTITY will return the last identity value generated in any table in the current session. However, SCOPE_IDENTITY returns the value only within the current scope; @@IDENTITY is not limited to a specific scope.

  9. #9
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)


Bookmarks

Posting Permissions

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