Solved

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

Posted on 2009-07-09
6
612 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
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 
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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

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…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

809 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