obtain the value of a newly created Autonumber in SQL Server

tadropik · Mar 8, 2012

Tbl_Customer has 2 fields.
IntID - AutoNumber
Name - Text

Using VBA code in Access I can obtain the value of a newly created Autonumber. Because Access creates the Autonumber when the record is created, the code below assigns iIntID the newly created number.
Set rs = CurrentDb.OpenRecordset("Tbl_Customer")
rs.AddNew
rs.Fields("Name") = "Bob Smith"
iIntID = rs.Fields("IntID")
rs.Update
rs.Close

Because SQL creates the Autonumber After the record is saved, the code above doesn't work.
I'm not sure how to obtain the newly created number.
Any help would be appreciated.

pbaldy · Mar 8, 2012

Like so:

...
rs.Update
rs.Bookmark = rs.LastModified
iIntID = rs!IntID

tadropik · Mar 8, 2012

Very nice pbaldy.

Simple and it works great.

Thank you.

pbaldy · Mar 8, 2012

Happy to help!

mdlueck · Mar 23, 2012

tadropik said:
I'm not sure how to obtain the newly created number.

Or if you could use a Stored Procedure to perform the INSERT, this is the way to receive back the new AutoNumber value in the return of the SP:

Code:

[COLOR=Blue][B]DECLARE @id smallint[/B][/COLOR]

BEGIN TRAN
INSERT INTO [dbo].[projects] (authid,logtimestamp,title)
[COLOR=Blue][B]OUTPUT INSERTED.id[/B][/COLOR]
VALUES (@authid,CURRENT_TIMESTAMP,@title);
COMMIT TRAN

[B][COLOR=Blue]SELECT @id AS [id][/COLOR][/B]

obtain the value of a newly created Autonumber in SQL Server

tadropik

Registered User.

pbaldy

Wino Moderator

tadropik

Registered User.

pbaldy

Wino Moderator

mdlueck

Sr. Application Developer

Similar threads

Users who are viewing this thread