?
Solved

get the last ID of an item added?

Posted on 2007-07-26
6
Medium Priority
?
812 Views
Last Modified: 2013-12-24
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#;
0
Comment
Question by:pigmentarts
  • 3
  • 2
6 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 1000 total points
ID: 19576002
select scope_identity()   will return
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 19576008
make sure that you run it immediatly after that insert statement
0
 
LVL 8

Expert Comment

by:raj_
ID: 19576013
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 33

Expert Comment

by:hongjun
ID: 19576016
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
 
LVL 33

Assisted Solution

by:hongjun
hongjun earned 1000 total points
ID: 19576024
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
 
LVL 33

Expert Comment

by:hongjun
ID: 19576101
Remember to use <cflock>
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question