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

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
<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.
      } else { //Older Method
            field_area.innerHTML += "<li><input name='"+(field+count)+"' id='"+(field+count)+"' type='text' /></li>";


<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>
<input type="submit" name="Submit" value="Submit" />

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

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:
#form.dept_1# <br>
#form.dept_2# <br>
#form.dept_3# <br>
I get the following correct output:
So I know the fields names are being passed correctly.  There is just something wrong with my query.

Please help me fix this.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

weharlowAuthor 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

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#')#)
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]#')

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.
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

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


  <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]#')

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
weharlowAuthor 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
weharlowAuthor 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.
weharlowAuthor 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.
weharlowAuthor 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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.