Need to insert multiple form rows into SQL using coldfusion

The form has a button on the end of each line to add as many more lines as there are unique books to add.

Can you post the code that adds new lines?

<script language=javascript>function newLine(){        
var table=document.getElementById("tableData")    
lastLine=table.rows.length-1    
var oRow = table.insertRow();    
for(i=0;i<table.rows(lastLine).cells.length;i++)    
{        var oCell = oRow.insertCell();        
var data=table.rows(lastLine).cells(i).innerHTML      
 oCell.innerHTML=data    }    
 }</script>
<cfform name="booksearch" method="post" action="sendlist2.cfm">
<TABLE class=table1 ID="tableData" name="tableData">
<THEAD> <TR><TH colSpan=7>New Surplus Book List</TH></TR></THEAD>
<TBODY>
<TR><TH>ISBN</TH> <TH>Qty</TH> <TH>Condition</TH>  <TH>Title</TH>  <TH>Copyright&nbsp;Year</TH><TH>Publisher</TH><TH><small>Click to add lines</small></TH></TR>
<TR>
<TD><LABEL for=isbn><cfinput type="text" name="isbn" id="isbn"><BR></LABEL></TD>
<TD><LABEL for=Quantity> <CFINPUT id=Quantity class=f-name tabIndex=1 type=text name=Quantity><BR></LABEL></TD>
<TD><LABEL for=Condition><SELECT id=Condition tabIndex=4 name=Condition><OPTION selected>Select..</OPTION><OPTION>USED</OPTION> <OPTION>Binder</OPTION> </SELECT></TD>
<TD><LABEL for=Title> <cfinput type="text" name="title" id="title" readonly="true"><BR></LABEL></TD>
<TD><LABEL for=Copyright> <cfinput type="text" name="copyright" id="copyright" readonly="true"><BR></LABEL></TD>
<TD><LABEL for=Publisher> <cfinput type="text" name="publisher" id="publisher" readonly="true"><BR></LABEL></TD>
<TD><CFinput id=button1 style="WIDTH: 76px; HEIGHT: 24px" type=button size=28 value="Add Line" name=button1 onclick="newLine()"></TD></TR>
</TBODY></TABLE>
<table><TR><TD>  <DIV class=f-submit-wrap><INPUT class=f-submit tabIndex=12 value="Upload List" type=submit><BR></DIV></TD></TR></TD></TR></table></cfform>

what you can also do is set the counter on the form to count the number of books, so once you arrive on the action page you will no exactly the number of times you need to interact through the loop.

And then just set the local variable  to evaluate the form .

For example, <cfset lBookName = Evaluate("form.bookname")>

var data=table.rows(lastLine).cells(i).innerHTML      
 oCell.innerHTML=data  

That technique is good for html, but not form fields.  Reason is it reuses the same field names. If there are 3 lines added

       <input name="publisher" ....>
       <input name="publisher" ....>
       <input name="publisher" ....>
        ...

When the form is posted, all of the values will be glommed together as one big string separated by commas.  Making it impossible to reliably separate the values even with list functions.

          Publisher ABC,The ABC,PublisherEFG,PublisherXYZ

You need to adjust the function to generate unique names for each set of form fields.

How can I do that? This is a new concept to me.

I can help with the CF processing. But this first part is a better question for the javascript zone.  

I'm not a js expert, but my approach would be to use the row number as a suffix. So your 1st set of fields would all end in "_1"

     <CFINPUT id="Quantity_1" name="Quantity_1"  ...>
     <SELECT id="Condition_1" name="Condition_1" ..>

and keep the total rows in a hidden field

         <input type="hidden" id="totalRows" name="totalRows" value="1">

In your javascript code, get the last row number ie 1, 2, 3 and as you append the new cells, do a replace() so the new fields all have unique names. Finally, increment the "totalRows" value.

Sounds like we're getting close. I am not sure how to proceed javascript-wise. Can this thread cross-reference at the javascript zone?

click "request attention"

ask moderator to add it to the javascript zone

Ok. I requested cross-zoning it.

_agx_ and erikTsomik have given the correct way to do this. Now you just need to be able to add the form fields. See this page they give a great explanation.

http://www.randomsnippets.com/2008/02/21/how-to-dynamically-add-form-elements-via-javascript/

I did not read your post in great detail but in general cases like this - do something like:

On the action page you can test how many records you have by the listlen of(form.ISBN).
So you can then:
<cfloop from="1" to="#listlen(form.ISBN)#" index="ii">
      insert into table
      (isbn,quantity)
      values
      ('#trim(listgetat(form.ISBN,ii))#','#trim(listgetat(form.quantity,ii))#' et cetera ...
</cfloop>
you do need to protect from one or more of your form fields not having the same listlen as the others - like if form.isbn has a length of 3 and quantity a length of 2 - you will error.
you can do a try catch or better I guess would be to do a if listlen(form.whatever) gte ii - then go ahead and insert - if not then break out of the loop
like:
<cfloop from="1" to="#listlen(form.ISBN)#" index="ii">
      <cfif listlen(form.quantity) gte ii>
      insert into table
      (isbn,quantity)
      values
      ('#trim(listgetat(form.ISBN,ii))#','#trim(listgetat(form.quantity,ii))#'
      <cfelse>
            <CFBREAK>
      </cfif>
</cfloop>
You would need to ask if all your form fields have a length of ii as well!

BUT - probably a better method would be to name your form fields unique names such as isbn_1 - coinciding with a counter - as others above have suggested.
The only problem with that is your script is not friendly towards doing that.
One could search for a new script that is setup to do that.
Your script is simply copying a row - you'd need one that writes a row so that a unique name can be written.

good luck ...

The link posted by Rick is very good, except I *think* the javascript needs some tweaking.   I don't believe it is incrementing the field name with a 1,2,3... counter.

The field name is MyInputs[]  ... what's with the brackets in the field name?  That's the second time in the last week I have seen that.. .it is new?

In any case, here's an adjustment to the javscript from Rick's post that will increment the counter on the field name as well as the prompt...

var counter = 1;
var limit = 3;
function addInput(divName){
     if (counter == limit)  {
          alert("You have reached the limit of adding " + counter + " inputs");
     }
     else {
          var newdiv = document.createElement('div');
          newdiv.innerHTML = "Entry " + (counter + 1) + " <br><input type='text' name='myInputs" + counter + "'>";
          document.getElementById(divName).appendChild(newdiv);
          counter++;
     }
}

Select allOpen in new window

I am sort of at a loss as far as how to assemble it all. I have raised the point value.

i understand
actually - if you look at the post I made above - you'll see that you do not need to make any changes to your form - just the action page which I described.
just try it - you'll see how slick it is and easy to use.
the ONLY thing to worry about is leaving a field blank on the form page - which can be dealt with no problem.
the first step is to try it!
If you need help implementing - just ask ...

You could do away with the JavaScript all together is you did not mind you page refreshing each time they added a line.

You need to modify the code part of the function to match your row of input tags...

In this function...

var counter = 1;
var limit = 3;
function addInput(divName){
     if (counter == limit)  {
          alert("You have reached the limit of adding " + counter + " inputs");
     }
     else {
          var newdiv = document.createElement('div');
          newdiv.innerHTML = "Entry " + (counter + 1) + " <br><input type='text' name='myInputs" + counter + "'>";
          document.getElementById(divName).appendChild(newdiv);
          counter++;
     }
}


You would change this line...

newdiv.innerHTML = "Entry " + (counter + 1) + " <br><input type='text' name='myInputs" + counter + "'>";


To hold all the input tags (not cfinput) of one row in your form....

The "Entry 1"  text at the beginning is just a label for the line, you can remove it.

So must create an <input...> tag for each field you want in your row.

The name of the input tag, needs to be like show with a " + counter..." after it.  That's where the 1, 2, 3 after the name comes in...


newdiv.innerHTML = "<input type='text' name='Name" + counter + "'>" +
    "<input type='text' name='Email" + counter + "'>" +
    "<input type='text' name='Phone" + counter + "'>"

That will create 3 input tags  Name5, Email5 and Phone5  ... assuming that 5 is the next row you need.

Make sure to include an other HTML tag for formatting, such as <TD> or <div> or whatever.

Will do! I will get into it today or tomorrow and let you know!

IMO, you should use a row counter on each field.  That will make it much easier to associate fields from the same record, manage fields that are left blank, highlight fields with errors (if so desired) and repopulate fields with the user's input if there is an error (that is, if an error occurs you can show the user's input rather than clearing the fields with the default/database values)

well - the code I posted does all that!
and all soulsinger need do is follow the simple instructions in my last post.

What if the user enters a value and changes his mind, and remove it?

not only could you then add some js validation - but if you look at the action page code it is protected ...

> not only could you then add some js validation

That would help IF the field was required.  But if you want to allow the field to be option, it could be a problem..

> but if you look at the action page code it is protected

I did look at the action page, it is protected from throwing an error, but not from confusing a field from 1 with row 2.


Also, what if the user enters a comma in the field?

Company Name:   ACME, Inc.

That will also be a problem.

gd - you have too much time on your hands ...
:)

i wasn't speaking of a required field but some filling in of the field with '&nbsp;' again if blank - probably onBlur

the confusing of a row with another because of leaving values blank is a concern, but may not be a problem.
The ONLY time would be a problem would be if the person deletes everything in a field - including the &nbsp;.
But onblur that can be fixed with js.
see what i just said above.

again - as far as commas go - just remove or replace them with js.
replace would be if you want them back again you can replace the replacement string with a comma again in the action page loop.

and a simple note as to HOW to fill out the form would be very usefull.

> gd - you have too much time on your hands

Lol, I guess so.   I know this is not your question, but really, I am trying to convince you to update your approach.  Using row numbers on fields is commonly used and a very reliable method.   It's not hard, you should try it.   Doing things like putting in fake spaces, using js on every field, stripping out commas that users enter is much more work!

as i said way up above - i agreed that the best method was to add row numbers.
but - you need to realize that i needed to prove that what i've been describing here is a viable method.
it is not hard either.
and this was the starting point of soulsinger.
after reading the posts i concluded that it would be easier to make the changes i proposed than a js rewrite.

soulsinger if your page is on the public internet, can you provide a link?  I'd like to see how well it holds up..

It is actually on a closed system that is internal for inventory.

ok, I suggest testing it very well

 entries with commas, blank fields, enter values and clear them, enter some fields for a row and leave other blank, leave an entire row empty

I have a cfc bound to the form. It populates every field except quantity based on the first field. Quantity defaults to 1. It's functional. They may not even need it this way but they wanted a mockup.