Solved

Output Inserted ID in MS Access & Coldfusion

Posted on 2008-10-29
7
496 Views
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#','#form.contact#','#form.marketing#')
</cfquery>
<cfoutput>
<cfdump var="#inserted#">
</cfoutput>

Open in new window

0
Comment
Question by:bjv211
  • 3
  • 2
  • 2
7 Comments
 
LVL 42

Assisted Solution

by:dqmq
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.
0
 
LVL 1

Author Comment

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

Expert Comment

by:dqmq
ID: 22836886
What does one row of the table represent?  
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:bjv211
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
0
 
LVL 59

Accepted Solution

by:
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.
0
 
LVL 1

Author Comment

by:bjv211
ID: 22838082
mwvisa1, thanks for your post, you gave me a good search term and I discovered this http://cfmxplus.blogspot.com/2002/08/oh-my-select-identity-works-in-access.html  which seems reliable and efficient.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22840341
Very nice!

Glad you found that.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

685 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