We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

Looping thru insert query where the values are from dynamic input fields

weharlow
weharlow asked
on
Medium Priority
207 Views
Last Modified: 2013-12-24
I have a javascript that creates dynamic input fields (I did not write this js).  The fields are named dynamically dept_1, dept_2, dept_3, etc.  However, the cfquery only inserts the first field (dept_1) into the database.  Here are the js, HTML and the coldfusion:

Page 1
JAVASCRIPT
<script language="Javascript" type="text/javascript">
<!--
//Add more fields dynamically.
function addField(area,field,limit) {
      if(!document.getElementById) return; //Prevent older browsers from getting any further.
      var field_area = document.getElementById(area);
      var all_inputs = field_area.getElementsByTagName("input"); //Get all the input fields in the given area.
      //Find the count of the last element of the list. It will be in the format '<field><number>'. If the
      //            field given in the argument is 'friend_' the last id will be 'friend_4'.
      var last_item = all_inputs.length - 1;
      var last = all_inputs[last_item].id;
      var count = Number(last.split("_")[1]) + 1;
      
      //If the maximum number of elements have been reached, exit the function.
      //            If the given limit is lower than 0, infinite number of fields can be created.
      if(count > limit && limit > 0) return;
       
      if(document.createElement) { //W3C Dom method.
            var li = document.createElement("li");
            var input = document.createElement("input");
            input.id = field+count;
            input.name = field+count;
            input.type = "text"; //Type of field - can be any valid input type like text,file,checkbox etc.
            li.appendChild(input);
            field_area.appendChild(li);
      } else { //Older Method
            field_area.innerHTML += "<li><input name='"+(field+count)+"' id='"+(field+count)+"' type='text' /></li>";
      }
}

//-->
</script>

HTML
<body>
<form action="Page2.cfm" method="POST" name="frm">
  <ul id="dept_area">
  <input type="button" value="Add Dept Number Field" onclick="addField('dept_area','dept_',30);" / >
  <li><input type="text" name="dept_1" id="dept_1" /></li>
  </ul>
<input type="submit" name="Submit" value="Submit" />
</form>
</body>

Page 2
COLDFUSION
<cfloop from="1" to="#ListLen(form.dept_1)#"  index="i">
        <cfquery name="MyInsertQuery" datasource="MyDatasource">
           insert into test (box)
           values  (#evaluate('form.dept_#i#')#)
        </cfquery>
</cfloop>

I can dynamically create 3 fields and insert the following data:  dept_1 = 100, dept_2 = 200, dept_3 = 300
Then if do a cfoutput on page2 like the following:
<cfoutput>
#form.dept_1# <br>
#form.dept_2# <br>
#form.dept_3# <br>
</cfoutput>
I get the following correct output:
100
200
300
So I know the fields names are being passed correctly.  There is just something wrong with my query.

Please help me fix this.
Comment
Watch Question

Author

Commented:
P.S.  I need to clarify what I want this to do.  I want a new record entered for each dynamic field so the database would look like this from the above example:

Column:    Box
Record 1:  100
Record 2:  200
Record 3:  300

thanks!
try something like ...

<cfloop list="#form.FIELDNAMES)#" index="curField">
      <cfif left(variables.curField, 4) IS "dept">
        <cfquery name="MyInsertQuery" datasource="MyDatasource">
           insert into test (box)
           values  (#evaluate('form.#curField#')#)
        </cfquery>
      </cfif>
</cfloop>
Ack, Evaluate()?!?

Bracket notation is not only faster, but easier to read, easier to maintain, and less likely to result in some sort of oddball error.

Weharlow, from looking at your code it would seem that you're not passing anything to the CFM page that would indicate how many dynamic fields to expect.  However, it does look like there's an upper limit of 30 defined.  So, keeping that in mind, here's what I'd suggest.

<cfquery name="insertdepartment" datasource="dsn">
  <cfloop from="1" to="30" index="i">
    <cfif StructKeyExists(form, "dept_" & i)>
      INSERT INTO test (box)
               VALUES ('#form["dept_" & i]#')
    </cfif>
  </cfloop>
</cfquery>

Yes, the loop is *inside* the query.  That results in a single connection to the database for all your insert queries rather than a connection for each of your queries.  Just remember that if you ever change the upper limit in your form page, you'll also need to revisit this CFM template and change it there as well.  Even better would be to put it in a hidden form field and referring to that in your "to" attribute of the cfloop.  Then you'd only have to change it in one place.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
I think we are almost there.  However, I am now getting the following error when it is actually trying to insert into the Access database:

Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Missing semicolon (;) at end of SQL statement.
The error occurred in C:\Inetpub\wwwroot\budget\page2.cfm: line 54
52 :       VALUES ('#form["dept_" & i]#')
53 :     </cfif>
54 :   </cfloop>
55 : </cfquery>
56 :
SQL -    INSERT INTO test (box) VALUES ('100') INSERT INTO test (box) VALUES ('200') INSERT INTO test (box) VALUES ('300')

When I add a semi colon to the end of the insert statement I then get an error that it "found characters at the end of the SQL statement."  So I can't win in this situtation.  Any ideas as to why it's upset at the syntax and what would fix it?

Other than that when you look at the SQL statement in the error above it looks like it is doing exactly what it needs to do.

Thanks,
Winona

  <cfloop from="1" to="30" index="i">
    <cfif StructKeyExists(form, "dept_" & i)>
      <cfquery name="insertdepartment" datasource="dsn">
        INSERT INTO test (box)
               VALUES ('#form["dept_" & i]#')
      </cfquery>
    </cfif>
  </cfloop>

Author

Commented:
WOOHOO!!  THANKS SO MUCH!!!!!  This works absolutely perfectly!!!  
Sorry about the multiple INSERT query problem.  Had I known you were using Access, I would have suggested placing the <cfquery> tags inside the loop.  Bummer I didn't get at least an assist.  :p

Author

Commented:
Oh man, I'm so sorry.  I thought he gave the first part of the answer too (my fault, I'm in such a hurry)...how can I give you points now?  I would be glad to give you 500 too, that's how much it meant to me to get the answer.  Please let me know who to contact to give you points too.  If you don't know, I'll go look around myself to see how to do it.

Author

Commented:
Hang loose JeffHowden.  I just sent a question to Community support about splitting the accept after accepting and giving you 500 points too.  Hopefully I'll get a reply soon.  Again I apologize.  
No, don't worry about it.  Just be more careful next time.  I'm not here for the points.

Author

Commented:
Nope, gotta do it if I can.  I'm going to need a lot more answers from you people in the future and I don't want anyone thinking I'm a chump.  So, you see I'm not being altruistic - just covering my back. :)
Do what you have to, but no need for it, imo.  Just the fact that you considered it is good enough for me.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.