3 Replies Latest reply on Dec 9, 2008 7:28 AM by tej_web

    SQL Update Dynamic form Names

    tej_web Level 1
      Hello all,

      Using CFloop updating SQL table with two or more records.

      This page prints user subscriber bill.

      GUI;

      CFOUTPUT QUERY="PT"

      A HREF="PtDemo.cfm?bill_id=#PT.bill_id#" target="_self">#subscriber#

      td align="center">#ptnum# 
      td align="center">#re#
      td align="center"><font color="##FF0000">#phone#</font>

      <cfset ptamount = 0>

      <td align="center" >$ <font color="##ff0000">#ptamount#</font>  </td>

      <td align="center">#psdate# align="center">#peDate#

      <font color="##FF0000">#TV#</font>
      $ <font color="##ff0000">#ttamount#</font>  

      THE AMMOUNT FIELD BELOW IS THE FIELD IN NEED TO UPDATE IN SQL
      input type="text" name="amount" value="#tBilledAmount# "

      input type="Checkbox" name="bill" value="#bill_id#"

      </CFOUTPUT>

      PROCESSING FORM

      <cfset pdate = dateformat(now(), "mm/dd/yyyy")>

      cfset billedVar = "Y">
      cfif isdefined("form.bill")>
      cfoutput

      CFLOOP INDEX="isid" LIST="#form.bill#"

      <cfquery name="prcPna" datasource="wCast">

      update billing

      set billed = 'Y', billed_date = '#pdate#', amount = '#form.amount#', paid = 'y', paid_date = '#pdate#'

      where bill_id = #bill_id#


      </cfquery>


      <!--- <cfoutput>#form.bill#</cfoutput> amount = '#form.ptnum#',--->
      </CFLOOP>

      </cfoutput>
      </cfif>

      Using the loop List, I can update the table with field names the are constant.

      I need to update the calculated amount with ;
      input type="text" name="amount" value="#tBilledAmount# " This will create a list; (#tBilledAmount# ex: "10,11,12")
      OR
      input type="text" name="#amount#" value="#tBilledAmount# " This will display "unknown field name"

      Any ideas will help.

      Thank you
      T Jones
        • 1. Re: SQL Update Dynamic form Names
          Dan Bracuk Level 5
          I just did a presentation about this to my local cfug. It's close, but not exactly your scenario.

          Shows another use of array notation. The scenario is the the user sees a bunch of records and can add a date to any of them. This will update the db table.

          On the form page.

          <cfquery name = "q1">
          select id
          from sometables
          where whatever
          </cfquery>

          <cfform>
          <cfoutput query="q1">
          <cfinput name = "date#id#"> <!--- note variable portion of input name --->
          etc

          On the action page

          <cfloop list = "#form.fieldnames#" index = "ThisField">

          <cfif left(ThisField, 4) is "date" and len(form[ThisField] gt 0>
          <!--- assume yyyy-mm-dd --->
          <cfset ThisValue = CreateDate(left(form[ThisField], 4), mid(form[ThisField], 6, 2), right(form[ThisField], 2)>
          <cfset ThisRecord = RemoveChars(ThisField, 1, 4)>

          <cfquery>
          update SomeTable
          set SomeField = <cfqueryparam cfsqltype="cf_sql_date" value="#ThisValue#" >
          where TheIdField = <cfqueryparam cfsqltype="cf_sql_integer" value="#ThisRecord#" >
          </cfif>
          </cfloop>



          • 2. Re: SQL Update Dynamic form Names
            tej_web Level 1
            Hi Dan,

            Thanks for your reply.


            I will make some changes based on your example and then update the thread with the results

            Thank you
            Tjones
            • 3. Re: SQL Update Dynamic form Names
              tej_web Level 1
              Thanks again Dan,

              I modified your example to solve my SQL update issue.
              This is what worked for me.

              User Form.

              <cfquery name="PT" datasource="DS">
              select bill_id, amount
              from billing
              </cfquery>

              <cfoutput Query="PT">

              <cfset afield = "amount" & #bill_id#>
              <input type="text" name="#afield#" value="#amount#">
              <input type="checkbox" name="bill" value="#bill_id#">

              </cfoutput>

              Action Page.

              <cfloop Index="isid" list="#form.bill#">
              <cfset fAmount = #form["amount" & isid]#>

              <cfquery name="update" datasource="DS" >
              update BILLING
              set amount = #fAmount#
              where bill_id = #isid#
              </cfquery>


              Thanks again.
              Tjones