RE: FN-FORUM: asp/sql - the correct way to get the ID of the record you just added?

date posted 9th January 2007 11:33

[EMAIL REMOVED] wrote:
> This isnt something I'm doing now, but its something I've
> often wondered if I do the right way, I'm fairly sure I dont.
>
> Imagine you have a membership database, a user signs up and
> you add their info to your table which automatically assigns
> an ID to the record. You then need to add records to a
> 'link' table to associate (for example) the user with a
> particular mailing list/s. To do that you need the ID of the
> record you just added for the user.
>
> In the past I've just done a select statement to pull out the
> highest ID number based on the assumtion that the highest ID
> will be the last record added - however if, on the rare
> occasion this might happen, another user signs up at the same time
> that ID will be wrong.
>
> Whats the correct way of doing it? is there one?

Depends on database. PostGreSQL and Oracle have ways of pre-reserving an id
before insert. MySQL has

SELECT LAST_INSERT_ID()

MS SQL Server has

SELECT IDENT_CURRENT - returns the last identity value generated for a
specific table in any session and any scope.
SELECT @@IDENTITY - returns the last identity value generated for any table
in the current session, across all scopes.
SELECT SCOPE_IDENTITY - returns the last identity value generated for any
table in the current session and the current scope.

depending on how you are using it. MS Access (not going to start a flame
war by describing it) has no direct way afaik, so then yes you are stuck
with highest id or making it unique on other fields. Don't use accesss much
so may be out of date there.

HTH,

Dai




--
TechnologyAngel
http://www.technologyangel.co.uk/