I'm not sure about scope_identity but I believe that if there are any other
inserts then the identity value changes anyway and once you complete
execution of a query The @@Identity is reset. @@IDENTITY works well if it is
in a stored procedure and the Stored procedure is called by cfstoredproc
I.E.
Inside the Sproc you have the following
Create Stored procedure example_sp
@p1 INT,
@p2 int,
AS
Insert
into Tablename (
Field1,
Field2
)
Values
@p1,
@p2
Select @@identity as newid
The calling CF Code would be
<cftry>
<cfstoredproc
procedure="example_sp"
datasource="#application.DSN#"
returncode="yes">
<cfprocparam type="In"
cfsqltype="CF_SQL_INTEGER"
dbvarname="@p1"
value="100"
null="no" >
<cfprocparam type="In"
cfsqltype="CF_SQL_INTEGER"
dbvarname="@p2"
value="200"
null="no" >
<procresult = "exampls_sp"
</cfstoredproc>
<cfcatch type="database">
<cfinclude template="../_global/qryerr.cfm">
</cfcatch>
</cftry>
I haven't tested the above code but I have no problems with the @@identity
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Joel Nath
Sent: Monday, 22 March 2004 4:45 PM
To: CFAussie Mailing List
Subject: [cfaussie] RE: New Record - SELECT @@IDENTITY
Hi,
its just a simple test....
if i run
<cftransaction>
<cfquery name="qInsert" datasource="XXXX" username="XXXX" password="XXXX">
INSERT INTO Test(title)
VALUES(<cfqueryparam value="#title#" cfsqltype="CF_SQL_VARCHAR">)
</cfquery>
<!--- START: Get ID of record just inserted --->
<cfquery name="qGetLastID" datasource="XXXX" username="XXXXX"
password="XXXX"> SELECT SCOPE_IDENTITY() as LastID </cfquery>
</cftransaction>
and i dump qGetLastID.. LastID is Null
its the same result if i have <cftransaction> or not...
this works with or without cftransaction...(no cfquery param!!)
<cftransaction>
<cfquery name="qInsert" datasource="XXXX" username="XXXX"
password="XXXX">
INSERT INTO Test(title)
VALUES('#title#')
</cfquery>
<!--- START: Get ID of record just inserted --->
<cfquery name="qGetLastID" datasource="XXXX" username="XXXX"
password="XXXX">
SELECT SCOPE_IDENTITY() as LastID
</cfquery>
</cftransaction>
cheers
joel
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Behalf Of Nick Gorst
Sent: Monday, 22 March 2004 4:36
To: CFAussie Mailing List
Subject: [cfaussie] RE: New Record - SELECT @@IDENTITY
Can we see the query?
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Joel Nath
Sent: Monday, 22 March 2004 3:28 PM
To: CFAussie Mailing List
Subject: [cfaussie] RE: New Record - SELECT @@IDENTITY
SQL 2000
the ID returned seem random but higher then expected ID value when using
@@IDENTITY
but ID is null when using SCOPE_IDENTITY()....
the ID field is an Auto Identity field.
cheers
joel
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Behalf Of Scott Thornton
Sent: Monday, 22 March 2004 4:13
To: CFAussie Mailing List
Subject: [cfaussie] RE: New Record - SELECT @@IDENTITY
nope, no thoughts
why messed up? are they close? are they way off? are they the same
datatype? what database engine?
>>> [EMAIL PROTECTED] 22/03/2004 3:24:07 pm >>>
slightly off topic...
but if you use cfquery param's with inserts and use scope or @@
identities...
the id's you get are messed up..:(((((((((((((((
without the cfquery params.. it seems fine...
any thoughts on this????
cheers
joel
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Behalf Of Scott Thornton
Sent: Thursday, 18 March 2004 11:56
To: CFAussie Mailing List
Subject: [cfaussie] RE: New Record - SELECT @@IDENTITY
Hi,
If you have a trigger on your table, that inserts into another table, and if
that table as an identity field, you will get THAT identity instead of the
one you expect.
SCOPE_IDENTITY() will return the correct identity I believe.
Scott Thornton, Programmer
Application Development
Information Services and Telecommunications
Hunter Area Health Service
Phone +61 2 49214193
Fax +61 2 49214191
[EMAIL PROTECTED]
>>> [EMAIL PROTECTED] 18/03/2004 10:56:46 am >>>
<cftransaction>
<cfquery name="insertBlah" datasource etc>
INSERT INTO tblBlah
(name, address)
VALUES
('Bill', 'Somewhere Street')
</cfquery>
<cfquery name="getIdentity" datasource etc>
SELECT @@Identity as blahID
</cfquery>
</cftransaction>
Then you use it in your code:
getIdentity.blahID
etc.
HTH
- J
> -----Original Message-----
> From: Yorke Hinds [mailto:[EMAIL PROTECTED]
> Sent: Thursday, 18 March 2004 10:39 AM
> To: CFAussie Mailing List
> Subject: [cfaussie] New Record - SELECT @@IDENTITY
>
>
> Not having used the SELECT @@IDENTITY regularly,
> I was wonder if anyone can shed some light on this method
> of retrieve the Record ID on INSERT.
> DB: Ms SQLServer 2000
>
> All comments & insight welcome!
>
>
>
> ---
> You are currently subscribed to cfaussie as:
> [EMAIL PROTECTED]
> To unsubscribe send a blank email to
> [EMAIL PROTECTED]
>
> MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia
> http://www.mxdu.com/ + 24-25 February, 2004
>
---
You are currently subscribed to cfaussie as:
[EMAIL PROTECTED]
To unsubscribe send a blank email to [EMAIL PROTECTED]
MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia
http://www.mxdu.com/ + 24-25 February, 2004
---
You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To
unsubscribe send a blank email to [EMAIL PROTECTED]
MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia
http://www.mxdu.com/ + 24-25 February, 2004
---
You are currently subscribed to cfaussie as:
[EMAIL PROTECTED]
To unsubscribe send a blank email to [EMAIL PROTECTED]
MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia
http://www.mxdu.com/ + 24-25 February, 2004
---
You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To
unsubscribe send a blank email to [EMAIL PROTECTED]
MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia
http://www.mxdu.com/ + 24-25 February, 2004
---
You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To
unsubscribe send a blank email to [EMAIL PROTECTED]
MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia
http://www.mxdu.com/ + 24-25 February, 2004
---
You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To
unsubscribe send a blank email to [EMAIL PROTECTED]
MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia
http://www.mxdu.com/ + 24-25 February, 2004
---
You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To
unsubscribe send a blank email to [EMAIL PROTECTED]
MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia
http://www.mxdu.com/ + 24-25 February, 2004
---
You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
To unsubscribe send a blank email to [EMAIL PROTECTED]
MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia
http://www.mxdu.com/ + 24-25 February, 2004
