retreiving autonumber of most recently saved record

[asanjue]

I'm using vb6.0 sp4 with SQL-Server 7.0. Using ADO I would like to retreive the autonumber generated for the most recently saved record. Any assistance at all will be greatly appreciated.
Thanks [sig][/sig]

 

[culshaja]

SQL Server stores the recently generated 'autonumber' value in a variable called @@IDENTITY. The best way to get access to this is to use a stored procedure to insert the row and define an output parameter. After the INSERT statement has been issued you can then set the output parameter to the new IDENTITY value using:

[tt]SELECT @var = @@IDENTITY[/tt]

You either have to set the Connection type to be server side cursor or use an [bold]IsEmpty()[/bold] test on the Output parameter in the Command object's Parameter collection i.e.

[tt]
while not IsEmpty(cmCommand.Parameters("@var&quot
'do nothing
wend
[/tt]

This halts the execution long enough for the output parameter to populate itself if using a client-side cursor.

James

p.s. I would like to know of a more elegant way of doing this. [sig]<p>James Culshaw<br><a href=mailto:jamesculshaw@active-data-solutions.co.uk>jamesculshaw@active-data-solutions.co.uk</a><br><a href=

http://www.active-data-solutions.co.uk>

Active Data Solutions</a><br> [/sig]

 

[swilliams]

In DAO you have the LastModified method of a recordset, but I do know of an equivalent in ADO - no help there.

But I have just done the following (in DAO, but can be converted to ADO):
1. Create a recordset that is guaranteed to return zero records (I did this by using the WHERE clause ...WHERE False=-1)
2. Use the AddNew method of the recordset to add your record, generating an ID.
3. The recordset will now hold only the one record that you have just added. Do a MoveLast to move to that record and then query the rs(&quot;ID&quot field.

Another way would be to use BookMarks. [sig]<p>Simon<br>[/sig]

 

[culshaja]

Preaching time!:

Using direct table appends is not very efficient and doesn't allow for layering of your code. The use of stored procedures is the way to go if you can. It improves stability and allows for easier and improved maintainability.

James [sig]<p>James Culshaw<br><a href=mailto:jamesculshaw@active-data-solutions.co.uk>jamesculshaw@active-data-solutions.co.uk</a><br><a href=

http://www.active-data-solutions.co.uk>

Active Data Solutions</a><br> [/sig]