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 :)
// ADD NEW ITEM
INSERT INTO products (name, productCode, description) VALUES ('#FORM.name#', '#FORM.productCode#', '#FORM.description#')
//GET THE LAST ITEM ADDED
<cfquery name="getLastProdId" maxrows="1">
SELECT prodId FROM products ORDER BY prodId DESC
//SET ID IN A STRING
prodId = #getLastProdId.prodId#;