Solved

capture id of index (identity) column after insert in cold fusion insert query

Posted on 2009-07-09
6
614 Views
Last Modified: 2013-12-24
I have a query that I am executing:

<cftransaction isolation="serializable">

<cfquery name="enter_ms_values" datasource="my_DSN" result="music_insert">
INSERT INTO Music (church_ID,Music_Date,Music_Time,Performance_Type,Music_Location,Music_Desc,calendar_ID,user_id,created_by,created_date,created_time)
VALUES ('#church_id#', '#new_music_date#', '#new_music_time#','#new_performance_type#','#new_music_location#','#new_music_desc#','#calendar_id#','#posted_by#','#posted_by#','#dateformat(now(), 'mm/dd/yyyy')#','#TimeFormat(Now(),"hh:mm tt")#')
</cfquery>



      <cfoutput>
      <!--- this is where we capture the number of the identity/key column for music --->
      <CFPARAM NAME="new_music_id" DEFAULT="">
      <cfset new_music_id = "#??????????????#">      
       
       
       the number of my music even is "#new_music_id#"<br />
   
               
          </cfoutput>



I need the value that goes where I have put "????????"  - or - a  better way of getting it done, besides this way.




I need to capture the id of the key/identity column (music_id) for the table "music". When the information is added to the database, it represents the addition of a new music event - but I have to have the music event's Id  (music_id)  because I have to add other values to another table (see my second question) and I have to add the music_id to that table so that those values are associated with this music event by that number.

I am also a but fuzzy on 'table locking' because this table may be getting hit with multiple addition queries - and it is important to make sure that the right music_id is captured.
0
Comment
Question by:btintermedia
6 Comments
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 40 total points
ID: 24818373
immediately after the insert statement run this , this will return the last inserted value
SELECT SCOPE_IDENTITY()
0
 
LVL 19

Assisted Solution

by:erikTsomik
erikTsomik earned 90 total points
ID: 24818408
what you need to do is this . After the insert query add this query and store the identity column

<cfquery name="MusicID" datasource="XXXXXXX">
                                    SELECT @@Identity AS MusicID
                              </cfquery>
            
            <cfset newMusicID= MusicID.MusicID>
The you can set the value

  <cfset new_music_id = "#newMusicID#">      
0
 
LVL 19

Accepted Solution

by:
Jones911 earned 120 total points
ID: 24818508
Both of those are not good the best way to handle this the best way is to use the inbuilt functionality of Coldfusion:

http://www.forta.com/blog/index.cfm/2007/7/6/ColdFusion-8-Can-Return-Identity-Values
<cfquery name="enter_ms_values" datasource="my_DSN" result="music_insert">
INSERT INTO Music (church_ID,Music_Date,Music_Time,Performance_Type,Music_Location,Music_Desc,calendar_ID,user_id,created_by,created_date,created_time)
VALUES ('#church_id#', '#new_music_date#', '#new_music_time#','#new_performance_type#','#new_music_location#','#new_music_desc#','#calendar_id#','#posted_by#','#posted_by#','#dateformat(now(), 'mm/dd/yyyy')#','#TimeFormat(Now(),"hh:mm tt")#')
</cfquery>
 
<cfoutput> Total records: #music_insert.IDENTITYCOL#</cfoutput>

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 19

Assisted Solution

by:Jones911
Jones911 earned 120 total points
ID: 24818513
CF8 only however.
0
 

Author Closing Comment

by:btintermedia
ID: 31601837
thanks - your help was greatly appreciated
0
 
LVL 52

Expert Comment

by:_agx_
ID: 24818737
Jones911's suggestion is the most correct for CF8.

> SELECT @@Identity

In terms of sql only, aneeshattingal is correct.   SCOPE_IDENTITY() is generally preferred over @@Identity.  
0

Featured Post

Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question