Solved

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

Posted on 2009-07-09
6
615 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Lease-to-own eliminates the expenditure of hardware replacement and allows you to pay off the server over time. Usually, this is much cheaper than leasing servers. Think of lease-to-own as credit without interest.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.

734 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