Server Time:
Monday May 12 2008 06:39 AM  		 Your Time:
  
HostMySite.Com is sponsoring this tutorial, please visit their site today!
This tutorial is sponsored by HostMySite.Com - ColdFusion Hosting

What is the ID for the record I just inserted?
 by: Pablo Varando 		Email this tutorial to a friend Display Printer Friendly Format
[Download in PDF Format] [Download in FlashPaper Format]

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,

FIELD TYPE FIELD TYPE NULL IDENTITY
MEMBER_ID INT NO 1
FIRSTNAME VARCHAR(50) YES  
LASTNAME VARCHAR(50) YES  
EMAIL varchar(250) YES  

<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....


Date added: Mon. August 11, 2003
Posted by: Pablo Varando | Views: 20634 | Tested Platforms: CF4,CF5,CFMX | Difficulty: Intermediate
 Categories Listed: Best Practices Other Reusing Code

HostMySite.Com is sponsoring this tutorial, please visit their site today!
This tutorial is sponsored by HostMySite.Com - ColdFusion Hosting

This author's other tutorials:
Delete files and folders in a specified path!
This tutorial will demonstrate how you can delete all files and sub-folders in a specified folder using ColdFusion and Windows! - Date added: Wed. September 7, 2005
 Dynamic Last Date Modified?
This tutorial will demonstrate how to display the date a web page was last modified to your visitors dynamically. - Date added: Mon. April 12, 2004
 Correct Content (document) serving!
This tutorial will demonstrate how to correctly serve documents via ColdFusion and allow you to correctly name the download as you see fit! - Date added: Tue. February 10, 2004
 Creating your very own RSS XML Feeds with ColdFusion MX!
Have you ever wanted to create your very own RSS XML News Feeds? This tutorial will show you how to create an RSS feed that will allow you to syndicate your web site and allow the world to easily use your data! - Date added: Thu. January 15, 2004
 Processing XML/RSS feeds with ColdFusion MX
This tutorial will show you how to parse XML files (RSS Feeds) with ColdFusion MX and it uses an EasyCFM.COM Feed for example [Feed: 5 Most Viewed Tutorials]. It shows you how to call it via CFHTTP all the way to parse and display your records! - Date added: Sat. December 27, 2003
Additional Tutorials:
· Changing the form submission page on the fly!
· Creating a file content crawler with ColdFusion....
· Delete Records From Your Database With ColdFusion!
· Do you want to remember your members?
· Get A Folder Size Using ColdFusion and FSO...
· Preventing People From Leeching Your Images!
· Combining two queries into one..
· CaSe SensitiVe password logins!
· Creating an ODBC Connection within ColdFusion MX Server...
· Print your web pages on the fly!
· Using <CFPOP> and creating an email client for POP3 Email Reading!
· Using CFRegistry to Add Your IP To CF Debug IP List!
· Reading your IIS Log Files with ColdFusion!
· Automatically Adding Smiles To Your Messages!
· Using Arrays in ColdFusion To Properly Display Data....
· Implementing FORM Error Checking On Your Pages!
· Inserting FORM data into multiple database tables!
· Creating, Altering and Deleting database tables with ColdFusion.
· Sending multiple attachments with CFMAIL!
· ColdFusion and .INI Files!
· Clearing your session variables!
· Using PayPal's IPN with ColdFusion!
· Alternating Row Colors!
· Previous / Next n Records
· Using Query String Values....
· A quick intro into the world of Custom Tags!
· A brief demonstration of Fusebox 2.0
· Creating a Newsletter System....
· Count Active Users On Your Site.
· User Defined Functions....
· Creating a user athentication (Login) area.
· DSNLess Coldfusion?
· A Simple Contact Us Page….
· Having Your Database Do The Work… not ColdFusion!
· Retrieving Records From a Database..
· Inserting data into a database
Please rate this tutorial:
5 Stars 4 Stars 3 Stars 2 Stars 1 Stars
Comments on this tutorial
Read previous comments on this particular tutorial
Question
Does this work when you use <CFINSERT...> or <CFUPDATE...>?
thanks!!!


man I love this site!
Posted by: Dano
Posted on: 07/02/2004 08:59 AM
Didn't work
I tried this with MS access .. no luck. Looked into the SQL and it seems that this is an only SQL server trick. Ahh man. Back to Select MAX(ID)

MC
Posted by: Mike
Posted on: 11/02/2004 09:56 AM
Yup
@@identity is indeed MS SQL Server specific.
Posted by: timo
Posted on: 11/16/2004 05:19 PM
mysql
is there any similar variant for this using mysql?
Posted by: tim
Posted on: 12/03/2004 04:33 PM
MySQL variant
mysql_insert_id() is the MySQL version of @@identity.
Posted by: Wayne
Posted on: 12/06/2004 09:44 AM
setting identity
how can i set a particular field into identity field using mySQlfront. I used mySQL server.
Posted by: dick
Posted on: 02/03/2005 09:31 AM
IT DOES work in access.
It works in newer versions of access. I've used it many many times. you need to use 2 cfqueries, and make sure you cftry and cftransaction them together, so the id will allways be correct.

there is also a method like this in mysql. SELECT LAST_INSERT_ID() AS id
Posted by: kevin
Posted on: 02/26/2005 04:26 PM
Need help setting identity
Hi there. I cant get this to work. Please instruct on setting up the identity. Thanks!
Posted by: MichaelBlues
Posted on: 02/27/2005 05:16 PM
access, mysql
If you are using msAccess (or mysql) you will need to NOT use a ';' and break the query into two seperate queries, which then leads to issues with 'what if two people add a record at the same time' so locking and good checking needs to be used also
Posted by: kevin
Posted on: 04/21/2005 04:34 PM
MySQL
It seems not to work for MySQL. Stick to Select max(ID)
Posted by: iqbal
Posted on: 08/01/2005 01:23 AM
MySQL
You should select from something unique to that record, max id has nothing to do with a specific record.

The best way is to select exactly what you inserted. If there is the possibility of identical rows, add a UUID to each row.
the only surefire way to make this multi thread safe is to select exactly what was inserted with a UUID also.
Posted by: Kevin
Posted on: 11/24/2006 07:56 PM
WHy NOT?
If you are unable to use this method why not just use two separate queries. You still have the data from the form just inserted, you can still use it.

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

<cfquery name="getID" datasource="MyDSN">
SELECT member_id FROM users WHERE (firstName = #Form.firstName ) AND ( lastName = '#Form.LastName');
</cfquery>
Posted by: holmes
Posted on: 05/18/2007 01:08 PM
Post a new comment on this tutorial
post a new comment on this particular tutorial
Your Name:
Your Email:
Comment Title:
Comments:
Key Phrase:
 
Skyscrapper Banner Advertisement
ProWorkFlow.Com

You are 1 of 647 active sessions! | Privacy | Company
Copyright © 2002 EasyCFM.Com, LLC. (Easy ColdFusion Tutorials) All Rights Reserved
All other trademarks and copyrights are the property of their respective holders.
ColdFusion Hosting ColdFusion Hosting
ADD TO:
Blink
Del.icio.us
Digg
Furl
Google
Simpy
Spurl
Y! MyWeb