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

date posted 9th January 2007 11:07

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?