When inserting a new row to a mySQL database table using <cfquery>, it is often necessary to find the ID of the new record so you can refer to it in subsequent queries or other code further down the page.

There are a few ways to do this, but I like this one best:

view plain print about
1<cfquery name="insertRecord" datasource="#request.dsn#" result="insertResult">
2INSERT INTO my_table
3(
4column_1,
5column_2,
6etc
7)
8VALUES
9(
10'#value1#',
11'#value2#',
12'etc'
13)
14</cfquery>
15
16<cfset newID = insertResult.generated_Key>

Notice the "result" attribute of the cfquery tag, which will accept any variable name you want to use. Then, immediately following the query code, we simple get the 'generated_Key' node of the result value, and voila - we have the ID of the new record, ready for whatever is needed next.

(Note: This example is for mySQL - not sure how this plays out in MS SQL or other DB engines... your mileage may vary.)