How can you update to the same row when........

With MySQL 4.1, you should have transactions available if your table type is InnoDB. This would allow you do to things like the following. If you needed to update a column in another table with the new ID, you could just add a third <cfquery> to take care of that all in one shot. Doing it all at once inside a transaction is best since if any of the queries fail, they all rollback and you don't get any data problems.

You are setting the session variable at wrong place.
Try this:

<cfparam name="SESSION.Last_Insert_id" default="1">

<cfquery name="LastInsertID" datasource="saa">
SELECT LAST_INSERT_ID() AS AD_ID;
</cfquery>

<cflock scope="Session" timeout="30" type="Exclusive">
<cfset SESSION.LAST_INSERT_ID=#LastInsertid.AD_ID#>
</cflock>

<cfoutput>#LastInsertid.AD_ID#</cfoutput>

Hope this will solve your problem.

Sankalan
(www.mindfiresolutions.com)

Hi Sankalan, thanks for your input. I am trying now to get the following code to work on the Insert page and keep getting this error "Element AD_ID is undefined in LASTINSERTID"
Not sure what is wrong but I have tried many different changes and it still will not clear the error.

Any help would be great.

Shane
<cfparam name="SESSION.Last_Insert_id" default="1">

<cflock scope="Session" timeout="30" type="Exclusive">
<cfset SESSION.LAST_INSERT_ID=#LastInsertid.AD_ID#>
</cflock>

<cfquery name="LastInsertID" datasource="saa">
SELECT LAST_INSERT_ID() AS AD_ID;
</cfquery>

<cfoutput>#LastInsertid.AD_ID#</cfoutput>

Basically what we do to get the Last_Insert_ID() is,
Insert query then select Last_Insert_ID(). We need to specify the same datasource for both the queries.

I suppose you have Insert query in one page, then you are submiting the page and in the second page you need to Last_Insert_ID() to update the last inserted row.

If this is the case then, you can select the Last_Insert_ID() in the first page itself and pass the same to the next page to update the row.

Thanks

Sankalan
(www.mindfiresolutions.com)