What is my ID for the record I just inserted?

A question I get quite often from visitors and members of EasyCFM.COM is "if I insert a record, is there a way I can get the ID of the record I just inserted, without having to connect to the database again?"

The answer of course is yes ;) first let me explain how your database structure would look for this example (keep in mind that this is a small example and your table will be different, all you need for this example to work properly is an IDENTITY FIELD in your SQL server. If you need help setting up an identity field, please post a request to the forums [I will be writing a tutorial on this at a later time and will update this page with the link at a latter time... for now please use the forums ;] ...) Now, lets look at the database itself,

<cfquery name="qGetUser" datasource="MyDSN">
   INSERT INTO users (firstName, lastName, email )
   VALUES ( '#FORM.LastName#', '#FORM.LastName#', '#FORM.email#' );

   select thisID = @@identity
</cfquery>

<cfoutput>
The ID of the item just inserted is: #qGetUser.thisID#
</cfoutput>

That more or less does it :) Let me explain what it is you saw and what it does....

First you do an insert (as you would any other insert) with the difference that you add a ";" at the end of it. (Notice it in bold). The next thing you did was SELECT thisID = @@identity.... this works similar to a regular SELECT statement... but it basically just returns the ID of the item just inserted to the database!

Questions? Comments? Let me know....