EasyCFM.COM - View Tutorial

MS Access:
The problem with MS Access is that it does not have any built in functionality to obtain the last id. So, in order to obtain it with the highest level of confidence we’ll want to wrap the query in a <cftransaction> block.

</cftransaction>

This will ensure that whatever is in this block will be executed in the same scope. This may slow things down a little, but is well worth it.

Now, inside the <cftransaction> block we’ll add our insert query:

<cftransaction>
    <cfquery name=“insertRecord“ datasource=“myAccessDB“>
        INSERT INTO tblComments (author,title,message)
        VALUES (‘John Doe’,’I really like this tutorial’,’This is the best one yet!’)
    </cfquery>
</cftransaction>

Okay, easy enough. Now I want to return the user to a confirmation page with the book they just entered. The problem is, how do I know what book was just entered? Well, as I stated above, the most common solution I’ve seen is to query the table for the last record filtering on data we just entered. This approach leaves you wide open for errors if someone enters the same or similar enough data.

However, in our case we wrapped the transaction in a <cftransaction> block. That means we can ensure that subsequent queries inside this block execute in the same scope before any other database requests from other sessions. It locks the table for that brief moment and queues up any transactions that were submitted after this one. So, how do we get the ID? Like this:

<cftransaction>
    <cfquery name=“insertRecord“ datasource=“AccessDB“>
        INSERT INTO tblComments (author,title,message)
        VALUES (‘John Doe’,’I really like this tutorial’,’This is the best one yet!’)
    </cfquery>
    <cfquery name=“getNewID“ datasource=“myAccessDB“>
        SELECT MAX(id) as newID
        FROM tblComments
    </cfquery>
    <cfset newID = getNewID.newID>
</cftransaction>

There it is. Now we just pass back #newID# and we can identify the record we just inserted.

MS SQL:
MS SQL is even better at handling inserts and returning auto generated IDs. In fact, there are several functions that make this possible. I choose to use Scope_Identity (). This returns the ID of the last inserted record in the scope or session. So to use it, do the following:

<cfquery name=“insertRecord“ datasource=“MSSQLDB“>
INSERT INTO tblComments (author,title,message)
VALUES (‘John Doe’,’I really like this tutorial’,’This is the best one yet!’);

SELECT Scope_Identity () as newID
</cfquery>
<cfset newID = insertRecord.newID>

Even easier right? Now the local variable newID contains the ID of the record you just inserted.

MySQL:
As with MSSQL, MySQL has a built in function to retain the last inserted id. This function is called: LAST_INSERT_ID(). Unfortunately the JDBC drivers for MySQL prohibit the use of multiple queries in a single database transaction. So, therefore we use <cftransaction>:

<cftransaction>
    <cfquery name=“insertRecord“ datasource=“MySQLDB“>
        Insert Into tblComments (author,title,message)
        Values (‘John Doe’,’I really like this tutorial’,’This is the best one yet!’)
    </cfquery>
    <cfquery name=“getNewID“ datasource=“MySQLDB“>
        Select LAST_INSERT_ID() as newID
    </cfquery>

Suppose I have multi users inserting records at the same time, and it will not be ok with this code.

Posted on 04/08/2007
The same tutorial which previously been submitted by Pablo. :D

Posted on 12/27/2006
Very cool example. However, it does not work when you are using more complex ids. IE, if you were to use UUID's or MSUID's for your pk's this will only return the numeric value of the id and not the id it's self.

Posted on 09/18/2006
how can i get the ID of the second last entry from a database

Posted on 09/18/2006
Excellent

Posted on 09/07/2006

MS Access: The problem with MS Access is that it does not have any built in functionality to obtain the last id. So, in order to obtain it with the highest level of confidence we’ll want to wrap the query in a <cftransaction> block.

MS SQL: MS SQL is even better at handling inserts and returning auto generated IDs. In fact, there are several functions that make this possible. I choose to use Scope_Identity (). This returns the ID of the last inserted record in the scope or session. So to use it, do the following:

MySQL: As with MSSQL, MySQL has a built in function to retain the last inserted id. This function is called: LAST_INSERT_ID(). Unfortunately the JDBC drivers for MySQL prohibit the use of multiple queries in a single database transaction. So, therefore we use <cftransaction>:

MS Access:
The problem with MS Access is that it does not have any built in functionality to obtain the last id. So, in order to obtain it with the highest level of confidence we’ll want to wrap the query in a <cftransaction> block.

MS SQL:
MS SQL is even better at handling inserts and returning auto generated IDs. In fact, there are several functions that make this possible. I choose to use Scope_Identity (). This returns the ID of the last inserted record in the scope or session. So to use it, do the following:

MySQL:
As with MSSQL, MySQL has a built in function to retain the last inserted id. This function is called: LAST_INSERT_ID(). Unfortunately the JDBC drivers for MySQL prohibit the use of multiple queries in a single database transaction. So, therefore we use <cftransaction>: