Skip to main content
Inspiring
June 11, 2012
Answered

CFQUERY always returns the same value

  • June 11, 2012
  • 3 replies
  • 1326 views

<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

This topic has been closed for replies.
Correct answer meensi

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..

3 replies

meensiAuthorCorrect answer
Inspiring
June 11, 2012

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..

Inspiring
June 12, 2012

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.

WolfShade
Legend
June 11, 2012

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.

^_^

Inspiring
June 11, 2012

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.