Solved
capture id of index (identity) column after insert in cold fusion insert query
Posted on 2009-07-09
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.