18 Replies Latest reply on Sep 28, 2006 12:24 PM by Newsgroup_User

    looping insert

    Level 7
      What is wrong with this code and how can I fix it.

      I have a form with a loop to display all the options.

      Then the user makes thier selections (via checkboxes) I need the checked
      boxes to insert in the database with the email address. I keep getting that
      the form elements are not defined.

      The list works fine, I dumped the variables and they all appear correctly,
      but I am getting the looping insert wrong.

      Thanks.

      Insert:
      <cfif IsDefined('Update')>
      <cfloop index="i" from="1" to="#optionList.Recordcount#">
      <cfquery name="optout" datasource="mailer">
      INSERT INTO Optouttable (outoption)
      VALUES ('#Form.email#', '#Form.optionlist#')
      </cfquery>
      </cfloop>
      <cflocation url="success.cfm" addtoken="no">
      </cfif>

      Form:
      <cfset i = 0>
      <cfloop query="optionList">
      <table width="100%" border="0">
      <cfset i = i+1>
      <tr>
      <td width="19%"><div align="right">
      <input name="list#i#" type="checkbox" id="list#i#"
      value="#optionList.listName#">
      </div></td>
      <td width="81%">#optionList.listName#
      <input type="hidden" id="email" name="email"
      value="#URL.email#"></td>
      </tr>
      </table>
      </cfloop>


        • 1. Re: looping insert
          Scott Stroz Level 1
          In HTML forms, if a checkbox is not selected, the form variable is not passed when the form is submitted. So, you will need to check for the form fields existence before tryiong to use it.

          One way you can handle this is to name all checkboxes the same name. Then a comma delimited list for all items checked will be passed when the form is submitted. However, you would still check for the form fields existence, or do a <cfparam>, but you will only need to do it once, rather than once for each item.
          • 2. Re: looping insert
            Level 7
            Not 100% sure what you mean. Each check box has a unique name based on the
            loop. Box 1 is List1, Box 2 is List2, etc. Since the form is looping and
            list all the values from the optionList query, it should match with
            RecordCount I am using with the insert loop, correct?

            I added this to the loop insert to check to see if the box is there.

            <cfif IsDefined('Update')>
            <cfloop index="i" from="1" to="#optionList.Recordcount#">
            <cfif IsDefined("list#i#")>
            <cfif evaluate("list#i#") NEQ "">
            <cfquery name="optout" datasource="mailer">
            INSERT INTO Optouttable (outoption)
            VALUES ('#Form.email#', '#Form.optionlist#')
            </cfquery>
            </cfif>
            </cfif>
            </cfloop>
            <cflocation url="success.cfm" addtoken="no">
            </cfif>


            • 3. Re: looping insert
              Level 7
              What confuses me the most is that the error I am getting (and this has
              happened in past posts) is that #Form.email# is not defined when I go to
              insert.

              Inside of a hidden variable I have <input type="hidden" name="email"
              value="#Url.Email#"> it displays fine in the actual displayed form, but not
              when I click the submit button.

              Any ideas on why it seems to disappear??


              • 4. Re: looping insert
                Level 7
                Put <cfdump var="#form#"> at the top of your action page. This will
                show you all the form fields and their values being submitted.

                I suspect the problem is the #form.optionlist#. I do not see that field
                defined in the form.
                • 5. Re: looping insert
                  Level 7
                  I tried that and it gave me the form.email not defined. Although it IS
                  defined in the form inside the loop.

                  Thanks for the headsup on the option list. I am sure that would be next to
                  be a problem. #optionlist# is the value of the list#i#.

                  Ok. How can I enter the form.list#i# inside the values area of the insert
                  since it is also dynamic. Will CF freak if I have #form.list#i##?


                  • 6. Re: looping insert
                    Level 7
                    <quote>I tried that and it gave me the form.email not defined. Although
                    it IS defined in the form inside the loop.</quote>

                    So what fields showed up in the form dump. You did dump the entire
                    form, not just the one field correct. <cfdump var="#form#"> not <cfdump
                    var="#form.email#">.


                    <quote>Ok. How can I enter the form.list#i# inside the values area of
                    the insert since it is also dynamic. Will CF freak if I have
                    #form.list#i##?</quote>

                    Yes it will freak out. We have had this discussion before. My
                    preferred method is #form["list" & i]#. But you are going to run into
                    the undefined problem here because if a user does not check an checkbox
                    input that field is not returned and thus will be undefined.
                    • 7. Re: looping insert
                      Dan Bracuk Level 5
                      This thread is about a similar problem. Maybe you will see something helpful.

                      http://www.adobe.com/cfusion/webforums/forum/messageview.cfm?forumid=1&catid=3&threadid=11 98187&enterthread=y
                      • 8. Re: looping insert
                        Level 7
                        Now when I dump I am getting nothing.....I am sooo lost. Here is my form and
                        here is my insert. Please let me know what I am doing wrong.

                        My Form:
                        <form action="" method="get" name="showMeMy">
                        <cfoutput>
                        <cfset i = 0>
                        <cfloop query="optionList">
                        <table width="100%" border="0">
                        <cfset i == i>
                        <tr>
                        <td width="19%"><div align="right">
                        <input name="list#i#" type="checkbox" id="list#i#"
                        value="#optionList.listName#">
                        </div></td>
                        <td width="81%">#optionList.listName#
                        <input name="email" type="hidden" id="email"
                        value="#Url.email#" />#URL.email#</td>
                        </tr>
                        </table>
                        </cfloop>
                        <td><input name="Update" type="submit" id="Update" value="Update
                        Subscription List"></td>
                        </tr>
                        </table>
                        </cfoutput>
                        </form>

                        Here is my insert:
                        <cfif IsDefined('Update')>
                        <cfloop index="i" from="1" to="#optionList.Recordcount#">
                        <cfif IsDefined("list#i#")>
                        <cfif evaluate("list#i#") NEQ "">
                        <cfquery name="optout" datasource="mailer">
                        INSERT INTO Optouttable (email, optionlist)
                        VALUES ('#myemail#', '#form["list" & i]#')
                        </cfquery>
                        </cfif>
                        </cfif>
                        </cfloop>
                        <cflocation url="success.cfm" addtoken="no">
                        </cfif>


                        • 9. Re: looping insert
                          Level 7
                          Ok, I seemed to have solved the issue with the email by moving it outside
                          the loop, but still having no luck with the looping insert.

                          Now it just skipping the insert all together because the Form#i# is
                          apparently no existant. Even though if I look through the HTML source code,
                          it is defined and the element name and value are correct. This needs to be
                          done today and I am no where closer that when I stated.

                          The funny part is that I used the same coding as I did on another site of
                          mine and that one works perfectly.

                          I am down to this:

                          FORM:
                          <cfset i = 0>
                          <cfloop query="optionList">
                          <table width="100%" border="0">
                          <cfset i = i+1>
                          <tr>
                          <td width="19%"><div align="right">
                          <input name="list#i#" type="checkbox" id="list#i#"
                          value="#i#">
                          </div></td>
                          <td width="81%">#optionList.listName#</td>
                          </tr>
                          </table>
                          </cfloop>

                          INSERT
                          <cfif IsDefined('Update')>
                          <cfloop index="i" from="1" to="#optionList.Recordcount#">
                          <cfif IsDefined("Form.list#i#")>
                          <cfif evaluate("form.list#i#") NEQ "">
                          <cfquery name="optout" datasource="mailer">
                          INSERT INTO Optouttable (email, outoption)
                          VALUES ('#Form.email#', '#form["list" & i]#')
                          </cfquery>
                          </cfif>
                          </cfif>
                          </cfloop>
                          <cflocation url="success.cfm" addtoken="no">
                          </cfif>


                          • 10. Re: looping insert
                            Level 7
                            Have you put <cfdump var="#form#" at the top of your action page. It
                            would show you EVERYTHING that the is being submitted by the form and
                            EXACTLY how it is named as well as all the values submitted.

                            <cfinput name="list#i#" ...> is NOT going to create something named
                            "Form#i#" It is going to create something named "Form.list#i#". Now
                            you would need to evaluate that string or my preferred method,
                            #form["list" & i]#.

                            If you had a <cfdump var="#form#"> you would have seen just what was
                            submitted, a bunch of fields as below in a pretty blue table.

                            list1 = foo
                            list2 = bar
                            list3 = something
                            ect
                            ect
                            ect
                            • 11. Re: looping insert
                              Dan Bracuk Level 5
                              This is going to give you the same value for every checkbox, which is probably not your intent.

                              <cfloop query="optionList">
                              <input name="list#i#" type="checkbox" id="list#i#"
                              value="#optionList.listName#">



                              • 12. Re: looping insert
                                Scott Stroz Level 1
                                The one issue with doing:

                                <cfloop query="optionList">
                                <input name="list#i#" type="checkbox" id="list#i#"
                                value="#optionList.listName#">

                                Is that you will still need to check for each form field's existence before trying to use it.

                                One other option, and this is assuming the check-boxes are all related, is to name all the form fields the same:

                                <cfloop query="optionList">
                                <input name="myList" type="checkbox" id="list#i#"
                                value="#optionList.listName#">

                                When you have 2 form fields with the same name, and both contain values, they are passed to the action page as a comma delimited list.

                                Then on the action page, you could add <cfparam name="form.myList" default='' /> to the top, and then insert using a loop:

                                <cfloop list="#form.myList#" index="i">
                                ...
                                do stuff
                                ...
                                </cfloop>

                                • 13. Re: looping insert
                                  Level 7
                                  "Have you put <cfdump var="#form#" at the top of your action page. It would
                                  show you EVERYTHING that the is being submitted by the form and EXACTLY how
                                  it is named as well as all the values submitted."

                                  I tried that and posted this in an earlier post:

                                  "Now when I dump I am getting nothing......"

                                  'Now you would need to evaluate that string or my preferred method,
                                  "#form["list" & i]#."'

                                  When I enter <cfif evaluate ("#form["list" & i]#") NEQ ""> or <cfif evaluate
                                  (#form["list" & i]#) NEQ ""> I get this error:
                                  'Element list1 is undefined in a Java object of type class
                                  coldfusion.filter.FormScope referenced as'

                                  It refers me to the <cfif evaluate ("#form["list" & i]#") NEQ "">

                                  "If you had a <cfdump var="#form#"> you would have seen just what was
                                  submitted, a bunch of fields as below in a pretty blue table."

                                  I get the same error:
                                  Element list1 is undefined in a Java object of type class
                                  coldfusion.filter.FormScope referenced as

                                  Again, here is my form code:
                                  <form action="" method="get" name="showMeMy">
                                  <cfoutput>
                                  <b>#URL.email#</b>

                                  Please check the boxes of the email types you would like to unsubscribe
                                  from: </td>

                                  <cfloop query="optionList">
                                  <cfset i = i+1>
                                  <input name="list#i#" type="checkbox" id="list#i#"
                                  value="#optionList.listid#">
                                  optionList.listName#
                                  </cfloop>
                                  input name="email" type="hidden" id="email" value="#URL.email#" />
                                  input name="Update" type="submit" id="Update" value="Update Subscription
                                  List">
                                  </cfoutput>
                                  </form>

                                  What is going wrong in my list to not pass anything?

                                  If nothing, here is my insert code block...what is wrong there?

                                  <cfif IsDefined('Update')>
                                  <cfloop index="i" from="1" to="#optionList.Recordcount#">
                                  <cfif evaluate (#form["list" & i]#) NEQ "">
                                  <cfquery name="optout" datasource="mailer">
                                  INSERT INTO Optouttable (email, outoption)
                                  VALUES ('#URL.email#', '#form["list" & i]#')
                                  </cfquery>
                                  </cfif>
                                  </cfloop>
                                  <cflocation url="success.cfm" addtoken="no">
                                  </cfif>


                                  • 14. Re: looping insert
                                    Level 7
                                    This is actually what I have. And the other version was the same but I had
                                    to change the value. Itstill game my value the unique value and the loop did
                                    give me unique names:

                                    <cfset i = 0>
                                    <cfloop query="optionList">
                                    <cfset i = i+1>
                                    <input name="list#i#" type="checkbox" id="list#i#"
                                    value="#optionList.listid#">optionList.listName#
                                    </cfloop>


                                    • 15. Re: looping insert
                                      Level 7
                                      I found a great simple tutorial on how to do this with a multiple select box
                                      and I have done this with file upload fields, but check boxes are a pain.
                                      Is there ANYWHERE a coldfusion version of a tutorial or example?


                                      • 16. Re: looping insert
                                        Level 7
                                        <form action="" method="get" name="showMeMy">


                                        This is the first time you have showed us the <form ...> tag. Your
                                        method is "get". This means that all values on the action page will be
                                        in the URL scope. So do <cfdump var="#url#">. If you changed the
                                        method to "post", then the values will be in the form scope. <cfdump
                                        var="#form#>
                                        • 17. Re: looping insert
                                          Level 7
                                          evaluate ("#form["list" & i]#")

                                          You do not do both evaluate and array notation, you do one or the other.

                                          Either

                                          evaluate("form.list#i#") <!--- using the evaluate function --->

                                          OR

                                          #form["list" & i]# <!--- using array notation of complex variables types
                                          --->
                                          • 18. Re: looping insert
                                            Dan Bracuk Level 5
                                            There is an example in the thread I referred you to at 09/28/2006 04:16:13 PM