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
