Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 623
  • Last Modified:

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

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
btintermedia
Asked:
btintermedia
4 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
immediately after the insert statement run this , this will return the last inserted value
SELECT SCOPE_IDENTITY()
0
 
erikTsomikSystem Architect, CF programmer Commented:
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
 
Jones911Commented:
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
Jones911Commented:
CF8 only however.
0
 
btintermediaAuthor Commented:
thanks - your help was greatly appreciated
0
 
_agx_Commented:
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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now