<cfquery name="qryGetMAXID" datasource="#Request.DSN#">
MAX(FLEET_CON_ID)+1 as MaxFleetId
<cfset intFLEET_CON_ID = #qryGetMAXID.MaxFleetId#/>
<cfquery name="qryAddOperator" datasource="#Request.DSN#" result="testing">
the Query qryGETMAXID always returns the same value as 18703. I inserted some values into the database directly. the Query should return 18705. When I run the same query in SQL Developer it returns the correct value. I have not cached the query.
Please help me out.
Thanks in advance
This can happen if the queries are being cached. If you don't have the "cachedwithin" parameter in your CFQUERY tag, check the CFAdmin to see if queries are being cached, there.
@Dan: Sometimes, rarely, there is a reason for manual incrementing.
SELECT MAX(FLEET_CON_ID)+1 as MaxFleetId
Unless you are using locking, a serializable transaction (not the default), etcetera two threads can still obtain the same "MaxFleetId" value. If that will cause a problem in your application, you should consider letting the database determine the next available id as Dan suggested.