• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 513
  • Last Modified:

Output Inserted ID in MS Access & Coldfusion

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
bjv211
Asked:
bjv211
  • 3
  • 2
  • 2
2 Solutions
 
dqmqCommented:
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
 
bjv211Author Commented:
Thanks for the overview, in your opinion what would be the best combination for the key here?
0
 
dqmqCommented:
What does one row of the table represent?  
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
bjv211Author Commented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
 
bjv211Author Commented:
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
 
Kevin CrossChief Technology OfficerCommented:
Very nice!

Glad you found that.
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now