Solved

Need to insert multiple form rows into SQL using coldfusion

Posted on 2011-02-25
33
827 Views
Last Modified: 2012-05-11
I have a book inventory system that works well while entering one book into the system at a time. I am trying to modify it so it can send multiple records into the database. HEre's what I have so far:

<cfparam name="form.fmtISBN" default="">
<cfparam name="form.Quantity" default="">
<cfparam name="form.Condition" default="">
<cfparam name="form.Title" default="">
<cfparam name="form.Copyright" default="">
<cfparam name="form.Publisher" default="">
<cfparam name="session.username" default="">
<cfparam name="session.school" default="">
<cfparam name="session.district" default="">
<cfparam name="session.name" default="">
<cfparam name="session.email" default="">
<cfparam name="session.Phone" default="">
<cfparam name="session.schoolname" default="">
<cfparam name="session.schooldistrict" default="">
<cfparam name="session.Contact_Name" default="">
<cfparam name="session.Contact_Phone" default="">
<cfparam name="session.Contact_Email" default="">
<!---<cfdump var="#form#"><cfabort>--->
<!---  Retrieve form values from FIELDNAMES CF variable and loop through them --->
<cfloop list="#form.FIELDNAMES#" delimiters="," index="ISBN">
<cfoutput>
<cfquery name="sendlist" datasource="#DSN#">
INSERT INTO
booklist
(ISBN,
Quantity,
Condition,
Title,
CY_Year,
Publisher,
username,
school,
district,
Contact_Name,
Contact_Phone,
Contact_email)
VALUES
(
'#form.ISBN#',
'#form.quantity#',  
'#form.condition#',
'#form.Title#',
'#form.Copyright#',
'#form.publisher#',
'#session.username#',
'#session.schoolname#',
'#session.schooldistrict#',
'#session.name#',
'#session.phone#',
'#session.email#',
</cfquery></cfoutput>
</cfloop>

Can anyone shed light on this?

Thanks!
0
Comment
Question by:soulsinger
  • 9
  • 9
  • 7
  • +3
33 Comments
 

Author Comment

by:soulsinger
ID: 34982604
The form has a button on the end of each line to add as many more lines as there are unique books to add.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 34982663
Can you post the code that adds new lines?
0
 

Author Comment

by:soulsinger
ID: 34982716
<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>
0
 
LVL 19

Expert Comment

by:erikTsomik
ID: 34982799
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")>
0
 
LVL 52

Expert Comment

by:_agx_
ID: 34982815
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.
0
 

Author Comment

by:soulsinger
ID: 34983565
How can I do that? This is a new concept to me.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 34999651
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.
0
 

Author Comment

by:soulsinger
ID: 35037508
Sounds like we're getting close. I am not sure how to proceed javascript-wise. Can this thread cross-reference at the javascript zone?
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 35037567
click "request attention"

ask moderator to add it to the javascript zone
0
 

Author Comment

by:soulsinger
ID: 35037714
Ok. I requested cross-zoning it.
0
 
LVL 14

Expert Comment

by:RickEpnet
ID: 35046894
_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/
0
 
LVL 24

Expert Comment

by:dgrafx
ID: 35055958
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 ...
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 35057968
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++;
     }
}

Open in new window

0
 

Author Comment

by:soulsinger
ID: 35088351
I am sort of at a loss as far as how to assemble it all. I have raised the point value.
0
 
LVL 24

Expert Comment

by:dgrafx
ID: 35088480
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 ...
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 14

Expert Comment

by:RickEpnet
ID: 35088835
You could do away with the JavaScript all together is you did not mind you page refreshing each time they added a line.
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 35090708

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.



0
 
LVL 24

Accepted Solution

by:
dgrafx earned 500 total points
ID: 35096993
came accross your post again - had some time to post more

FIRST: from time to time there are errors in example code one posts as its usually not tested - so syntax errors or a missing something or other can happen.
I'm hoping that if you try some of the code posted here you will say something if you get an error.
Just looking at the code I posted I can see an error and if you tried to run it you would error.
So - I wrote something more for you - don't know where you are at though as you're not saying much ...

This is the code I copied from your post.
All I did was add a value="&nbsp;"
Read on ...
<TR>
<TD><LABEL for=isbn><cfinput type="text" name="isbn" id="isbn" value="&nbsp;"><BR></LABEL></TD>
<TD><LABEL for=Quantity> <CFINPUT id=Quantity class=f-name tabIndex=1 type=text name=Quantity value="&nbsp;"><BR></LABEL></TD>
<TD><LABEL for=Condition><SELECT id=Condition tabIndex=4 name=Condition><OPTION value="&nbsp;" selected>Select..</OPTION><OPTION value="Used">USED</OPTION> <OPTION value="Binder">Binder</OPTION> </SELECT></TD>
<TD><LABEL for=Title> <cfinput type="text" name="title" id="title" readonly="true" value="&nbsp;"><BR></LABEL></TD>
<TD><LABEL for=Copyright> <cfinput type="text" name="copyright" id="copyright" readonly="true" value="&nbsp;"><BR></LABEL></TD>
<TD><LABEL for=Publisher> <cfinput type="text" name="publisher" id="publisher" readonly="true" value="&nbsp;"><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>

you see how value="&nbsp;" is added above?
&nbsp; is a space and people won't see it - just try it.
this is the fix for someone leaving a field blank - simple as that.

then on the action page:
<cfset flist="isbn,quantity,condition,title,copyright,publisher"><!--- this is a list of your form fields that need to be inserted to db --->

<cfquery datasource="yourdatasource">
<cfloop from="1" to="#listlen(form.ISBN)#" index="ii">
<cfset go=1>
      <cfloop list="#flist#" index="jj">
            <cfif Not listlen(form[jj]) gte ii>
                  <cfset go=0>
            </cfif>
      </cfloop>
      <cfif go>    
            insert into table
            (#flist#)
            values
            (
            <cfset cnt=0>
            <cfloop list="#flist#" index="jj">
            <cfset cnt++ />
            '#trim(replacenocase(listgetat(form[jj],ii),"&nbsp;","","all"))#'
            <cfif cnt lt listlen(flist)>,</cfif>
            </cfloop>            )    
     </cfif>
</cfloop>
</cfquery>

If you try this and get an error just post back and I can help you figure it out ...

0
 

Author Comment

by:soulsinger
ID: 35129469
Will do! I will get into it today or tomorrow and let you know!
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 35129535
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)

0
 
LVL 24

Expert Comment

by:dgrafx
ID: 35129606
well - the code I posted does all that!
and all soulsinger need do is follow the simple instructions in my last post.
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 35129681

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

0
 
LVL 24

Expert Comment

by:dgrafx
ID: 35130123
not only could you then add some js validation - but if you look at the action page code it is protected ...
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 35130199
> 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.
0
 
LVL 24

Expert Comment

by:dgrafx
ID: 35130341
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.
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 35130531
> 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!
0
 
LVL 24

Expert Comment

by:dgrafx
ID: 35130665
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.
0
 
LVL 39

Expert Comment

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

Author Comment

by:soulsinger
ID: 35417138
It is actually on a closed system that is internal for inventory.
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 35417175
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
0
 

Author Comment

by:soulsinger
ID: 35417195
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.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Envision that you are chipping away at another e-business site with a team of pundit developers and designers. Everything seems, by all accounts, to be going easily.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn the benefit of using external CSS files and the relationship between class and ID selectors. Create your external css file by saving it as style.css then set up your style tags: (CODE) Reference the nav tag and set your prop…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now