[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 190
  • Last Modified:

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
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.
0
weharlow
Asked:
weharlow
  • 6
  • 4
  • 2
2 Solutions
 
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

thanks!
0
 
rob_lorentzCommented:
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>
0
 
JeffHowdenCommented:
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.
0
New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

 
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.

Thanks,
Winona
0
 
rob_lorentzCommented:

  <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>
0
 
weharlowAuthor Commented:
WOOHOO!!  THANKS SO MUCH!!!!!  This works absolutely perfectly!!!  
0
 
JeffHowdenCommented:
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
0
 
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.
0
 
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.  
0
 
JeffHowdenCommented:
No, don't worry about it.  Just be more careful next time.  I'm not here for the points.
0
 
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. :)
0
 
JeffHowdenCommented:
Do what you have to, but no need for it, imo.  Just the fact that you considered it is good enough for me.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now