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 8 of 8

Thread: Return Identity

  1. #1
    Regular Coder
    Join Date
    Nov 2002
    Location
    Loughborough (UK)
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Return Identity

    Hi,

    Previously all web apps I have done have been in asp.net (c#) using SQL server. now I'm doing a small app for myself in asp.net (c#) using a microsoft database. What I want to know is how you return the identity of a newly inserted record when you use a inline sql string. I've tried inserting the record then running a select for the top 1 record ordering my the primary key descending, but sometimes this seems to be running too quickly and hence dosn't return the correct ID. Is there another way to do this?

    Thanks in advance for you help.

    Chris

  • #2
    Regular Coder
    Join Date
    Jun 2002
    Location
    Cincinnati, OH
    Posts
    545
    Thanks
    0
    Thanked 0 Times in 0 Posts
    does this sig match?

  • #3
    Regular Coder
    Join Date
    Nov 2002
    Location
    Loughborough (UK)
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Inline Sql

    Thanks, but this is sql-server. I'm executing a sql string on the web page. i.e. a simple...

    "INSERT INTO table (column) VALUES ("+ value +")";

    How would I return the identity of that?

    Thanks,
    Chris

  • #4
    Senior Coder Spudhead's Avatar
    Join Date
    Jun 2002
    Location
    London, UK
    Posts
    1,856
    Thanks
    8
    Thanked 110 Times in 109 Posts
    You can do:

    "SET NOCOUNT OFF INSERT INTO table (column) VALUES ("+ value +")" SELECT @@IDENTITY AS newID SET NOCOUNT ON;"

    I think

  • #5
    Regular Coder
    Join Date
    Nov 2002
    Location
    Loughborough (UK)
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts
    No, that dosn't work... even if you run it in a query design in the acutal database. It knows that the statement should end after the list of values so throws an error. Any other ideas?

  • #6
    New to the CF scene
    Join Date
    Jun 2004
    Location
    South Africa
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi,

    Don't know if it's ideal, but here's something that works on my PC with MS SQL Server


    sql = "execute('INSERT INTO TryInsert(TheValue) VALUES (''CF'') SELECT NewVal = @@identity')"
    set rs = conn.execute(sql)
    if NOT rs.EOF Then
    Response.Write(rs("NewVal"))
    End If

    Wouldn't a stored procedure give you more freedom though?

  • #7
    Senior Coder
    Join Date
    Jun 2002
    Location
    Wichita
    Posts
    3,880
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Spudhead
    You can do:

    "SET NOCOUNT OFF ; INSERT INTO table (column) VALUES ("+ value +")"; SELECT @@IDENTITY AS newID ; SET NOCOUNT ON;"

    I think
    Adding the semi-colons as shown in the quoted section above should fix the problem of that statement not working.
    Check out the Forum Search. It's the short path to getting great results from this forum.

  • #8
    Regular Coder
    Join Date
    Jun 2002
    Location
    New York, USA
    Posts
    175
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Be careful using @@IDENTITY... it can lead to problems in complicated stored procedures

    SCOPE_INDENTITY() is more specific. One should at least be aware of the difference between the two and avoid possible headaches.


  •  

    Posting Permissions

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