Output Inserted ID in MS Access & Coldfusion

Posted on 2008-10-29
Last Modified: 2012-06-22
I need to output the row ID of the last inserted row without using a MAX statement.  I have used the MAX statement in the past, however, it is not fool proof at accessing the correct ID. I am using CF 8 and understand that you can do this with the Result variables in cfquery however I am not able to do it using a MS access DB.  Is this a limitation with using MS Access?  Is there a way to do this using SQL?
<cfquery datasource="#application.dsn#" name="insertIT"  result="inserted">
INSERT INTO events (title,starttime,endtime,description,contact,marketing)
VALUES ('#form.title#',<cfqueryparam cfsqltype="cf_sql_timestamp" value="#starttime2#">,<cfqueryparam cfsqltype="cf_sql_timestamp" value="#endtime2#">,'#form.description#','','')
<cfdump var="#inserted#">

Open in new window

Question by:bjv211
  • 3
  • 2
  • 2
LVL 42

Assisted Solution

dqmq earned 200 total points
ID: 22836265
Especially when you have an AutoNumber PK, it's important remember the natural key to your table.  The AutoNumber is generally a surrogate key and conveys no business meaning.   The table should also have a natural key (ideally, with a unique index) that uniquely identifies each row.

Without that, you could, for example, insert the same event twice with no means to tell them apart except the autonumber. Since that field has no business meaning, then you really do not know if you have a duplicate row or not.  Bottom line, you best have some combination of business-meaningful columns to identify a row.

It's that set of columns, or "alternate key", that identifies every row in business terms.  Lookup the ID column based on that key.

Author Comment

ID: 22836469
Thanks for the overview, in your opinion what would be the best combination for the key here?
LVL 42

Expert Comment

ID: 22836886
What does one row of the table represent?  
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.


Author Comment

ID: 22837433
Basic information (date/time,contact person,location,title) of an event.  the current PK for this table is the Autonumber eventID which ties all the tables together
LVL 59

Accepted Solution

Kevin Cross earned 300 total points
ID: 22837679
If this where SQL, I would say use a <cftransaction> and just follow the INSERT with setting of a SQL variable with SCOPE_IDENTITY() and return the variable to CF.

Alternatively in CF, you could do (it has been a little bit for me and CF so forgive me if wrong tag) CFLOCK (lock application) and run query for insert and immediately following that SELECT MAX(ID) FROM TableName.  Unless you didn't want to use MAX for another reason other than synchronization/thread safety.

Author Comment

ID: 22838082
mwvisa1, thanks for your post, you gave me a good search term and I discovered this  which seems reliable and efficient.
LVL 59

Expert Comment

by:Kevin Cross
ID: 22840341
Very nice!

Glad you found that.

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Server 2012 r2 - Make Temp Table Query Faster 5 41
Access on Mouse move 5 33
Column Layout in Access Xp VBA Report 3 19
Query Syntax 17 34
CFGRID Custom Functionality Series -  Part 1 Hi Guys, I was once asked how it is possible to to add a hyperlink in the cfgrid and open the window to show the data. Now this is quite simple, I have to use the EXT JS library for this and I achiev…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question