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
