CFQUERY always returns the same value

[meensi]

<cfquery name="qryGetMAXID" datasource="#Request.DSN#">

        SELECT

            MAX(FLEET_CON_ID)+1 as MaxFleetId

        FROM

            CONFIGURATION

    </cfquery>

    <cfset intFLEET_CON_ID = #qryGetMAXID.MaxFleetId#/>

    <cfquery name="qryAddOperator" datasource="#Request.DSN#" result="testing">

        INSERT INTO

            CONFIGURATION

        (FLEET_CON_ID,

        COL2,

        COL3,

        COL4)

        VALUES

        (#intFLEET_CON_ID#,

        "ADD OPERATOR",

        '#strCode#',

        '#strName#')

    </cfquery>

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

[Dan_Bracuk]

My suggestion is to let the database determine the value of the id field.  The way to do that depends on what type of database you are using.

[WolfShade]

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.

^_^

[meensi · Accepted solution]

Dan and Wolfshade,

Thanks for the problem.

I figured out the problem, while I insert into the database, I did not commit it. so it was returning the same value.

sorry..

[-__cfSearching__-]

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.