[thelist] @@IDENTITY w/SQL 7 - ColdFusion query

databarn databarn at airmail.net
Fri Aug 10 20:51:55 CDT 2001


Sorry, I bypassed this initially, as 'twas expected that one or more of several folk better qualified to answer would.

This works in ASP 2.0 and ASP 3.0, i.e. IIS 4.0 and IIS 5.0.
Set cnctAprv = Server.CreateObject("ADODB.Connection")
cnctAprv.Open AprvCnct
' *** SQL statement
sSql="insert into AutoNum (Created) VALUES ('" & dtmCreated & "'); select @@identity 'newid'"
' *** run SQL
set rsERD=cnctAprv.Execute(sSql)
' *** assign 2nd recordset to object/variable
' *** read returned value into variable
Note that there is a semicolon (;) between the insert statement and the select statement.  SQL Server then returns two "recordsets", the first being success/failure of the insert, the second being the identity field if the insert was successful.  As far as I know, this will work fine as long as there are no triggers (and maybe stored procedures) involved, but I know it will be rendered useless if a trigger (*some* triggers, maybe not all) is invoked.

I'm not really conversant with CF, but I would assume you could add the last two statements above into your <cfquery> just before </cfquery>.  Don't know where you would have to declare the recordset variable/object and the identity variable, though.  

I think your 2nd code example will not be reliable, as it uses two queries, which means that in an active environment, someone else could be entering data at the 'proximate same time.  That could produce a situation where your 2nd query would return the id from someone else's insert.

Maybe this will help; if not, try again Monday.

Make a good day . . .
			 . . . barn
Old age and treachery will overcome youth and skill.
From:	Erik Mattheis [gozz at gozz.com]
To:	evolt [thelist at lists.evolt.org]
Subject:	[thelist] @@IDENTITY w/SQL 7 - ColdFusion query
Time:	8/10/2001 at 6:15PM

I'd like to retrieve the primary key ID of an insert statement - SQL
Server 7/CF 5 ... this was the first thing I tried:

<cfquery datasource="dsn">
    INSERT INTO amazon([columns])
    SELECT @@IDENTITY AS amazon_item_id

and expected amazon_item_id to be the ID of the row which was
created, but the value of it appears to be NULL ... which I
understand to mean there was not a new identity created.

Is my syntax incorrect? Is my table definition incorrect? The primary
key row, called amazon_item_id is marked as an Identity.

This however works, but seems clunky to me.

   <cfquery datasource="dsn">
     INSERT INTO amazon([columns])
   <cfquery datasource="april" name="created">
     SELECT @@IDENTITY AS amazon_item_id

Any ideas or is this what I should be doing?

- Erik Mattheis

More information about the thelist mailing list