How to get the primary key of a record added to the table with coldfusion

Many people use database tables with autonumber primary keys. These are columns (typically

named “id”) that the database will provide a value for by simply adding one to the last highest value.

So if the last record inserted had an ID value of 5, the next will be 6. (Note that this isn’t always true.

You can’t assume the next value will be one over the last highest value.) If you need to find out what

value was used for the primary key, ColdFusion provides a simple way to do that.

To use this feature, you first must provide the result attribute to your cfquery tag. This tells

ColdFusion to save information about the query to the variable named by the result attribute.

“foo” result=“result”>

insert into people(name,email)

values(“Paris Hilton”, “trash@celebs.com”)

After running this query a structure named result will be created. Most of the keys of this structure

are set, including the sql of the query, recordcount, and other values, however there is a special key

that will store the value of the primary key assigned to the insertion. Unfortunately, this key value

will vary depending on the database. For SQL Server, the value will be in the IDENTITYCOL key.

For Oracle, the value will be in the ROWID key. For Sybase, the value will be in the

SYB_IDENTITY key. For Informix, the value will be in the SERIAL_COL key. For MySQL, the

value will be in the GENERATED_KEY key.

Using the above query as an example and assuming MySQL, you can display the primary key value

like so:

The ID of the row I just inserted was #result.generated_key#.