Hello!
I'm not understanding what the SET NOCOUNT statements do in SQL Server...
I'm using the @@IDENTITY variable to duplicate record id's between tables.
In Example 1, the SELECT @@IDENTITY statement does not work. In Example 2,
where the 1st query is inside a SET NOCOUNT block, the SELECT @@IDENTITY
*does* work. Why?
Thx
************* Example 1 **************
<cftransaction>
<cfquery name="AddNewItem" datasource="#APPLICATION.DSN#" dbtype="oledb">
INSERT INTO TableName
(VAR1, VAR2, VAR3)
VALUES ('#FORM.VAR1#', '#FORM.VAR2#', '#FORM.VAR3#')
---> SELECT @@IDENTITY AS NewID
</cfquery>
<cfquery name="AddLinkedItem" datasource="#APPLICATION.DSN#"
dbtype="OLEDB">
INSERT INTO AnotherTable
(TableID, NewVar1, NewVar2)
VALUES(#AddNewItem.NewID#, '#FORM.NewVar1#','#FORM.NewVar2#')
</cfquery>
<cfset SESSION.TheNewRecordID = #AddNewItem.NewID#>
</cftransaction>
************* Example 2 **************
<cftransaction>
---> SET NOCOUNT ON
<cfquery name="AddNewItem" datasource="#APPLICATION.DSN#" dbtype="oledb">
INSERT INTO TableName
(VAR1, VAR2, VAR3)
VALUES ('#FORM.VAR1#', '#FORM.VAR2#', '#FORM.VAR3#')
SELECT @@IDENTITY AS NewID
---> SET NOCOUNT OFF
</cfquery>
<cfquery name="AddLinkedItem" datasource="#APPLICATION.DSN#"
dbtype="OLEDB">
INSERT INTO AnotherTable
(TableID, NewVar1, NewVar2)
VALUES(#AddNewItem.NewID#, '#FORM.NewVar1#','#FORM.NewVar2#')
</cfquery>
<cfset SESSION.TheNewRecordID = #AddNewItem.NewID#>
</cftransaction>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
