SQL Update Dynamic form Names

[tej_web]

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

[Dan_Bracuk · Accepted solution]

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>

[tej_web]

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

[tej_web]

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