~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Nick Charlesworth" <nick@f...>
Subject: [aspx_professional] using @@IDENTITY directly in code without a
stored procedure


: Is it possible to use the @@IDENTITY SQL function within VB.NET code
: without having to create a stored procedure in SQL server.
:
: I have an insert statement with about 20 fields in it and then want to get
: the IDENTITY field of the record that has just been added by the INSERT
: statement.
:
: What type of object(s) could I use to run the INSERT statement and get the
: IDENTITY value back.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

<not an expert>

@@IDENTITY is scoped to the current connection.

So you could execute another SQL statement over the current connection to
return @@IDENTITY. You could even do it in a batch (with ; between
statements):

strSQL = _
    "INSERT INTO myTable (myField) VALUES ('myValue');SELECT @@IDENTITY"

Using a sproc though is much better...

Cheers
Ken