Using cfloop to update and/or add data?

Report · Jan 20, 2012

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:

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

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

            </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:

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

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

</cfquery>

</cfloop>

Report · Jan 20, 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>

Action page:

</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?

Report · Jan 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:

etc

I process that something like this:

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.

Report · Jan 23, 2012

emartek1 wrote:
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?

Comments:

1) Using the associative array notation FORM["CFSNumber_"& counter] is in fact better than using the evaluate() function.

2) When you use cfupdate, the names listed in the formfields attribute must match the names of actual fields in the form. In addition, one of those field names must be the primary key of the database table you wish to update. That is the likely reason for the error message.

Report · Jan 23, 2012

How do I 'pass' the primary key? When I updated the code like shown below I am still getting the error message. My cfdump clause shows the ProductID field as 30,30,30. When I execute the template I still get the error message listed above but if I refresh it it will run through the whole gambit of all the fields ending in _#. How can I make this work?

update SubProduct (ProductID,CFSNumber,MfgPart,Pack,PalletCase,Size)

set ('#ProductID#','#CFSNumber#','#MfgPart#','#Pack#','#PalletCase#','#Size#')

</cfquery>

</cfloop>

Report · Jan 24, 2012

emartek1 wrote:
How do I 'pass' the primary key? When I updated the code like shown below I am still getting the error message. My cfdump clause shows the ProductID field as 30,30,30. When I execute the template I still get the error message listed above but if I refresh it it will run through the whole gambit of all the fields ending in _#. How can I make this work?
<cfset ProductID = FORM["ProductID">
<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]>
<cfquery name="updSubProducts" datasource="farmky">
        update SubProduct (ProductID,CFSNumber,MfgPart,Pack,PalletCase,Size)
        set ('#ProductID#','#CFSNumber#','#MfgPart#','#Pack#','#PalletCase#','#Si ze#')
    </cfquery>
</cfloop>

In a previous post, you called the database table Products. Now, you call it SubProduct. This is confusing.

I will assume the table is SubProduct, and that its primary key is ProductID. The primary key comes in via the URL (see your second post). You could then forward it to the action page by means of a hidden form field. Hence add the following tag to the form page:

Now to the action page. You have used single quotes for all the update values, which leads me to a question. Are all the datatypes varchars? If not, remove the quotes for columns that are of numeric type.

UPDATE SubProduct (CFSNumber,MfgPart,Pack,PalletCase,Size)

SET ('#CFSNumber#','#MfgPart#','#Pack#','#PalletCase#','#Size#')

WHERE productID = <cfqueryparam value="#FORM['ProductID']#" cfsqltype="CF_SQL_VARCHAR">

</cfquery>

</cfloop>

Report · Jan 24, 2012

BKBK, thank you for taking the time to help me with this. I did refer to the table as Products in an earlier post, sorry for the confusion. The table I am working on now is SubProduct.

Okay, stupid me was updating and uploading the wrong files. Now all I am getting is that there is a 'syntax error in UPDATE statement'. This is the code I am using:

UPDATE SubProduct (CFSNumber,MfgPart,Pack,PalletCase,Size)

SET ('#CFSNumber#','#MfgPart#','#Pack#','#PalletCase#','#Size#')

WHERE ProductID = <cfqueryparam value="#FORM["ProductID"]#" cfsqltype="CF_SQL_VARCHAR">

</cfquery>

ProductID is a number but all the other fields are VARCHARS, I have tried this with CF_SQL_INTEGER instead but still get the same syntax error. Any ideas?

Report · Jan 24, 2012

emartek1 wrote:
<cfquery name="updSubProducts" datasource="farmky">
    UPDATE SubProduct (CFSNumber,MfgPart,Pack,PalletCase,Size)
    SET ('#CFSNumber#','#MfgPart#','#Pack#','#PalletCase#','#Size#')
    WHERE ProductID = <cfqueryparam value="#FORM["ProductID"]#" cfsqltype="CF_SQL_VARCHAR">
</cfquery>

We both missed the obvious mistake of using the Insert syntax for Update.

UPDATE SubProduct

SET CFSNumber='#CFSNumber#', MfgPart = '#MfgPart#', Pack = '#Pack#', PalletCase= '#PalletCase#', Size='#Size#'

WHERE ProductID = <cfqueryparam value="#FORM["ProductID"]#" cfsqltype="CF_SQL_INTEGER">

</cfquery>

Report · Jan 26, 2012

Okay, that definitely helped. I can now process the template and it goes through without error. The only problem is it changes everything to the last entry. In other words, my test template has three entries. The CFS
Number, MfgPart and Size are unique for each entry. Once I process the template, even if I don't make any changes it changes all three entries to the same thing, matching the last entry. What would cause this?

Report · Jan 26, 2012

Start at the beginning, the query. Run the following on a test page, using the product ID you've been using so far:

SELECT *

FROM SubProduct

WHERE ProductID = 1234

</cfquery>

Are the values of CFS Number, MfgPart and Size as unique as you expect?

Report · Jan 27, 2012

No there not...each column is identical. Even if I change the data in my update form it reverts all the columns to display the same as row three, the last row. What did I do?

Report · Jan 28, 2012

I think I know what is causing the problem. Take a look at this code:

UPDATE SubProduct

SET CFSNumber='#CFSNumber#', MfgPart = '#MfgPart#', Pack = '#Pack#', PalletCase= '#PalletCase#', Size='#Size#'

WHERE ProductID = <cfqueryparam value="#FORM["ProductID"]#" cfsqltype="CF_SQL_INTEGER">

</cfquery>

</cfloop>

This suggests inefficiency! You are very likely doing a lot of overwriting in the database.

The counter implies that the values of the variables CFSNumber, MfgPart, etc. are changing dynamically. This means that are you are repeatedly updating the row corresponding to one product ID.

Your original idea of an insert query is better. However, you should realize that you will end up with multiple rows per product ID.

INSERT INTO SubProduct (ProductID,CFSNumber,MfgPart,Pack,PalletCase,Size)

VALUES (#ProductID#,'#CFSNumber#','#MfgPart#','#Pack#','#PalletCase#','#Size#')

</cfquery>

Adobe Community

Using cfloop to update and/or add data?