get the last ID of an item added?

at the moment I have a query which inserts a new product into my database. after I have done this I need to know the new prodID of the item just added (the unique ID  generated by the database).

As you can see below (code)  I dont do this very efficiently at the moment , because if another user was adding at the  same time  its possible although unlikely that I could get the wrong product ID.

So my question is....... can I get the prodid of the item I have just added (see below), without have to run another query right after to find the last id?

Thank in advanced  :)  


the code.....

// ADD NEW ITEM
<cfquery name="addProduct">
    INSERT INTO products (name, productCode, description) VALUES ('#FORM.name#', '#FORM.productCode#', '#FORM.description#')
</cfquery>

//GET THE LAST ITEM ADDED
<cfquery name="getLastProdId" maxrows="1">
      SELECT prodId FROM products ORDER BY prodId DESC
</cfquery>
            
//SET ID IN A STRING
prodId = #getLastProdId.prodId#;
LVL 12
pigmentartsAsked:
Who is Participating?
 
Aneesh RetnakaranDatabase AdministratorCommented:
select scope_identity()   will return
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
make sure that you run it immediatly after that insert statement
0
 
raj_Commented:
http://msdn2.microsoft.com/en-us/library/ms190315.aspx


USE tempdb
GO
CREATE TABLE TZ (
   Z_id  int IDENTITY(1,1)PRIMARY KEY,
   Z_name varchar(20) NOT NULL)

INSERT TZ
   VALUES ('Lisa')
INSERT TZ
   VALUES ('Mike')
INSERT TZ
   VALUES ('Carla')

SELECT * FROM TZ

--Result set: This is how table TZ looks.
Z_id   Z_name
-------------
1      Lisa
2      Mike
3      Carla

CREATE TABLE TY (
   Y_id  int IDENTITY(100,5)PRIMARY KEY,
   Y_name varchar(20) NULL)

INSERT TY (Y_name)
   VALUES ('boathouse')
INSERT TY (Y_name)
   VALUES ('rocks')
INSERT TY (Y_name)
   VALUES ('elevator')

SELECT * FROM TY
--Result set: This is how TY looks:
Y_id  Y_name
---------------
100   boathouse
105   rocks
110   elevator

/*Create the trigger that inserts a row in table TY
when a row is inserted in table TZ*/
CREATE TRIGGER Ztrig
ON TZ
FOR INSERT AS
   BEGIN
   INSERT TY VALUES ('')
   END

/*FIRE the trigger and determine what identity values you obtain
with the @@IDENTITY and SCOPE_IDENTITY functions.*/
INSERT TZ VALUES ('Rosalie')

SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]
GO
SELECT   @@IDENTITY AS [@@IDENTITY]
GO
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
hongjunCommented:
try this

<cflock name="insertrecord" timeout="10">
<cfquery name="addProduct">
    INSERT INTO products (name, productCode, description) VALUES ('#FORM.name#', '#FORM.productCode#', '#FORM.description#')
</cfquery>
<cfquery name="getLastProdId" maxrows="1">
      SELECT @@IDENTITY MaxID
</cfquery>
</cflock>

prodId = #getLastProdId.MaxID#;
0
 
hongjunCommented:
Or this

<cflock name="insertrecord" timeout="10">
<cfquery name="addProduct">
    INSERT INTO products (name, productCode, description) VALUES ('#FORM.name#', '#FORM.productCode#', '#FORM.description#')
</cfquery>
<cfquery name="getLastProdId" maxrows="1">
      SELECT scope_identity() MaxID
</cfquery>
</cflock>

prodId = #getLastProdId.MaxID#;
0
 
hongjunCommented:
Remember to use <cflock>
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.