dcsimg
Results 1 to 16 of 16

Thread: "select @@identity;"

  1. #1

    Thread Starter
    New Member
    Join Date
    Nov 2010
    Posts
    14

    "select @@identity;"

    I am finding that "SELECT @@IDENTITY;" works about 3 times out of 4.

    I have no idea why it decides not to work sometimes. Anyone else found "SELECT @@IDENTITY;" to be unreliable?

    My code writes a transaction and then debit and credit postings which need the ID of the transaction ID as an external key.

    Code:
    strSQLCommand = "INSERT INTO Transactions " & _
        "(" & strSQLFields & ") " & _
        "VALUES(" & strSQLValues & ");"
    objCommand.Execute Options:=(adExecuteNoRecords Or adCmdText)
    'NOTE: the insert transaction statement never fails
    
    'retrieve the ID of the new Transaction
    objCommand.CommandText = "SELECT @@IDENTITY;"
    Set rsData = objCommand.Execute(Options:=adCmdText)
    intTransID = rsData.Fields("TransID").Value
    'SOMETIMES THE ABOVE LINE GETS THE ERROR "no current record"

  2. #2
    Super Moderator Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,335

    Re: "select @@identity;"

    Moved To Database Development
    Please use [Code]your code goes in here[/Code] tags when posting code.
    When you have received an answer to your question, please mark it as resolved using the Thread Tools menu.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    I dont answer coding questions via PM or EMail. Please post a thread in the appropriate forum section.

    Creating A Wizard In VB.NET
    Paging A Recordset
    What is wrong with using On Error Resume Next
    Good Article: Language Enhancements In Visual Basic 2010
    Upgrading VB6 Code To VB.NET
    Microsoft MVP 2005/2006/2007/2008/2009/2010/2011/2012/Defrocked

  3. #3
    Hyperactive Member
    Join Date
    Aug 2004
    Location
    Itasca, IL USA
    Posts
    266

    Re: "select @@identity;"

    I don't really know SQL Server, but could it be that nothing was inserted? The issue may not be with @@IDENTITY, but with what you are inserting?

  4. #4

    Thread Starter
    New Member
    Join Date
    Nov 2010
    Posts
    14

    Re: "select @@identity;"

    Quote Originally Posted by Chrissy View Post
    I don't really know SQL Server, but could it be that nothing was inserted? The issue may not be with @@IDENTITY, but with what you are inserting?
    No, the insert always works!

  5. #5
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    5,613

    Re: "select @@identity;"

    Read this please:
    "@@IDENTITY and SCOPE_IDENTITY 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."
    So you better of use SCOPE_IDENTITY if you are not sure on what you are using identity for.
    Try it and let us know how it's going.
    Also i would strongly,STRONGLY suggest that you program @@ commands inside your sql server(i hope you are not using access) and this is , apart of that you are certain the error is on the sql and not on your app, that you may get more help because i for example have forgotten vb6.Of course that is your choice.
    Slow as hell.

  6. #6

    Thread Starter
    New Member
    Join Date
    Nov 2010
    Posts
    14

    Re: "select @@identity;"

    Firstly I need to say that I am using Excel 2010 VBA (a fact that was obvious before the moderator moved my OP from the Excel forum to this one).

    I always get my code working on the client (Excel) before moving it to the server (MS-SQL).

    "SCOPE_IDENTITY" does not get a mention in any of my reference works (they say I should be using @@IDENTITY). Does "SCOPE_IDENTITY" require an @@ prefix? Will it work from Excel VBA?

    What "Scope" are we talking about here? (I would have thought that an "@@IDENTITY" SQL statement issued immediately after an "ADD" SQL statement would always be "in scope")

  7. #7
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,681

    Re: "select @@identity;"

    "I would have thought that an "@@IDENTITY" SQL statement issued immediately after an "ADD" SQL statement would always be "in scope"" -- the "scope" is the sql server... so let's say two people insert at the same time, UserA and UserB... UserA inserts a second before UserB does.... at the same time that UserB sent the command to SQL Server, UserA executes the @@IDENTITY command.... what you get back is the ID generate by UserB..... not UserA. Scope_Identity on the other hand will return the last inserted ID BY THAT CONNECTION...

    The thread was moved because the question is a database question not an Office question.

    No, SCOPE_IDENTITY() does not require @@... if it did, the suggestion would have been to use @@SCOPE_IDENTITY ... a simple search on MSDN would have cleared that up in a matter of seconds.

    If you're working with SQL Server, really you should take the time to learn about stored procedures and parameters. Stringing SQL commands together in the manner you are isn't easy and can lead to problems debugging. Not to mention that it isn't all that secure and leave you open to SQL Injection attacks (especially if you're including user input values).

    Just some thoughts to consider.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  8. #8

    Thread Starter
    New Member
    Join Date
    Nov 2010
    Posts
    14

    Re: "select @@identity;"

    "SELECT SCOPE_IDENTITY" gives me the error:
    "Invalid column name 'SCOPE_IDENTITY'"

  9. #9
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,681

    Re: "select @@identity;"

    Seriously... you're hearing us, but you're not listening. Took me less than 10 seconds to look it up on MSDN. It's not that hard. You open a browser.... type msdn.microsoft.com into the address bar, enter "scope_identity" into the search box. It'll even give you assistance with "scope_identity" "scope_identity()" and "scope_identity sql" as suggestions... pick any of them... even with just "scope_identity" you get results. the first of which is this link: http://msdn.microsoft.com/en-us/library/ms190315.aspx and as you can see... it is a FUNCTION ... so you have to call it as "scope_identity()"

    MSDN is your friend. Learn to use it.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  10. #10

    Thread Starter
    New Member
    Join Date
    Nov 2010
    Posts
    14

    Re: "select @@identity;"

    SELECT SCOPE_IDENTITY() is failing to retrieve a record

    Code:
    strSQLCommand = "INSERT INTO Transactions " & _
        "(" & strSQLFields & ") " & _
        "VALUES(" & strSQLValues & ");"
    objCommand.CommandText = strSQLCommand
    objCommand.Execute Options:=(adExecuteNoRecords Or adCmdText)
    'retrieve the ID of the new Transaction
    objCommand.CommandText = "SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY];"
    Set rsData = objCommand.Execute(Options:=adCmdText)
    The "Set rsData" statement returns the error:
    item cannot be found in the collection corresponding to the requested name or ordinal

    (The INSERT statement is working OK)

    Note: I have looked at MSDN and it implies that "SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY];" should work.... Perhaps "Excel 2007 VBA" (Wrox) recommends @@IDENTITY because you cannot use SCOPE_IDENTITY() from VBA????


    (I have often found MSDN to be unhelpful or incomprehensible which is why I am not in the habit of using it as a first resource)

  11. #11

    Thread Starter
    New Member
    Join Date
    Nov 2010
    Posts
    14

    Re: "select @@identity;"

    just a thought: might someone in the VBA forum be able to help me?

  12. #12
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,681

    Re: "select @@identity;"

    Do it all in one shot.... take a look at this thread: http://www.vbforums.com/showthread.php?t=461142

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  13. #13
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,327

    Re: "select @@identity;"

    Which version/edition of SQL Server are you using? (eg: 2005 Express)

    Have you tried removing ; from the end of the query?
    Quote Originally Posted by fuzzbug View Post
    just a thought: might someone in the VBA forum be able to help me?
    No, or at least not as well as the people here.

    While you are using VBA, none of the code you are using is specific to VBA - it is all valid in VB5/6 and VBScript too, and it is also valid (but not ideal) in VB.Net with very minor changes.

    On top of that, the important part of your problem (the query you are sending to the database) has absolutely nothing to do with any kind of VB whatsoever - it is purely about the database.

  14. #14

    Thread Starter
    New Member
    Join Date
    Nov 2010
    Posts
    14

    Re: "select @@identity;"

    Got it working!

    The final piece of the jigsaw was using field number not field name (I always used numbers until someone on a forum told me I should use names!)

    Here is the working code:

    Code:
    strSQLCommand = "INSERT INTO Transactions " & _
        "(" & strSQLFields & ") " & _
        "VALUES(" & strSQLValues & ");"
    objCommand.CommandText = strSQLCommand
    objCommand.Execute Options:=(adExecuteNoRecords Or adCmdText)
    
    ' retrieve the ID of the new Transaction
    objCommand.CommandText = "SELECT SCOPE_IDENTITY()"
    Set rsData = objCommand.Execute(Options:=adCmdText)
    intTransID = rsData.Fields(0).Value
    ' intTransID = rsData.Fields("TransID").Value
    ' NB Field names do not work in this context
    Thanks to everyone who responded to my cry for help.

    (I will be back in about 2 weeks asking why I cannot get this working as a stored procedure... )

  15. #15
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,327

    Re: "select @@identity;"

    I'm guessing you accidentally misled us earlier with your comment "The "Set rsData" statement returns the error:", because your latest post implies that the error is in another location that makes far more sense.

    Using field names is generally the best idea (it is debatable/irrelevant here), and should work in this case, eg:
    Code:
    objCommand.CommandText = "SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY];"
    intTransID = rsData.Fields("SCOPE_IDENTITY").Value
    ...or:
    Code:
    objCommand.CommandText = "SELECT SCOPE_IDENTITY() AS NewID"
    intTransID = rsData.Fields("NewID").Value

  16. #16

    Thread Starter
    New Member
    Join Date
    Nov 2010
    Posts
    14

    Re: "select @@identity;"

    Thanks again. I have amended my code to use the name as you suggest.

Tags for this Thread

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width