Return ID after update statement

[bloedgever]

Is there a function in VB.NET or C# that returns me the ID of a record when i do an insertstatement?
something like:
dim id = cmd.ExecuteNonQuery(QUERY,connection)

[jmcilhinney]

@@IDENTITY is a global variable of most (all?) SQL compliant databases. Use "SELECT @@IDENTITY" to get the last ID value generated. This is independant of the development language.

[Andy]

In other words, in your QUERY, have a second statement preceded by a semicolon (SELECT @@IDENTITY) or u can use a stored procedure with the last line (RETURN @@IDENTITY).

ie:

"INSERT INTO table (column) VALUES (value);SELECT @@IDENTITY"

[The_Duck]

Note that if you are using Access you will have to perform the Select @@Identity in a seperate query.

ie (Pesudocode!)

cmdInsert.execute
autonumber = cmdGetID.execute

[Andy]

another thought just occured to me...If you have triggers that may get fired off when you insert, you will get back the identity of the other tables, etc...an alternative is to use Scope Identity.

It is like @@Identity, but it guarantees that if there are triggers firing on the insert you will get the identity of the record YOU inserted. If there are triggers firing @@Identity will return the identity of the last record inserted by the trigger program. Scope Identity is new in SQL Server 2000.

FROM A BOOK ONLINE:
SCOPE_IDENTITY Returns the last IDENTITY value inserted into an IDENTITY column in the same scope. A scope is a module -- a stored procedure, trigger, function, or batch. Thus, two statements are in the same scope if they are in the same stored procedure, function, or batch.

Syntax
SCOPE_IDENTITY( )

[Briantcva]

Another solution to the identity issue is using a key table. The key table has two columns - TableName and NextValue - and exists solely to provide PKs. Setting up a class to access the key table means you can also grab an array of PKs in one swoop. Another benefit of the key table is that if you're writing test code (say w/ NUnit) you can reset the key table values at the end of the test code.