"select @@identity;"

[fuzzbug]

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.

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"

[Hack]

Moved To Database Development

[Chrissy]

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?

[fuzzbug]

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!

[sapator]

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.

[fuzzbug]

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")

[techgnome]

"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

[fuzzbug]

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

[techgnome]

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

[fuzzbug]

SELECT SCOPE_IDENTITY() is failing to retrieve a record

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)

[fuzzbug]

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

[techgnome]

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

-tg

[si_the_geek]

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

Have you tried removing ; from the end of the query?

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.

[fuzzbug]

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:

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... )

[si_the_geek]

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:

objCommand.CommandText = "SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY];"
intTransID = rsData.Fields("SCOPE_IDENTITY").Value

...or:

objCommand.CommandText = "SELECT SCOPE_IDENTITY() AS NewID"
intTransID = rsData.Fields("NewID").Value

[fuzzbug]

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