It absolutely isn't your only option. And if you do the below you need to use cftransaction.
The best option, in my opinion, is to generate a UUID/GUID and use that instead of a database generated ID. That will work with every single database platform out there and will always be the correct ID. - Calvin -----Original Message----- From: Ewok [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 01, 2005 10:01 AM To: CF-Talk Subject: RE: EASY: grabbing the id of a newly created item.. Wow, too much for so little, Just use Richard's method RUN YOUR INSERT QUERY Then directly after the insert get the records ID... <cfquery name="mid" datasource=""> SELECT MAX(TheID) as LatestID FROM TABLENAME </cfquery> The newest records ID from that table will now be in #mid.LatestID# Since you said "grab the id ( autonum, primary key)"... autonumber being an Access data type means the above is about your only option. Does @@identity even work in access? (or does anything else useful work in access for that matter) There's also no need to cflock the query from above that I can think of with an access data source. For the most part, access locks itself when it needs to. -----Original Message----- From: Barney Boisvert [mailto:[EMAIL PROTECTED] Sent: Monday, January 31, 2005 2:02 PM To: CF-Talk Subject: Re: EASY: grabbing the id of a newly created item.. That's a horrible way to do it. Quite inefficient. A better route is to check your DB's docs and see how they expose the last inserted sequence value. In MySQL it's LAST_INSERT_ID(), with MS SQL Server its one of three @IDENTITY variables. Run your INSERT, and then select the value back out using this mechanism. You may or may not need a transaction, depending on the specifics, and you definitely won't need CFLOCK. cheers, barneyb On Mon, 31 Jan 2005 10:24:34 -0800, Richard Colman <[EMAIL PROTECTED]> wrote: > This is actually pretty easy in ACCESS: > > 1) put the enire transaction within a CFLOCK block > > 2) do the insert > > 3) then select max(id) assuming you have an autonumber field for the id > > Which will give you the id of the record just inserted. > > This is so easy that even I can do it. > > Rick Colman > -- Barney Boisvert [EMAIL PROTECTED] 360.319.6145 http://www.barneyb.com/ Got Gmail? I have 6 invites. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:192550 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
