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
