2 Replies Latest reply on Jan 14, 2013 4:29 PM by Dan Bracuk

    Increment a Hidden Column value by +1 each time..

    jlig Level 1

      I have the following code segment & need to add in a third column called ttcount that needs incremented by "1"

      - It always starts out with a value of "1" (when a ticket is created..)

      - If/when that ticket is "Re-Opened", it needs incremented by +1

       

      so, the question is how to alter the code below to update the ttcount field by 1?

      ---------------------------------

      <!--- Hold off doing the Update unless the the form has been submitted --->

      <cfif isdefined("form.KT_Insert1")>

      <!--- Update the Status to Re-Opened and Owner to Unassigned in the tbltickets table --->

            <cfquery datasource="care">  

          UPDATE tbltickets

      SET ttstatus=<cfqueryparam value="Re-Opened" cfsqltype="cf_sql_varchar">,

          ttowner=<cfqueryparam value="Unassigned" cfsqltype="cf_sql_varchar">

      WHERE ttNum=<cfqueryparam cfsqltype="cf_sql_varchar" value="#url.id#">

        </cfquery>

      </cfif>

      --------------------------------

       

      Note: the ttcount field is a hidden field as follows:

      <input type="hidden" name="ttcount" id="ttcount" value="1" />

        • 1. Re: Increment a Hidden Column value by +1 each time..
          jlig Level 1

          I figured this out using the following code in bold:

          ---------------------------------

          <!--- Hold off doing the Update unless the the form has been submitted --->

          <cfif isdefined("form.KT_Insert1")>

          <!--- Update the Status to Re-Opened and Owner to Unassigned in the tbltickets table --->

                <cfquery datasource="care">  

              UPDATE tbltickets

          SET ttstatus=<cfqueryparam value="Re-Opened" cfsqltype="cf_sql_varchar">,

              ttowner=<cfqueryparam value="Unassigned" cfsqltype="cf_sql_varchar">

             , ttcount=<cfif IsDefined("FORM.ttcount") AND #FORM.ttcount# NEQ "">

          <cfqueryparam value="#FORM.ttcount#" cfsqltype="cf_sql_numeric">+1

          <cfelse>

          NULL

          </cfif>

          WHERE ttNum=<cfqueryparam cfsqltype="cf_sql_varchar" value="#url.id#">

            </cfquery>

          </cfif>    

          -----------------------------

          • 2. Re: Increment a Hidden Column value by +1 each time..
            Dan Bracuk Level 5

            There is an easier way.

             

            UPDATE tbltickets

            SET ttstatus=<cfqueryparam value="Re-Opened" cfsqltype="cf_sql_varchar">

            ,    ttowner=<cfqueryparam value="Unassigned" cfsqltype="cf_sql_varchar">

            , ttcount = ttcount + 1

             

            WHERE ttNum=<cfqueryparam cfsqltype="cf_sql_varchar" value="#url.id#">