Actually if you are using MSSQL 2000 or 2005 or up, you should be using VALUES(); SELECT SCOPE_IDENTITY() AS id
-----Original Message----- From: Craig Dudley [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 19, 2008 11:45 AM To: CF-Talk Subject: RE: CFC Best Practices Rob, if you are using MSSQL why don't you use @@IDENTITY? e.g. this crappy example... <cfquery name="insertLocation" datasource="#mydsn#"> INSERT INTO tblLocations ( address1, address2, address3, town ) VALUES ( <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#trim(form.address1)#">, <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#trim(form.address2)#">, <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#trim(form.address3)#">, <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#trim(form.town)#">, ) SELECT @@IDENTITY AS locationID </cfquery> #insertLocation.locationID# is then your last inserted item. (assuming tblLocations has an identity column that is) Cheers, Craig. -----Original Message----- From: RobG [mailto:[EMAIL PROTECTED] Sent: 19 August 2008 16:26 To: CF-Talk Subject: Re: CFC Best Practices James Holmes wrote: > You can generally avoid the problem with a cftransaction tag if you're > doing the MAX(id) thing, but the other solutions are better > (especially the new CF8 goodies which, for example, are a life saver > for Oracle). I've tried using the result.identitycol trick with CF8 and MSSQL and for me, it doesn't work. I just get an error. So what I've stuck to is this... Before the first insert, I do <cfset now = Now()> Then in the insert, for my date_added value, I use #now#. Then after that, when I do my select MAX(id), I also add where date_added = '#now#' AND I also wrap the whole thing in a cftransaction... Rob ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311268 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4