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

date posted 9th January 2007 19:29

Scope_identity() seems to be the most appropriate solution but make sure
that you check out the other options as well.

Also, wrap your multiple inserts in a transaction and rollback if
there's a problem inserting the subsequent data.

I had a horrendous problem recently whereby I was getting deadlocks due
to us using a trigger to update ID fields (ID was per registration ID in
the table).

Eventually had to turn off the trigger functionality (which was working
quite happily with all the other tables) and pre-select by doing a
max(id) from table where reg_id =3D 12345 and then explicitly inserting
that value.

In SQL server you can also specify the start id and increment of the
identity field.

Cheers
Andy

-----Original Message-----
From: [EMAIL REMOVED] [EMAIL REMOVED] On Behalf Of Ben
Johnson (Neogic) F
Sent: 09 January 2007 18:33
To: Andy Macnaughton-Jones
Subject: RE: FN-FORUM: asp/sql - the correct way to get the ID of the
record you just added?



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

Access also supports "SELECT @@identity" (assuming Jet OLE DB v4+):
http://support.microsoft.com/default.aspx?scid=3Dkb%3b%5bLN%5d%3bQ233299

Ben

--
Ben Johnson, MD
Neogic Web Solutions

w | http://www.neogic.com
t | +44 (0)1242 808 262
e | [EMAIL REMOVED]



--
Freelancers, contractors earn more with Prosperity4 Call 0870 870 4414
or visit www.prosperity4.com and benefit from Inland Revenue approved
expenses today.

To advertise here: http://www.freelancers.net/advertising.html