I don't think @@IDENTITY OR SCOPE_IDENTITY() (still waiting on a reply Ajudh) will work on MS Access, you would have to work with SELECT MAX() blah blah right after the insert, thats what I remember from when I worked with access (many moons ago)
-----Original Message----- From: Beattie, Barry [mailto:[EMAIL PROTECTED] Sent: Wednesday, 26 November 2003 3:42 PM To: CFAussie Mailing List Subject: [cfaussie] RE: get primary key allocated during insert? hang on guys. select @@identity will work... a) on MSAccess db's (after Access97) b) work straight after a <cfquery> insert eg: here's some (crap DWMX made) code that works just fine using 3 <cfqueries>. MSAccess can't run multiple queries within the same <cfquery> tag, they have to be seperate look near the bottom for <cfquery name="rsOrderID" datasource="andes_bb"> SELECT @@IDENTITY as OrderID FROM Orders </cfquery> the page holds the connection open, that's why it works (although I'd use <cftransaction> just to make sure) cheers barry.b <cfif IsDefined("FORM.MM_InsertRecord") AND FORM.MM_InsertRecord EQ "OrderForm"> <cfquery datasource="andes_bb"> INSERT INTO Orders (CustomerID, OrderDate, Timestmp, OrderTotal, ShipAddress, ShipSuburb, ShipState, ShipPostCode, ShipCountry) VALUES ( <cfif IsDefined("FORM.CustomerID") AND #FORM.CustomerID# NEQ ""> #FORM.CustomerID# <cfelse> NULL </cfif> , <cfif IsDefined("FORM.OrderDate") AND #FORM.OrderDate# NEQ ""> ###FORM.OrderDate### <cfelse> NULL </cfif> , <cfif IsDefined("FORM.Timestmp") AND #FORM.Timestmp# NEQ ""> '#FORM.Timestmp#' <cfelse> NULL </cfif> , <cfif IsDefined("FORM.OrderTotal") AND #FORM.OrderTotal# NEQ ""> #FORM.OrderTotal# <cfelse> NULL </cfif> , <cfif IsDefined("FORM.ShipAddress") AND #FORM.ShipAddress# NEQ ""> '#FORM.ShipAddress#' <cfelse> NULL </cfif> , <cfif IsDefined("FORM.ShipSuburb") AND #FORM.ShipSuburb# NEQ ""> '#FORM.ShipSuburb#' <cfelse> NULL </cfif> , <cfif IsDefined("FORM.ShipState") AND #FORM.ShipState# NEQ ""> '#FORM.ShipState#' <cfelse> NULL </cfif> , <cfif IsDefined("FORM.ShipPostCode") AND #FORM.ShipPostCode# NEQ ""> #FORM.ShipPostCode# <cfelse> NULL </cfif> , <cfif IsDefined("FORM.ShipCountry") AND #FORM.ShipCountry# NEQ ""> '#FORM.ShipCountry#' <cfelse> NULL </cfif> ) </cfquery> <!-------------- get the latest order id --------------> <cfquery name="rsOrderID" datasource="andes_bb"> SELECT @@IDENTITY as OrderID FROM Orders </cfquery> <!-------------- process each item in the cart ---------> <cfloop collection="#session.cart#" item="key"> <cfset qty = StructFind(session.cart, #key#)> <cfquery name="rsOrderDetail" datasource="andes_bb"> INSERT INTO OrderDetails(OrderID, ProductID, Quantity) VALUES(#rsOrderID.OrderID#, #key#, #qty#) </cfquery> </cfloop> <cflocation url="ecommerce_payment.cfm?OrderID=#rsOrderID.OrderID#"> </cfif> -----Original Message----- From: Taco Fleur [mailto:[EMAIL PROTECTED] Sent: Wednesday, 26 November 2003 2:48 PM To: CFAussie Mailing List Subject: [cfaussie] RE: get primary key allocated during insert? What is SCOPE_IDENTITY() for then? -----Original Message----- From: Ayudh Nagara [mailto:[EMAIL PROTECTED] Sent: Wednesday, 26 November 2003 2:44 PM To: CFAussie Mailing List Subject: [cfaussie] RE: get primary key allocated during insert? @@IDENTITY is connection-specific. It returns the last identity value created on the same database connection. So if you retrieve the @@IDENTITY within the same CFQUERY as the INSERT statement, you can be assured it will return the identity you're after. SQL Server only AFAIK. <CFQUERY NAME="NewCustomer" ....> INSERT INTO customers (..., ..., ...) VALUES (..., ..., ...); SELECT @@IDENTITY AS NewCustomerID FROM customers </CFQUERY> Regards: Ayudh +----------------------------------------------------------------+ | SOAP is the glue! Hook up your server directly to your bank. | | Connect to VeriPay xServ, the Australian Payments Web Service. | | Reliable, Secure, FAST: http://www.xilo.com/xserv | +----------------------------------------------------------------+ ----- Original Message ----- From: "Taco Fleur" <[EMAIL PROTECTED]> To: "CFAussie Mailing List" <[EMAIL PROTECTED]> Sent: Wednesday, November 26, 2003 10:20 AM Subject: [cfaussie] RE: get primary key allocated during insert? @@IDENTITY returns the last ID inserted globally, it does not guarantee that it is actually the ID your after. SCOPE_IDENTIY() returns the last ID within the scope. -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, 26 November 2003 9:16 AM To: CFAussie Mailing List Subject: [cfaussie] RE: get primary key allocated during insert? Brian, I use this query immediately following an insert query if I want the primary key value: <CFQUERY NAME="getid" DATASOURCE="dsn"> SELECT @@Identity as artid FROM tablename </cfquery> I guess you could wrap the two queries in a CFTRANSACTION if it is high traffic. Regards... Steve C -----Original Message----- From: Brian Gilbert [mailto:[EMAIL PROTECTED] Sent: Wednesday, 26 November 2003 10:12 AM To: CFAussie Mailing List Subject: [cfaussie] get primary key allocated during insert? Hi all, Is there a way to get the primary key (autonumber) that would be allocated during an insert statement in a cfquery? TIA Brian --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia http://www.mxdu.com/ + 24-25 February, 2004 --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia http://www.mxdu.com/ + 24-25 February, 2004 --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia http://www.mxdu.com/ + 24-25 February, 2004 --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia http://www.mxdu.com/ + 24-25 February, 2004 --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia http://www.mxdu.com/ + 24-25 February, 2004 --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia http://www.mxdu.com/ + 24-25 February, 2004 --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia http://www.mxdu.com/ + 24-25 February, 2004
