Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Output Inserted ID in MS Access & Coldfusion

Posted on 2008-10-29
7
Medium Priority
?
510 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 800 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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
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 60

Accepted Solution

by:
Kevin Cross earned 1200 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 60

Expert Comment

by:Kevin Cross
ID: 22840341
Very nice!

Glad you found that.
0

Featured Post

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
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 …

773 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