This should insert your record and return the new primary key as 'newid' <cfquery NAME='myqry'> Insert into address (city) Values (<cfqueryparam value="#City#" cfsqltype="cf_sql_integer" />); Select LAST_INSERT_ID() as newid; </cfquery>
Primary keys that you control would probably be the better solution though. CF generated UUID's sound like a great solution. <cfset mynewid = createuuid()> <cfquery...> Insert into address (id, city) Values (#mynewid#, #city#) </cfquery> So you always know what the new records ID is... It not only keeps you from having to worry about the ids getting crossed when you use max() and 2 or more people insert something at the same time. (or worrying about transactions to stop that from happening) AND... if the data gets moved to another datasource down the road... all the relations can be maintained since the IDs aren't autoincrements. That alone is enough to use your own defined IDs over autoincrementing ones. ..:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:. Bobby Hartsfield http://acoderslife.com -----Original Message----- From: Baz [mailto:[EMAIL PROTECTED] Sent: Sunday, January 08, 2006 7:24 PM To: CF-Talk Subject: Get last inserted ID Is this the best way to do this: <cftransaction> <cfquery name="InsertAddress" datasource="#DSN#"> INSERT INTO Address (City) VALUES (<cfqueryparam value="#City#" cfsqltype="cf_sql_integer" />) </cfquery> <cfquery name="getInsertedID" datasource="#DSN#"> SELECT MAX(AddressID) as MaxID FROM Address </cfquery> </cftransaction> I'm using MySQL. Cheers, Baz ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:228802 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
