ID of last record inserted

[DJCull]

Is it possible, with a MS Access database, to pull the ID of a record just
inserted? I have found various threads and tutorials on various sites referring
to commands that can be used for this but they all seem to be unuseable with an
MS Access database. Many of the sites I have visited have advised there is
only one way to do this with Access but fail to make it clear how it should be
done.

I know that it can done by selecting the highest number ID but on a site that
may have a lot of traffic and the chance of simultaneous submissions, this
method would not be very reliable. I can also query the database with all the
details I have used to create the record in the first place and then output
the ID value but this seems to be a bit of a dirty way of doing it when there
are commands that can be used with other databases!

[jorgepino]

I usally create a
<Cfquery name = "lastid" dataSource = "#DNS#" maxrow =1 >
select id
from database
order by id desc
</cfquery>
<cfoutput query="lastid">#ID# </cfoutput>

can you post some code. it woudl make it easier to come up with a better answer

[Captain Ru]

I have the same question: I am inserting a record into a table, but need the
autonumber ID to be saved into a related table, using another insert query.

Here is the code for both tables as current.

<cfif AddRecord is "yes"><!--- Add Ticket --->
<CFQUERY NAME="#Table_Name#_Insert_Query" DATASOURCE="HelpDesk">
INSERT Into #Table_Name# (Entry_Date, Name, Priority_Level,
Type, Status, Entry_Description, Location)
VALUES ('#Entry_Date#','#Tech_Name#','#Priority_Level#',
<cfif #Type# is "">Null
<cfelse>'#Type#'
</cfif>, '#Status#', '#Entry_Description#',
<cfif #Location# is "">Null
<cfelse>'#Location#'
</cfif>)
</CFQUERY>


<cfquery name = "request" datasource = "helpdesk">
insert into request (fname, lname, survey, EntryID)
values ('#fname#', '#lname#', '#survey#', #id#)
</cfquery>

Here is what I tried, with no success:
<cfoutput query = "#Table_Name#_Insert Query">
<cfset id = #entry_id#>
</cfoutput>

[Captain Ru]

Here is my solution:

<cfif AddRecord is "yes"><!--- Add Ticket --->
<CFQUERY NAME="#Table_Name#_Insert_Query" DATASOURCE="HelpDesk">
INSERT Into #Table_Name# (Entry_Date, Name, Priority_Level,
Type, Status, Entry_Description, Location)
VALUES ('#Entry_Date#','#Tech_Name#','#Priority_Level#',
<cfif #Type# is "">Null
<cfelse>'#Type#'
</cfif>, '#Status#', '#Entry_Description#',
<cfif #Location# is "">Null
<cfelse>'#Location#'
</cfif>)
</CFQUERY>

<cfquery name = "lastid" dataSource = "HelpDesk" maxrows = "1">
select entry_id
from help_desk_list
order by entry_id desc
</cfquery>

<cfoutput query="lastid">
<cfset id = #entry_id#>
</cfoutput>


<cfquery name = "request" datasource = "helpdesk">
insert into request (fname, lname, survey, EntryID)
values ('#fname#', '#lname#', '#survey#', #id#)
</cfquery>

Is there a better way?

[mxstu]

DJCull,

For Access, you can use SELECT @@IDENTITY in combination with CFTRANSACTION.
Grouping the queries within a CFTRANSACTION ensures that you pull ID of the
record just inserted and that the queries succeed/fail as a single unit.





<cftransaction>
<cfquery name="addRecord" ...>
INSERT INTO yourTable (....)
VALUES (....)
</cfquery>
<cfquery name="getNewRecord" ...>
SELECT @@IDENTITY AS NewID
</cfquery>

<cfquery name="insertRelatedRecord" ...>
INSERT INTO yourRelatedTable(relatedIDColumn, ...)
VALUES (#getNewRecord.NewID#, ....)
</cfquery>
</cftransaction>

[mxstu]

Captain Ru,

A few things about the code you posted

1) At the very least, you should enclose all of the CFQUERY's in a
CFTRANSACTION to ensure that all of the queries succeed/fail together and that
the "lastid" query returns the correct record ID.

2) If you are not using SELECT @@IDENTITY, then the MAX() aggregate

SELECT MAX(Entry_ID)
FROM help_desk_list

... or even the TOP N operator ...

SELECT TOP 1 entry_id
FROM help_desk_list
ORDER BY entry_id DESC

would typically provide better performance than the current query :

select entry_id
from help_desk_list
order by entry_id desc

The CFQUERY "maxrows" attribute only limits the records in the CFQUERY object,
it doesn't limit the information your database returns, so the current query
would typically return much more information than is needed. The SELECT MAX()
method returns only a single record, which is what you want.

3) You do not need # signs around your variables in the CFIF statements. You
may also want to TRIM the values to catch invalid values like .... a string of
5 spaces.

<cfif Len(Trim(Type)) eq 0>Null<cfelse>'#Type#'</cfif>

instead of

<cfif #Type# is "">Null<cfelse>'#Type#'</cfif>

4) You do not need to use CFOUTPUT when using a query value in a CFSET
statement. This statement sets the "id" variable to the "entry_id" in the
first query row.

<cfset id = lastid.entry_id>

instead of ...

<cfoutput query="lastid">
<cfset id = #entry_id#>
</cfoutput>

5. If you are using FORM or URL variables, then you should scope your
variables in your code, ex. #form.entry_id#

[Captain Ru]

The transaction and @@ are some things that I haven't yet come across (i'm new)
but I'll try them out.

so my...
<cfquery name = "lastid" dataSource = "HelpDesk" maxrows = "1">
select top 1 entry_id
from help_desk_list
order by entry_id desc
</cfquery>

<cfoutput query="lastid">
<cfset id = #entry_id#>
</cfoutput>


would be..

<cfquery name = "lastid" dataSource = "HelpDesk">
select @@identity as id
</cfquery>

??

[mxstu]

Yes, as long as all of the related queries are within one CFTRANSACTION. You
should also be using CFQUERYPARAM, for security (see livedocs).

Also, be careful of sql injection. What type of parameter is #Table_Name# in
your first insert statement?

.... INSERT Into #Table_Name# ....

[Captain Ru]

basically i'm a new hire for a guy that quit, and he started using CF, so now
I'm learning CF and trying to fix things

Eventually I'll have all these variables held in the application.cfm.

Could you suggest any good online tutorials or lessons, or any text? I am
using the Ben Forta and Nate Weiss 5th Edition Mac CF MX Web App Construct. Kit

[mxstu]

Originally posted by: Captain Ru
Eventually I'll have all these variables held in the application.cfm.

Could you suggest any good online tutorials or lessons, or any text? I am
using the Ben Forta and Nate Weiss 5th Edition Mac CF MX Web App Construct. Kit

I haven't personally read it, but I've heard it provides a pretty good
introduction to CF MX. As far as web site tutorials, again I haven't reviewed
any extensively, but [url]http://www.w3schools.com/sql/default.asp[/url]. It has a good
explanation of most of your basic SQL statements.

If you are using MX, as you get more experience, you may want to look into
CFC's . They really help consolidate and compartmentalize your code.