For MSSQL there's a native function to give you the id. In a CFQuery tag it works like this:
<cfquery name="Insert" datasource="#variables.dsn#" > SET NOCOUNT ON INSERT into Tablename ( email, firstname, lastname ) VALUES ( <cfqueryparam value="#email#" cfsqltype="cf_sql_varchar"/>, <cfqueryparam value="#firstname#" cfsqltype="cf_sql_varchar"/>, <cfqueryparam value="#lastname#" cfsqltype="cf_sql_varchar"/> ) SELECT Ident_Current('Tablename') as RecordID SET NOCOUNT OFF </cfquery> The NOCOUNT thing prevents MSSQL returning messages that might cause the query to abort. The Ident_Current('tablename') gives you the ident of the latest insert in the current tablename. Because it's all done in a single CFQUERY, I do believe the table is automatically locked This is only a MSSQL solution but i've never had a problem with getting the ID this way, even on my highest volume sites. Cheers Mike Kear Windsor, NSW, Australia Adobe Certified Advanced ColdFusion Developer AFP Webworks http://afpwebworks.com ColdFusion, PHP, ASP, ASP.NET hosting from AUD$15/month On Wed, Aug 20, 2008 at 1:26 AM, RobG <[EMAIL PROTECTED]> wrote: > > 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:311287 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4