There is an issue with @@identity in MS-SQL. From MS-SQL help:
@@IDENTITY returns the last identity value generated in "any table" in the
current session. (Quotations mine).
If you have any INSERT triggers on your table that perform INSERTS on other
tables, @@identity will return the identity of the last insert in your
triggers, rather than the one you want.
MS-SQL 2000 has a new function to deal with the issue called SCOPE_IDENTITY,
which "Returns the last IDENTITY value inserted into an IDENTITY column in
the same scope."
-----Original Message-----
From: Daniel Lancelot [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, March 20, 2001 6:06 AM
To: CF-Talk
Subject: RE: @@identity
You need to use SET NOCOUNT ON / SET NOCOUNT OFF round your query to be able
to access the info in cfquery
e.g.
<cfquery datasource="#request.dsn#"
name="qAddEntry">
SET NOCOUNT ON
INSERT INTO tblUsers(name, jobtitle,
company, address, town, postcode, countryID, email, website, password, hint,
maillist, brieflist, foundsiteID)
VALUES ('#form.name#', '#form.jobtitle#',
'#form.company#', '#form.address#',
'#form.town#','#form.postcode#',#form.countryID#, '#form.email#',
'#form.website#', '#form.password#', '#form.hint#', #maillist#, #brieflist#,
#form.foundsiteID#)
SELECT newID = @@IDENTITY
SET NOCOUNT OFF
</cfquery> -->
<cfoutput>#qAddEntry.newID#</cfoutput>
this works.
HTH
Dan
-----Original Message-----
From: Joshua Tipton [mailto:[EMAIL PROTECTED]]
Sent: 20 March 2001 05:07
To: CF-Talk
Subject: RE: @@identity
When I do that CF says not a valid query specified.
-----Original Message-----
From: CF [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, March 20, 2001 12:04 AM
To: CF-Talk
Subject: Re: @@identity
You need to give your query a name. Instead <cfquery name="", use <cfquery
name="queryName". Then you just access that variable as:
queryName.identity
Also, I don't know it it matters to your database or not, but you might want
to put a ; at the end of your first query to differentiate it from the next
...... just to avoid confusion.
Todd Ashworth
Web Application Developer
Network Administrator
Saber Corporation
314 Oakland Ave.
Rock Hill, SC 29730
(803) 327-0137 [111] (p)
(803) 328-2868 (f)
----- Original Message -----
From: "Joshua Tipton" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Monday, March 19, 2001 11:32 PM
Subject: @@identity
> I know how to use @@identity to select the last inserted record right
under
> an insert but how do I display that id of that record?
>
> <cfquery name="" datasource="gunledger" dbtype="ODBC">
> INSERT INTO dbo.distrib(compname, phonenum, address, address2, city,
state,
> zip, fflid, expires)
> VALUES('#compname#', '#phonenum#', '#address#', '#address2#', '#city#',
> '#state#', '#zip#', '#fflid#', '#expires#')
> select @@identity as 'identity'
> </cfquery>
>
> How do I output the identity????
>
> Josh
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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
