Hi, I am not saying they is a problem with @@IDENTITY or Scope_Identity but they is a problem if you use <cfqueryparam> and Scope_Identity() or <cfqueryparam> and @@IDENTITY together
has anyone come across this... cheers joel -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Nick Gorst Sent: Monday, 22 March 2004 6:07 To: CFAussie Mailing List Subject: [cfaussie] RE: New Record - SELECT @@IDENTITY 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 --- 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
