SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Enthusiast drorit30's Avatar
    Join Date
    May 2003
    Location
    tel aviv
    Posts
    31
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    getting the new ID from SQL server after row creation

    Hi all,

    I have a table in sql which has a field ID (integer which increments automatically - autonumber)

    After i create a new row i want to send out an email which links to the ID of the new row just created.


    LocalRecordset.AddNew
    LocalRecordset("name") = new_name
    LocalRecordset("email") = new_email
    LocalRecordset.Update

    Send email with link = www.123.com/info.asp?id= & LocalRecordset("id")
    This doe not work on SQL server, but it does work on ACCESS.

    I know I can query the table again with the fields I just entered in the new row, but is there another way to do this like it is done in ACCESS?

    Thanks in advance,
    Dror

  2. #2
    S1tepoint Surfer drochili's Avatar
    Join Date
    Aug 2002
    Location
    Mississauga, Canada
    Posts
    466
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Dror... you can either use SELECT @@IDENTITY or IDENT_CURRENT(tableName] or SCOPE_IDENTITY()
    My Blog: DR's Corner
    Random babble: TheCoolerZone

  3. #3
    SitePoint Enthusiast drorit30's Avatar
    Join Date
    May 2003
    Location
    tel aviv
    Posts
    31
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the reply. I am not familiar with the calls you specified. Can you please give me the full statement assuming the table name is 'shipments' and the auto-number name is 'id'.

    Thanks in advance,
    Dror

  4. #4
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    set @variable = SCOPE_IDENTITY()

    assuming that id is an auto number

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    SCOPE_IDENTITY works on Access databases? whoa
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    dror, i am told @@identity works in Access, but if you can't get it to work, you could also retrieve the autonumber by querying back the row you just inserted using the same column values that you used to insert it with

    this technique works in all databases, all the time, without special functions, and with no transaction locks or anything

    the only thing it relies on is that you will not be entering completely duplicate rows

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    drorit30 getting the new ID from SQL server after row creation
    sorry i was carried away by your title. And i refered to scope_identity() simply because it works within the scope of the transaction.

    Afrika

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    my mistake!! of course SCOPE_IDENTITY works, and of the three methods (@@identity, ident_current, scope_identity) it is the best one to use
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think drorit30, should correct the title of the thread. from SQL server to access

  10. #10
    S1tepoint Surfer drochili's Avatar
    Join Date
    Aug 2002
    Location
    Mississauga, Canada
    Posts
    466
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This doe not work on SQL server, but it does work on ACCESS.

    I know I can query the table again with the fields I just entered in the new row, but is there another way to do this like it is done in ACCESS?
    by the bolded fields, I assumed he was asking about SQL Server, not access?
    My Blog: DR's Corner
    Random babble: TheCoolerZone

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yeah, i got totally turned around back-@sswards on this thread, sorry
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •