Databases: Get ID of new record after INSERT

[4waystop]

Hi

I've been relentlessly querying google about this and got nowhere, all I can find is how to do it in VB Script or using LiveWire.

What I want to know is once I've inserted a new record to the database (in this case MS SQL Server), how do I find out the Autogenerated indexing number for that record?

Currently I have:

var adoConnection = Server.CreateObject("ADODB.Connection");
adoConnection.Open("Data Source=***;Database=
***;User Id=***;Password='***'");

var perSQL = "INSERT INTO ... VALUES(...)";
RS = adoConnection.Execute(perSQL);

At this point I need to get the ID of the record inserted and then use it in a second insert procedure.

All Help Much Appreciated!!

Thanks

 

[rac2]

You might want to post this in the ASP forum. You cannot actually do this with client-side Javascript.

The last identity value created is available in T-SQL in the variable @@IDENTITY. Use a stored procedure for the first INSERT statement. Within the stored procedure set a variable equal to @@IDENTITY. Declare that variable as an OUTPUT parameter in the stored procedure. Instead of a SQL statement as a source, use a command with parameters to call the stored procedure. One of the parameters will be an output type and it will contain the autogenerated value you need. Or it may be possible to perform both INSERTs in the same stored procedure in which case you wont need the OUTPUT parameter.

 

[adam0101]

The SQL Forum here is awesome if you have more questions.
forum183

Adam
while(ignorance){perpetuate(violence,fear,hatred);life=life-1};

 

[adam0101]

Actually, by going to that forum, I learned that you should use SCOPE_IDENTITY() instead of @@IDENTITY. If your table uses (or ever might use) triggers that insert rows, @@IDENTITY will return the id of the triggered table instead the one you were expecting.

Adam
while(ignorance){perpetuate(violence,fear,hatred);life=life-1};