Gow to Get a RecordID after a <CFUPDATE> 2

[bluesauceuk]

Hi!

I have a major problem - I can't seem to work out how to get the record number after I have updated a table item.

I know from past experience that after a <CFINSERT> I can do a query and get a MAX(RecordID) and that seems to work.

I cannot work out how to get the record ID when using an insert.

As the data is passed from a form. There is no URL.Record value...

Can someone please help me?

Cheers

Mark ;-)

 

[tlhawkins]

Hey Mark,

I think this will work in your case. I use it instead of Max(recordID) whenever there is a chance of more then one user inserting at the same time, otherwise one of them could get the wrong ID.

<cftransaction action=&quot;BEGIN&quot;>
<cftry>

<CFINSERT Datasource=&quot;mydata&quot;
Tablename=&quot;tblmine&quot;
FormFields=&quot;this, that, theother&quot;>

<cfquery name=&quot;Insert2&quot; Datasource=&quot;TimeCard&quot;>
select @@identity AS ID
</cfquery>

<CFSET MYID = #Insert2.ID#>

<cfcatch type=&quot;database&quot;>

Run your error routine, whatever it is, here
Rollback the transaction so the records are not inserted

<cftransaction action=&quot;ROLLBACK&quot;></cftransaction>
<CFLOCATION url=&quot;Error.cfm&quot; >
</cfcatch>
</cftry>
</cftransaction>

MyID should now have the record ID of the Action taken within the transaction. I have only used this with <CFINSERT... but I don't see why this wouldn't work with a <CFUPDATE ... as well.

Have fun ...

 

[strantheman]

hi bluesauceuk

Can you post the SQL you're using to run the update, along with any CF variables you're using in the WHERE/ AND clauses?

If you are able to specifically reference a record to update, then you should be able to specifically reference that records ID with a select statement using the same WHERE/ AND clauses.