SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Enthusiast
    Join Date
    Jan 2005
    Location
    epsom
    Posts
    59
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Get id of just added row from database

    Hi

    Im wondering how i can get the autonumberid of a just added item to the database so that i can match it up to another table.

    I know how to add it in php but not asp

    thank

    Nick

  2. #2
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    what db are u using?

    if its access or sql server, use the
    scope_identity()
    function

  3. #3
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    sorry you run this function in the database not in the asp file

  4. #4
    SitePoint Enthusiast
    Join Date
    Dec 2002
    Location
    Dubai - UAE
    Posts
    77
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT Max([Field]) as [Alias] FROM [Table];

  5. #5
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    this would not be efficient.
    SELECT Max([Field]) as [Alias] FROM [Table];


    SQL server and access has 3options to work with
    1. scope_idenitity()
    2. @@identity
    3. ident_current

    see books online




    SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are similar functions in that they return values inserted into IDENTITY columns.

    IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the value generated for a specific table in any session and any scope. For more information, see IDENT_CURRENT.

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

    For example, you have two tables, T1 and T2, and an INSERT trigger defined on T1. When a row is inserted to T1, the trigger fires and inserts a row in T2. This scenario illustrates two scopes: the insert on T1, and the insert on T2 as a result of the trigger.

    Assuming that both T1 and T2 have IDENTITY columns, @@IDENTITY and SCOPE_IDENTITY will return different values at the end of an INSERT statement on T1.

    @@IDENTITY will return the last IDENTITY column value inserted across any scope in the current session, which is the value inserted in T2.

    SCOPE_IDENTITY() will return the IDENTITY value inserted in T1, which was the last INSERT that occurred in the same scope. The SCOPE_IDENTITY() function will return the NULL value if the function is invoked before any insert statements into an identity column occur in the scope.

    See Examples for an illustration.

  6. #6
    SitePoint Enthusiast
    Join Date
    Dec 2002
    Location
    Dubai - UAE
    Posts
    77
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    this would not be efficient.
    Take a look at following article. You cannot guarantee these functions will work for all databases. While Max() does work with all databases

    http://www.aspfaqs.com/aspfaqs/ShowFAQ.asp?FAQID=79

    Offcourse there are ways to do things, so why not one should go for the one which is more handy?
    I know how to add it in php but not asp
    I personally think Max() is easier to remember when you have to work with different databases.

  7. #7
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You cannot guarantee these functions will work for all databases. While Max() does work with all databases
    they will work for Microsoft databases esp scope_identity()

    Because it works within the scope of the transaction

    here a quote from the article you recommended
    If you wish to use SQL to add data to your Access DB, then this is undoubtedly the best way to do it. (And thanks to Xanderno for correcting my March 5th post.)
    The select max field is only efficient if...
    1. You are not using MS databases
    2.
    Quote from your article.

    However, if you want to use ADO-style coding to add records (in particular, you want to use ADODB.RecordSet.AddNew), then read on...
    The above article works best only if you apply locking to your database. then again in a real high hit db, you might run into problems of scalability

    Then again and most important, it brings us back to my original saying.

    ITS NOT EFFICIENT.

    Go with the flow,
    ....else fight the flow

    Afrika

  8. #8
    SitePoint Enthusiast
    Join Date
    Dec 2002
    Location
    Dubai - UAE
    Posts
    77
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    they will work for Microsoft databases esp scope_identity()
    Sure? Does it work with MSSQL 7? NO!

    Offcourse there are ways to do things, so why not one should go for the one which is more handy?

  9. #9
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you win Sir !
    :-)


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
  •