9 Replies Latest reply on Oct 3, 2006 4:40 AM by Newsgroup_User

    looping insert...To be continued

    Level 7
      Sorry, but the post was getting too long to follow so I thought I would
      start it over and ask for help piece by piece so I can learn this:
      I viewed the created html from Coldfusion and my list has unique input names
      from the looping list (List1, List2, etc) and the values are unique due to
      the recordset values of the listsid.

      Ok, using cfdump var="#form#" I am getting nothing in the output.
      If I dump var = "#email#", I get the correct email addresse twice.
      I don't know how to dump the values of the lists because of the fact that
      they are incrementing dynamically based on the loop. I should figure out
      that to see if the numbers are correctly apprearing.

      Here is my form:
      <form action="" method="get" name="showMeMy">
      <cfoutput>
      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>
      input name="email" type="hidden" id="email" value="#URL.email#" />
      input name="Update" type="submit" id="Update" value="Update Subscription
      List">
      </cfoutput>
      </form>


        • 1. Re: looping insert...To be continued
          Dan Bracuk Level 5
          A couple of general hints on loops.
          If you want query values, you have to specify a row number. The syntax is
          queryname.fieldname[rownumber]

          If you want list elements, you need to use the ListGetAt function.

          It's probably worth your while to read the cfml reference manual for the cfloop tag. If you don't have one, the internet does.

          • 2. Re: looping insert...To be continued
            Level 7
            Here is my form:
            <form action="" method="get" name="showMeMy">

            There are two types of forms, get and post. Depending on which type you
            pick determines what scope ColdFusion puts the results into.

            method="get" = URL scope, use <cfdump var="#URL#">
            method="post" = FORM scope, use <cfdump var="#FORM#">

            The form tag you are showing in these messages has method="get", you
            need to be using URL scope variables on your action page.
            • 3. Re: looping insert...To be continued
              Level 7
              Thanks. I forgot i changed that. When I dumped #Form#, i got:

              struct
              EMAIL wmkolcz@avemarialaw.edu
              FIELDNAMES LIST1,LIST2,EMAIL,UPDATE
              LIST1 9999
              LIST2 1
              UPDATE Update Subscription List


              That is what I want to insert the value of the list elements (9999,1) and
              the email address.

              Seems the form is working fine. Now I have to figure out how to insert it
              into the database, looping 1 list column and 1 email address per.

              Here is what I had:
              <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#') <---I know that is wrong
              </cfquery>
              </cfif>
              </cfloop>
              <cflocation url="confirm.cfm" addtoken="no">
              </cfif>

              What should I look at next.


              • 4. Re: looping insert...To be continued
                Level 7
                <cfif evaluate("form.list#i#") NEQ ""> <!--- you do what you need here --->
                <cfquery name="optout" datasource="mailer">
                INSERT INTO Optouttable (email, outoption)
                VALUES ('#URL.email#', 'form.list#i#') <--- do what you did in the cfif
                statement #evaluate("form.list#i#")#--->
                </cfquery>
                • 5. Re: looping insert...To be continued
                  Level 7
                  This is why I am confused. I am getting this error in the evaluate:
                  Variable form.list1 is undefined.

                  I have used the same syntax in a different looping insert and it worked
                  fine.


                  • 6. Re: looping insert...To be continued
                    Level 7
                    Wally

                    Your next problem is going to be the age old HTML/HTTP issue of check
                    boxes not existing if they are not selected. This is just the way the
                    standard works, and the way you have your loop built it is going to
                    expect all the check boxes to be checked which I would not assume to
                    always be the case.

                    There are many solutions to this problem; <cfparam> all the check box
                    fields or check isDefined or structKeyExists before using any of the
                    value are two common ones.

                    The method I like to use is let form.fieldNames tell me what was passed.

                    To modify your example I would do something like this.

                    <cfloop list="#form.fieldnames#" index="field">
                    <!--- If the field is a list field --->
                    <cfif left(field,4) EQ "LIST")>
                    <cfquery ...>
                    INSERT INTO ...
                    VALUES ('#form.email#', #form[field]#
                    </cfquery>
                    </cfif>
                    </cfloop>

                    Look MA not EVALUATE.... :-)
                    • 7. Re: looping insert...To be continued
                      Level 7
                      Ok, i am beginning to see. I am looking at the cfloop list in the docs.
                      So I need to replace the 'form.fieldname' with the actual field 'list'? How
                      can I replace that with the name of 'list' since it is incrementing from the
                      form?

                      Do I need to rename my fieldname in the form from list#i# to something
                      static?


                      • 8. Re: looping insert...To be continued
                        Level 7
                        Ok, i am beginning to see. I am looking at the cfloop list in the docs.
                        So I need to replace the 'form.fieldname' with the actual field 'list'?
                        How can I replace that with the name of 'list' since it is incrementing
                        from the form?

                        Do I need to rename my fieldname in the form from list#i# to something
                        static?


                        NO, you just need to put all the pieces together.

                        Look at the <cfdump var="#form#"> you posted earlier.
                        struct
                        EMAIL wmkolcz@avemarialaw.edu
                        FIELDNAMES LIST1,LIST2,EMAIL,UPDATE
                        LIST1 9999
                        LIST2 1
                        UPDATE Update Subscription List


                        See the third line, there is a "field" named "FIELDNAMES" and it
                        contains a list of all the fields that where submitted by the form page.

                        <cfloop list="#form.fieldnames#" index="field">
                        This is going to loop over the list in the form.fieldnames key
                        [LIST1,LIST2,EMAIL,UPDATE] and put each list value into the variable
                        "field".

                        <cfif left(field,4) EQ "LIST">
                        This checks the value of the variable "field" in each loop to see if it
                        starts with the string "LIST" thus this is one of the list check box fields.

                        VALUES ('#form.email#', #form[field]#)
                        This inserts the values of form.email and the list field into the
                        database. In each loop itteration #form[field] is going to resolve to
                        form[LIST1] and form[LIST2] which in turn will resolve to the following SQL

                        VALUES ('#form.email#', #form[field]#

                        VALUES ('wmkolcz@avemarialaw.edu', 1)
                        VALUES ('wmkolcz@avemarialaw.edu', 9999)
                        • 9. Re: looping insert...To be continued
                          Level 7
                          Now I get it. I had one small error that was causing a problem, but I fixed
                          that. And it works like a charm.

                          Thank you very very much for being patient with me and helping me. Now I
                          understand it.