Its purpose in the CFQUERY is to turn off the SQLServer's responses which
would halt the query. You need to do that if you are going to have more
than one SQL operation in one CFQUERY.
Incidentally, be careful using @@identity to find out the ID of the insert
you just made. IN the vast majority of cases there's no problem, but
occasionally it might give you the wrong answer, specially if there is a lot
of activity going on or if you are using triggers.
@@identity returns you the ID of the last insert made into the database.
Normally that's the one you just did. But if two people run the same pages
almost simultaneously, it might return you the ID of the other insert. Or
if you use triggers to insert the data into two tables, it'll return you the
ID of the last insert, which ever that was.
Another way to find out what you want, and make sure it's specifically on
the table you're interested in, is to use Ident_current('tablename'). So
your CFQUERY would look like this:
<cfquery name="insert" datasource="#application.dsn#">
SET NOCOUNT ON
Insert into MyTable (field1, field2, field3)
VALUES
('#field1#','#field2#','#field3#')
SELECT Ident_current('MyTable) as InsertID
SET NOCOUNT OFF
</cfquery>
<cfoutput><p>The Inserted ID is: #insert.InsertID#</p></cfoutput>
You can see the difference between @@IDENTITY, Ident_Current and
Scope_Identity (which all do similar but not identical things) explained if
you use Books Online and search for Ident_Current.
Hope this helps
Cheers
Mike Kear
Windsor, NSW, Australia
Macromedia Certified Advanced ColdFusion Developer
http://afpwebworks.com
..com, .net, .org etc domains start at A$20 / year
Full scale ColdfusionMX hosting from $15/month.
-----Original Message-----
From: Johnny Le [mailto:[EMAIL PROTECTED]
Sent: Saturday, 30 April 2005 1:37 AM
To: SQL
Subject: RE: set nocount on?
Well, the reason I asked is because this person use it in an insert cfquery
statement in ColdFusion. It is not an exec store procedures. Cfquery does
not return how many rows are being affected anyway. Is there any reason why
you should use it when you are not using store procedures?
Johnny
>http://www.google.com/search?hl=en&q=set+nocount+on&btnG=Google+Search
>
>-----Original Message-----
>From: Johnny Le [mailto:[EMAIL PROTECTED]
>Sent: Friday, April 29, 2005 8:31 AM
>To: SQL
>Subject: set nocount on?
>
>
>Hi,
>
>What does set nocount on and off do? I saw an insert statement that starts
>with SET NOCOUNT ON and end with
>SELECT @@identity AS userID
>SET NOCOUNT OFF
>
>I know what select @@identity does, but what is set nocount on and off for?
>
>Johnny
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Discover CFTicket - The leading ColdFusion Help Desk and Trouble
Ticket application
http://www.houseoffusion.com/banners/view.cfm?bannerid=48
Message: http://www.houseoffusion.com/lists.cfm/link=i:6:2255
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/6
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:6
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.6
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
