For a low volume site it works alright, as efficiency probably isn't your biggest concern. If you're using Access it can probably be assumed that the application is fairly low volume.
There are some added considerations, though, that make this approach less than foolproof: - You have to make sure that all inserts into the table are locked, not just those where you need to retrieve the last id. - You have to make sure all of inserts use the same lock name. Using name="databasename_tablename" is one approach that will keep you from blocking access to other tables. In a shared environment I'd use "myname_databasename_tablename". ----- Original Message ----- From: "Barney Boisvert" <[EMAIL PROTECTED]> To: "CF-Talk" <cf-talk@houseoffusion.com> Sent: Monday, January 31, 2005 12:01 PM 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 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:192389 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=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
