11 Replies Latest reply on Jan 28, 2012 6:37 AM by BKBK

    Using cfloop to update and/or add data?

    emartek1 Level 1

      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>

        • 1. Re: Using cfloop to update and/or add data?
          emartek1 Level 1

          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,S pecies,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?

          • 2. Re: Using cfloop to update and/or add data?
            Dan Bracuk Level 5

            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.

            • 3. Re: Using cfloop to update and/or add data?
              BKBK Adobe Community Professional & MVP

              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.

              • 4. Re: Using cfloop to update and/or add data?
                emartek1 Level 1

                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#','#Size#')

                    </cfquery>

                </cfloop>

                • 5. Re: Using cfloop to update and/or add data?
                  BKBK Adobe Community Professional & MVP

                  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:

                   

                  <input type="hidden" name="ProductID" value="#URL.ProductID#">

                   

                  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.

                   

                  <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 (CFSNumber,MfgPart,Pack,PalletCase,Size)

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

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

                  </cfquery>

                   

                  </cfloop>

                  • 6. Re: Using cfloop to update and/or add data?
                    emartek1 Level 1

                    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:

                     

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

                     

                    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?

                    • 7. Re: Using cfloop to update and/or add data?
                      BKBK Adobe Community Professional & MVP

                      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.

                       

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

                          UPDATE SubProduct

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

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

                      </cfquery>

                      • 8. Re: Using cfloop to update and/or add data?
                        emartek1 Level 1

                        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?

                        • 9. Re: Using cfloop to update and/or add data?
                          BKBK Adobe Community Professional & MVP

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

                           

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

                          SELECT *

                          FROM SubProduct

                          WHERE ProductID = 1234

                          </cfquery>

                           

                          <cfdump var="#getSubProducts#">

                           

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

                          • 10. Re: Using cfloop to update and/or add data?
                            emartek1 Level 1

                            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?

                            • 11. Re: Using cfloop to update and/or add data?
                              BKBK Adobe Community Professional & MVP

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

                               

                              <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

                                  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.

                               

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

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

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

                              </cfquery>