Untitled Document

Finding your true Identity

How to find the identity field value of a record you have just inserted

One of the most basic and crucial parts of application development with any language, ColdFusion included, is interaction with a database.

In most cases, you will have tables that have relationships, i.e. you will place the primary key, or identity field, into the field of another table.

Lets say for example you have two tables, Jobs and Tasks. After you insert a record into the Jobs table, you will want to insert a record in the Tasks table, including the id of the the job record. Your starting queries look like this:

<cfquery datasource="#request.dsn#" name="qInsertJob">
insert into jobs(display_name)
values('test job')

</cfquery>

<cfquery datasource="#request.dsn#" name="qInsertTask">
insert into tasks(display_name, job_id)
values('test task', [need job id here])

</cfquery>

Our goal is to get the identity value of the record that is inserted in the first query, so we can use it in the second.

NoCount, I have other things to do . . .

Whenever SQL server executes a command, it returns a value to the requesting party. So for example, when we insert the data in the first query, a value is return as "1 Row(s) effected." ColdFusion does not understand this response, and will not parse it properly, which is why you cannot successfully output the contents of an insert query.

Our first step is to suppress that output with the line "set nocount on;"

<cfquery datasource="#request.dsn#" name="qInsertJob">
set nocount on;
insert into jobs(display_name)
values('test job')

</cfquery>

Searching for your true @@identity . . .

Next we want to find out what the identity of the field we just inserted is, in this case, the job_id field.

Luckily, there is a built in SQL variable to return just that. The @@identity variable will hold the last generated identity value from the previous query. All we have to do now, is select it!

<cfquery datasource="#request.dsn#" name="qInsertJob">
set nocount on;
insert into jobs(display_name)
values('test job');
select @@identity as job_id;

</cfquery>

We first end the previous query with a semi-colon (;).

Next we simply tell the SQL server to "select @@identity" and name it "as job_id". You can of course name this "as" anything you wish.

Putting it all together

Now all that is left is to output the identity value we've found in our next query. This is done the same as any other query value!

<cfquery datasource="#request.dsn#" name="qInsertJob">
set nocount on;
insert into jobs(display_name)
values('test job');
select @@identity as job_id from jobs;

</cfquery>

<cfquery datasource="#request.dsn#" name="qInsertTask">
insert into tasks(display_name, job_id)
values('test task', #qInsertJob.job_id#)

</cfquery>

Now you have an efficient way of retrieving and storing your identity values!

Please feel free to contact me with questions / comments

Nate Nielsen
nnielsen@cfgod.com

About This Tutorial
Author: Nate Nielsen
Skill Level: Beginner 
 
 
 
Platforms Tested: CF3,CF4,CF5,CFMX,BlueDragon
Total Views: 87,047
Submission Date: September 10, 2004
Last Update Date: June 05, 2009
All Tutorials By This Autor: 9
Discuss This Tutorial
  • thank you,that was helpful

  • Hi there. Very helpful...if I could get it to work. It says that I am trying to use a complex variable as a simple one...

  • Thanks for ur helps, but one thing i wanna ask is that if a data is inserted within the (second query), will be the identity become wrong?

  • you might consider placing tags or around the two statements so that another insert doesnt sneak in between the two queries.

  • for ms access, you will have a problem having more than one SQL statement in a single cfquery, so you can split them up. try the following : insert into jobs (job_name) values ('test') select @@identity as job_id Hope this helps, take care. Nate Nielsen nnielsen@cfgod.com

  • I quess this might work with SQL server but I am not having much luck in access. Also, should those words be in uppercase?

  • I ran across a similar post but the first part of setting nocount wasn't included. Thanks for the tip. useful .. you bet.

Advertisement


Website Designed and Developed by Pablo Varando.