Get the Primary ID after a SQL insert using ColdFusion

When you're inserting information in to a database table, it can be very helpful to immediately know what the number in the primary key field, also known as the identity (ID) field, is so you can set up links to editing pages, use it in other database inserts, etc.

Using the code below on MS SQL Server you can retrieve the ID field of the record you have just inserted:

<CFQUERY NAME="DBInsert" DATASOURCE="#Datasource#">
SET NOCOUNT ON

INSERT INTO Address (name,address1,addres2,address3)
VALUES ('#form.name#','#form.address1#','#form.address2#','#form.address3#')
SELECT @@identity AS LatestID

SET NOCOUNT OFF
</CFQUERY>

<CFOUTPUT>#DBInsert.LatestID#</CFOUTPUT>

More code articles

Useful books
Cover of Mastering ColdFusion
Mastering ColdFusion MX
Amazon UK | USA
Cover of Programming ColdFusion MX
Programming ColdFusion MX
Amazon UK | USA
Cover of ColdFusion X Web Application Construction Kit
ColdFusion X Web Application Construction Kit
Amazon UK | USA

Paul Silver. November 2003


Silver Web Services Ltd

© 2017 Paul Silver