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#;
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#;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Aneesh
make sure that you run it immediatly after that insert statement
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
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
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#;
<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#;
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Remember to use <cflock>