This is the way I have been doing it. I may have to keep doing it this way
if I can't get result.identityCol to work the way it is supposed to:
<cfquery datasource="someDsn" name="insertRecord">
INSERT INTO
someTable
(
firstName,
lastName
)
VALUES
(
<cfqueryparam cfsqltype="cf_sql_varchar" maxlength="20"
value="#arguments.firstName#">,
<cfqueryparam cfsqltype="cf_sql_varchar" maxlength="20"
value="#arguments.lastName#">
)
SELECT
Scope_Identity() AS ID
</cfquery>
<cfoutput>#tinsertRecord.ID#</cfoutput>
It's a little more efficient than doing it with two separate querys.
RR
On Sat, Jun 4, 2011 at 10:12 PM, Jenny Gavin-Wear <
jenn...@fasttrackonline.co.uk> wrote:
>
>
> http://cookbooks.adobe.com/post_Getting_the_ID_of_the_Last_Record_Inserted_I
> nto_a-16522.html
>
> I'm curious as to the best way of doing this in CF7.
>
> Currently I do another query to find the last inserted ID, but this seems a
> long winded way of doing things.
>
>
> Jenny Gavin-Wear
> Fast Track eCommerce
> http://www.ftol-ecommerce.com/
>
>
> >>-----Original Message-----
> >>From: Robert Rhodes [mailto:rrhode...@gmail.com]
> >>Sent: 05 June 2011 02:11
> >>To: cf-talk
> >>Subject: Re: cf9 query not returning identityCol
> >>
> >>
> >>
> >>Weird. Not working for me with cf9 and sql sever 2005.
> >>
> >>Identity Specification = Yes
> >>Is Identity = Yes
> >>Identity Increment 1
> >>Identity Seed 1
> >>
> >>On Sat, Jun 4, 2011 at 8:23 PM, Paul Kukiel <pkuk...@gmail.com> wrote:
> >>
> >>>
> >>> It does work and thats the way to do it. Are you 100% it's an auto inc
> >>> field?
> >>>
> >>>
> >>>
> >>http://cookbooks.adobe.com/post_Generated_key___Get_the_ID_of_the_
> >>inserted_record-16593.html
> >>>
> >>> Paul.
> >>>
> >>>
> >>> On Jun 5, 2011, at 9:35 AM, Robert Rhodes wrote:
> >>>
> >>> >
> >>> > That's for MYSQL. I am using SQL Server 2005. The docs say
> >>> "identityCol"
> >>> >
> >>> > But I will give it a try......
> >>> >
> >>> > Nope., didn't work. Thanks, though. :)
> >>> >
> >>> > On Sat, Jun 4, 2011 at 6:56 PM, Jason Durham
> >><jqdur...@gmail.com> wrote:
> >>> >
> >>> >>
> >>> >> It's theResult.generatedKey
> >>> >> On Jun 4, 2011 4:46 PM, "Robert Rhodes" <rrhode...@gmail.com>
> wrote:
> >>> >>>
> >>> >>> I am frustrated. :(
> >>> >>>
> >>> >>> In the cf9 docs, it says that cfquery is supposed to return, in the
> >>> >> result
> >>> >>> variable, the identity column value of the inserted record.
> >>However it
> >>> is
> >>> >>> not working from me.
> >>> >>>
> >>> >>> Would one of you kind souls point out my mistake?
> >>> >>>
> >>> >>> <cfquery datasource="someDsn" name="insertRecord"
> >>result="theResult">
> >>> >>> INSERT INTO
> >>> >>> someTable
> >>> >>> (
> >>> >>> firstName,
> >>> >>> lastName
> >>> >>> )
> >>> >>> VALUES
> >>> >>> (
> >>> >>> <cfqueryparam cfsqltype="cf_sql_varchar" maxlength="20"
> >>> >>> value="#arguments.firstName#">,
> >>> >>> <cfqueryparam cfsqltype="cf_sql_varchar" maxlength="20"
> >>> >>> value="#arguments.lastName#">
> >>> >>> )
> >>> >>> </cfquery>
> >>> >>>
> >>> >>> <cfoutput>#theResult.identityCol#</cfoutput>
> >>> >>>
> >>> >>> When I run this, identityCol is not there.
> >>> >>>
> >>> >>> I am indeed using an autonumbering identity column in the database.
> >>> >>>
> >>> >>> Yes, I know I can just add a select to this query to get at
> >>it, but I
> >>> >> really
> >>> >>> want this to work, dammit!
> >>> >>>
> >>> >>>
> >>> >>>
> >>> >>
> >>> >>
> >>> >
> >>> >
> >>>
> >>>
> >>
> >>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:345083
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
