CODEWITHSUNDEEP
sql-servervb.net
Maury Markowitz
Maury Markowitz

Reputation: 9273

SCOPE_IDENTITY, @@IDENTITY and IDENT_CURRENT return null

dbo, full permissions, local SQL Server Express database, tables I created and own.

I have just INSERTed a row into MLA and if I do a SELECT * FROM MLA I get the row I just inserted...

19 2019-04-16 15:02:52.000 Test

The "19" is the key, which is an identity column. So I try to get that key with any of these...

SELECT SCOPE_IDENTITY()
SELECT IDENT_CURRENT('MLA')
SELECT @@IDENTITY

And they all return NULL.

The row was inserted with this line of code:

DbS.Execute(SQL)

where DbS is an active, working connection to the server which was also used for the insert. I then attempt to retrieve the key with this code:

Dim DT as RecordSet
DT = DbS.Open("SELECT IDENT_CURRENT('USAA_ArgusVal_MLA')")

I can imagine that the Open makes a new scope, that would not explain @@IDENTITY

UPDATE: here is the solution that was suggested below, I simply added the SELECT to the existing INSERT SQL statement, and changed the .Execute to using this code

SQL &= vbCrLf & "SELECT SCOPE_IDENTITY() AS MLAKey"
Dim DI As RecordSet = DbS.Open(SQL)
If DI.Read Then
    MLAKey = DI!MLAKey
Else
    MLAKey = ""
End If

Upvotes: 1

Views: 213

Answers (2)

Lukasz Szozda
Lukasz Szozda

Reputation: 175596

You could use OUPTUT clause and perform insert + select as single operation:

INSERT INTO MLA(...)
OUTPUT inserted.identity_column_name
VALUES(...)

Upvotes: 2

Piotr Palka
Piotr Palka

Reputation: 3159

Your "dbs" connection probably has some connection pooling.

Write SQL INSERT and then SELECT identity in the same SQL statement. Best solution is to create a stored procedure to insert data and then return current identity.

Upvotes: 1

Related Questions