Use SCOPE_IDENTITY(). @@Identity returns the id for the last record inserted. If an another insert into that table occurs between your insert and the select @@identity (2 different people making inserts into the db for example) you will get the id for the other record that was inserted.
Scope identity only pulls the last record for the current scope. Sort of like putting something in a transaction. It all sticks together. (however putting a query in a transaction with @@identity will not solve the problem above). As for the SET NOCOUNT ON and SET NOCOUNT OFF, they have no effect on these. The only thing that does is prevent extra network traffic by stopping the extra reporting of how many rows were affected for each piece of the query. Of course I'm assuming that this is MSSQL. Steve -----Original Message----- From: Michael Dinowitz [mailto:mdino...@houseoffusion.com] Sent: Thursday, January 28, 2010 9:10 PM To: cf-talk Subject: Re: CF9 cfquery not giving same insert results as CF7 I've found a query that used Select @@Identity as NewAgentID without having a Set NoCount on or off. This query still worked and did not have to be altered at all. That brings up the question if NoCount is needed and if it is needed, do both the @@identity and the SCOPE_IDENTITY() methods need it? What is the difference between the two methods? Select NewAgentID = SCOPE_IDENTITY() Select @@Identity as NewAgentID Thanks -- Michael Dinowitz On Wed, Jan 27, 2010 at 8:19 PM, Brook Davies <cft...@logiforms.com> wrote: > > I am curious, does the query use the NOCOUNT and @@identity SQL commands?? > > <cfquery name="addAgent"> > SET NOCOUNT ON > insert into users_printprofiles > ( > ) > values( > ) > select newid=@@identity > SET NOCOUNT OFF > </cfquery> > > Brook > > > -----Original Message----- > From: Michael Dinowitz [mailto:mdino...@houseoffusion.com] > Sent: January-27-10 1:26 PM > To: cf-talk > Subject: Re: CF9 cfquery not giving same insert results as CF7 > > > OK, so it turns out that this is a known issue that came into effect between > CF 7 and CF 8. I can do one of three things here. > > 1. replace the name attribute with the result attribute: > <cfquery name="AddAgent"> becomes <cfquery result="AddAgent"> > > 2. add a result attribute of the same name to the cfquery > <cfquery name="AddAgent"> becomes <cfquery name="AddAgent" > result="AddAgent"> > > 3. use a 'generic' result value to the cfquery > <cfquery name="AddAgent"> becomes <cfquery name="AddAgent" > result="QueryResult"> > > The problem that I see is that I don't know everywhere that the query > results will be used. > Is anything returned to the 'name' variable of the query? If not, then will > removing it cause some other unforeseen effect? (experiments so far say no) > Will the use of both a name and a result conflict? (experiments so far say > no) > How will all this interact with Fusebox? Is the results of a query in an > act_ page supposed to be 'exposed' to the dsp_ page? > > If anyone has a clue or two on which way I should go on this, please let me > know. I've got to fix this in almost 700 places (the client site is a royal > mess of backed up and copied code) and a single, standard approach is what I > need. Luckily, the RegEx to find all of the locations took 60 seconds to > write. Literally. > > The hard is fast, the easy is slow. How standard for me. :) > > -- > Michael > >>I just ran into this and while it's new to me, I'm sure it's old news >>to others here. I'm looking at someones code from CF 7 where they have >>a cfquery inserting a record. The cfquery tag only has a name and a >>datasource. Immediately after the tag, there is a cfset that makes use >>of the query's name and the name of the identity field of the table >>that the data was inserted into. >> >><cfquery name="testquery" datasource="test"> >>insert ... >></cfquery> >><cfset newid = testquery.identityfield> >> >>In CF 7, the newid would be the id of the newly inserted record. In CF >>9 all I get is an error. If I use a result attribute rather than a >>name attribute, I get a structure with the identity result buried in >>it. >> >>So here's the question. If it worked in 7 and not in 9, is there a >>simple fix to make it work like it did in 7 again? I'd rather not have >>to recode a whole slew of insert statements. >> >>Thanks >> >>-- >>Michael > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:330254 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
