Skip to main content
A
Anonymous
January 21, 2012
Question

Using cfloop to update and/or add data?

  • January 21, 2012
  • 1 reply
  • 2810 views

I am using this code from a tutorial on the web to add multiple records to a database. These records are linked to another table by ProductID, in other words there could be many records generated by this form but they will all have the same ProductID number coinciding with the one ProductID in the other table. Here is my issue, I need an update page for these records that will allow me to update existing records or add additional ones. I am new to using cfloop and was able to get the original code to function correctly but can't figure out how to code this for update. Any help would be appreciated.

Here is the code:

<cfif isdefined ("form.number")>
    <cfset getnumber = form.number + 1>
<cfelse>
    <cfset getnumber = 1>
</cfif>


<cfif getnumber GT 1>
    <cfset focusonme = " document.all.osform.CFSNumber" & getnumber & ".focus();">
<cfelse>
    <cfset focusonme = "document.all.osform.CFSNumber.focus();">
</cfif>

<cfoutput>

<script type="text/javascript">
        function dofocusthing(){
                        #focusonme#      

        }
        function submitform(){
                        document.all.osform.action = 'act_add.cfm';
                        document.all.osform.submit();
        }
        function getnewline(){
                        document.all.osform.action = 'add_products.cfm';
                        document.all.osform.submit();
        }
    </script>
</cfoutput>

<body onLoad="javascript:dofocusthing();">

<table width="80%" rules="none" align="center">
    <tr>
        <td>CFSNumber</td>
        <td>MfgPart</td>
        <td>Pack</td>
        <td>Pallet-Case</td>
        <td>Size</td>
    </tr>
    <cfoutput>
       

        <input type="hidden" name="number" value="#getnumber#">
      

        <cfloop from="1" to="#getnumber#" index="idx">
          

            <tr>
                <cfif isdefined ("form.CFSNumber#idx#")>
                    <td><input type="text" name="CFSNumber#idx#" value="#evaluate("form.CFSNumber#idx#")#"></td>
                <cfelse>
                    <td><cfinput type="text" name="CFSNumber#idx#"></td>
                </cfif>
                <cfif isdefined ("form.MfgPart#idx#")>
                    <td><cfinput type="text" name="MfgPart#idx#" value="#evaluate("form.MfgPart#idx#")#"></td>
                <cfelse>
                    <td><cfinput type="text" name="MfgPart#idx#"></TD>
                </cfif>
                <cfif isdefined ("form.Pack#idx#")>
                    <td><cfinput type="text" name="Pack#idx#" value="#evaluate("form.Pack#idx#")#"></td>
                <cfelse>
                    <td><cfinput type="text" name="Pack#idx#"></td>
                </cfif>
                <cfif isdefined ("form.PalletCase#idx#")>
                    <td><cfinput type="text" name="PalletCase#idx#" value="#evaluate("form.PalletCase#idx#")#"></td>
                <cfelse>
                    <td><cfinput type="text" name="PalletCase#idx#"></td>
                </cfif>
                <cfif isdefined ("form.Size#idx#")>
                    <td><cfinput type="text" name="Size#idx#" value="#evaluate("form.Size#idx#")#"></td>
                <cfelse>
                    <td><cfinput type="text" name="Size#idx#"></td>
                </cfif>


            </tr>
        </cfloop>
    </cfoutput>
    <tr>
        <td colspan="8"><input type="button" name="add" value="Add Line onClick="javascript:getnewline();"></td>
    </tr>
</table>
<table width="90%" align="center">
    <tr>
        <td> </td>
    </tr>
</table>

And the action page:

<cfloop from="1" to="#form.number#" index="idx">

    <cfset getCFSNumber = evaluate("form.CFSNumber" & idx)>

    <cfset getMfgPart = evaluate("form.MfgPart" & idx)>

    <cfset getPack = evaluate("form.Pack" & idx)>

    <cfset getPalletCase = evaluate("form.PalletCase" & idx)>

    <cfset getSize = evaluate("form.Size" & idx)>

    <cfquery name="addSubProducts" datasource="farmky">

        insert into SubProduct (CFSNumber,MfgPart,Pack,PalletCase,Size)

        values (#getCFSNumber#,'#getMfgPart#','#getPack#','#getPalletCase#','#getSize#')

    </cfquery>

</cfloop>

This topic has been closed for replies.

1 reply

A
Anonymous
January 21, 2012

I am trying to utilize another tutorial supplied code and came up with this

Form:

<cfquery datasource="farmky" name="getSubProducts">
SELECT *
FROM SubProduct
WHERE ProductID = #URL.ProductID#
</cfquery>

<form action="update_cfsnumb.cfm" method="post">
<cfoutput query="getSubProducts">
<!--- use #currentRow# variable to name fields --->
<input type="text" name="CFSNumber_#currentRow#" value="#CFSNumber#" />
<input type="text" name="MfgPart_#currentRow#" value="#MfgPart#" />
<input type="text" name="Pack_#currentRow#" value="#Pack#" />
<input type="text" name="PalletCase_#currentRow#" value="#PalletCase#" />
<input type="text" name="Size_#currentRow#" value="#Size#" />
<br />
</cfoutput>

<cfoutput>
<input type="hidden" name="numberOfFields" value="#getSubProducts.RecordCount#">
</cfoutput>
<input type="submit" value="Update" />
</form>

Action page:

<cfdump var="#FORM#">

<cfparam name="form.numberOfFields" default="0">
<cfloop from="1" to="#form.numberOfFields#" index="counter">
<cfset CFSNumber = FORM["CFSNumber_"& counter]>
<cfset MfgPart = FORM["MfgPart_"& counter]>
<cfset Pack = FORM["Pack_"& counter]>
<cfset PalletCase = FORM["PalletCase_"& counter]>
<cfset Size = FORM["Size_"& counter]>

<cfupdate datasource="farmky" formfields="ManufacturerImage,ProductImage,Manufacturer,ProductTitle,ProductDescription,Species,CatalogGroup,ProductType" tablename="Products">

</cfloop>

Two issues arise. First I can update existing fields but would also need to have the option of adding additional rows and have no idea how to do that. Secondly, when I execute this form I can an error stating that Manufacturer_1 does not exist, this is the first record retrieved and I anticipate that the same error will result with any field ending in _1,2,3 etc.. I can only assume that I have incorrectly set this up. Any thoughts?

D
Dan_Bracuk
Inspiring
January 21, 2012

I tend to use an approach similar to your second example but with a few differences.  On my form page, I make the primary key value part of the name of the form field.  Something like this:

select somethingID, somethingName, somthingAddress, etc

from blah blah blah

outputing from query:

<cfinput name = "name#somethingID#" value="#somethingName#">

<cfinput name = "address#somethingID#" value="#somethingAddress#">

etc

I process that something like this:

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

<cfscript>

if (left(ListElement, 4 is "name")) {

ThisID = mid(ListElement, 5, len(ListElement) -4);

ThisName = form[ListElement];

ThisAddress = form["address" & ThisID];

etc

</cfscript>

update yourTable

set somethingName = #ThisName#

, somethingAddress = #ThisAddress#

etc

where somethingID = #ThisID#

end of loop.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices