Solved

Not able to correctly insert variables in db table, Add/remove rows dynamically

Posted on 2010-08-31
10
448 Views
Last Modified: 2012-05-10
hi,

i am creating a purchase order.  i use cfml, javascript. i am on coldfusion 8.

my pain is regarding the line item section. dynamically adding rows and then storing them in a database table.


what is working:  

i am able to add and delete rows.


what is not working:  

i am only able to save the first line item, 2 or more gets error.
i do not know how to correctly loop through the entries and insert them in my database.
when i enter 1 line item only, the insert statement functions properly.  when i enter 2 or more, the insert loop trys to add all entries to the same row in the db table.

error message:
[Macromedia][SQLServer JDBC Driver][SQLServer]There are fewer columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.


i apologize for rambling.  i am so exhausted.

something else to note. with my current setup, i do not have to reload the page. i would love to be able to keep it that way.

can someone assist me with my dilemma?  thank you.
<cfparam name="submit" default="">
<cfparam name="getitemnumber" default="1" type="integer">

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>SPR Form</title>

<link href="styleLineItem.css" rel="stylesheet" type="text/css">

    <script type="text/javascript">
				
		function deleteRow(r) 
		{
			var i = r.parentNode.parentNode.rowIndex;
			document.getElementById('dataTable').deleteRow(i);
			document.lineItem.itemNumber.value = document.lineItem.itemNumber.value - 1;
		}
		
		function addRow(tableID) 
		{
			document.lineItem.itemNumber.value = (Number(document.getElementById('itemNumber').value)) + 1;
			
			var table = document.getElementById(tableID);

			var rowCount = table.rows.length;
			var row = table.insertRow(rowCount);

			var colCount = table.rows[0].cells.length;

			for(var i=0; i<colCount; i++) 
			{
				var newcell	= row.insertCell(i);

				newcell.innerHTML = table.rows[0].cells[i].innerHTML;
				//alert(newcell.childNodes);
				switch(newcell.childNodes[0].type) 
				{
					case "text":
							newcell.childNodes[0].value = "";
							break;
				
					//case "select-one":
						//	newcell.childNodes[0].selectedIndex = 0;
							//break;
				}
			}
		}
	</script>
</head>

<body>

<cfif submit is "">
<cfform name="lineItem" action="lit.cfm">
<table>
<tr>
<td>
<table width="90%" align="center">
<tr>
<td>
    <span class="subtitlegrad"><table width="100%" align="center">
        <tr>
            <td>
                <span class="heading">Line Items</span>
            </td>
        </tr>
    </table>
    </span>
    <table width="100%" align="center">
        <tr class="label">
            <td class="five"></td>
            <td class="five">Qty</td>
            <td class="five">U/M</td>
            <td class="five">Description</td>
            <td class="five">Mfgr</td>
            <td class="five">Product #</td>
            <td class="five">Unit Cost</td>
            <td class="five">Total Cost</td>
        </tr>
    </table>
        
<table id="dataTable">
	<!--- Gets the value of getitemNumber from above --->
  <cfinput type="text" name="itemNumber" value="#getitemNumber#">
            
	 <!---Loop Through from 1 to the number above --->
	<cfloop from="1" to="#getitemNumber#" index="idx">
                <!--- At first, it displays the original one row form --->
                <!--- User enters the data and hits "Add Another" --->
                <!--- The page reloads with the data already entered and creates a new blank row that is ready for data entry --->
                <tr>
                        <TD><INPUT type="button" value="Del" onClick="deleteRow(this)" /></TD>
                        <TD><cfinput type="text" name="qty#idx#" style="width: 3.7em"></TD>
                        <TD><cfinput type="text" name="um#idx#" style="width: 3.7em"></TD> 
                        <TD><cfinput type="text" name="desc#idx#"></TD>
                        <TD><cfinput type="text" name="mfgr#idx#"></TD>
                        <TD><cfinput type="text" name="product#idx#"></TD> 
                        <TD><cfinput type="text" name="unitCost#idx#" style="width: 6em"></TD>
                        <TD><cfinput type="text" name="totalCost#idx#" style="width: 6em"></TD>
                </tr>
	</cfloop>
</table>
<table>
                <tr>
                    <TD>
                        &nbsp;&nbsp;&nbsp;
                    </TD>
                    <td colspan="8">
                        <input type="button" name="add" value="Add Line Item" onclick="addRow('dataTable')" >
                    </td> 
                    <td colspan="8">
                    </td>
                </tr> 
        </table>
        <table width="90%" align="center">
    <tr>
        <td><input type="submit" name="submit" value="Submit"></td>
    </tr>
	</table>
</td>
</tr>
</table>
</cfform>
<cfelse>
<!---
<cfloop from="1" to="#form.itemNumber#"  index="idx">
<cfoutput>
#idx#
</cfoutput>
</cfloop>
--->
<!--- insert data into SPRCER table --->
<cfquery datasource="CFIntraV2" name="Insert">
	INSERT INTO SPRCER
		(nm_req_type, vc_requestor_name, vc_requestor_email, dt_submit_date)
	VALUES
		(1, 'asamford', 'asamford@jpshealth.org',  GETDATE())
</cfquery>

 <!--- get data --->
<CFQUERY datasource="CFIntraV2" name="getSPRData">
   			Select pk_req_id From SPRCER
			where pk_req_id in
				(select Max(pk_req_id) From SPRCER)
</CFQUERY>

<cfset fk_req_id = #getSPRData.pk_req_id#>

<cfloop from="1" to="#form.itemNumber#"  index="idx">
    <cfset getQty = evaluate("form.Qty" & idx)>
    <cfset getUM = evaluate("form.UM" & idx)>
    <cfset getDesc = evaluate("form.Desc" & idx)>
    <cfset getMfgr= evaluate("form.Mfgr" & idx)>
    <cfset getProduct = evaluate("form.Product" & idx)>
    <cfset getunitCost = evaluate("form.unitCost" & idx)>
	<cfset gettotalCost = evaluate("form.totalCost" & idx)>
    <cfquery name="InsertItem" datasource="CFIntraV2">
        INSERT INTO SPRCER_ITEM (fk_req_id, nm_quantity, vc_um, vc_description, vc_manufacturer, vc_product_num, nm_unit_cost, nm_total_cost)
        values (#fk_req_id#, #getQty#, '#getUM#', '#getDesc#', '#getMfgr#', '#getProduct#', #getunitCost#, #gettotalCost#)
    </cfquery>
</cfloop>

<cfoutput> 
ueoa
</cfoutput>
</cfif>

</body>
</html>

Open in new window

0
Comment
Question by:aprilmfara
10 Comments
 
LVL 1

Accepted Solution

by:
coldfusionwebdev earned 167 total points
ID: 33573482
Use cfqueryparam for the vallues in the insert statement and your problem will probably be sovled.
0
 
LVL 2

Assisted Solution

by:plymelk
plymelk earned 167 total points
ID: 33573590
How is the table SPRCER_ITEM defined?
Is the primary key set up for auto incrementing?
Are there any other columns in SPRCER_ITEM, other than the columns defined on line 159 above?
Why do you use different methods for changing the itemValue in lines 17 and line 22?
Have you verified that the itemValue is actually changing?
Have you verified the resulting page has increasing #idx# values when you Show Source in Internet Explorer?
0
 

Author Comment

by:aprilmfara
ID: 33579189
coldfusionwebdev,

I tried something like you suggested.

I ran an insert to insert a line item number for each line item existing:

<cfloop from="1" to="#form.itemNumber#"  index="idx">
    <cfset nm_line_num = evaluate(idx)>
    <cfquery name="InsertItem" datasource="CFIntraV2">
        INSERT INTO SPRCER_ITEM (fk_req_id, nm_line_num)
        values (#fk_req_id#, #nm_line_num#)
    </cfquery>
</cfloop>

this worked, telling me that the loop is functioning correctly without the form variables.

Then I did an update statement to update the record  with the form variables where the fk_req_id = fk_req_id and nm_line_num = idx.

The loop still tried to insert every value on the same line.

Error:

Incorrect syntax near '2'.

The error occurred in F:\WebSites\SPRCER\lit.cfm: line 265
..., nm_total_cost = #gettotalCost#
264 :         WHERE fk_req_id = #fk_req_id#
265 :         AND nm_line_num = #idx#...
 

UPDATE SPRCER_ITEM SET nm_quantity = 1,2, vc_um = '1,2' , vc_description = '1,2' , vc_manufacturer = '1,2' , vc_product_num = '1,2' , nm_unit_cost = 1,2 , nm_total_cost = 1,2 WHERE fk_req_id = 16 AND nm_line_num = 1

The loop itself works but there is something wrong with the form variables after the first line item.

Viewing the source of the lit.cfm page after entering more than 1 line item, I have noticed that the source code only shows the initial line item variables.

0
 

Author Comment

by:aprilmfara
ID: 33579272
Plymelk,

How is the table SPRCER_ITEM defined?
Is the primary key set up for auto incrementing?

yes


Are there any other columns in SPRCER_ITEM, other than the columns defined on line 159 above?

no

Why do you use different methods for changing the itemValue in lines 17 and line 22?
Have you verified that the itemValue is actually changing?

yes, it counts up and down correctly

i have the itemNumber variable not hidden so i could test this

line 22: for some reason, when adding 1 to the itemNumber, this function though itemNumber was a string so i converted it to a number


Have you verified the resulting page has increasing #idx# values when you Show Source in Internet Explorer?

this is the issue i believe.  

Viewing the source of the lit.cfm page after entering more than 1 line item, I have noticed that the source code only shows the initial line item variables, eg (qty1, um1, desc1...).

can you assist with this?
0
 
LVL 52

Assisted Solution

by:_agx_
_agx_ earned 166 total points
ID: 33580416
The problem is your processing code expects all of the field names to be sequentially and uniquely named:

ie
    form.somefield1
    form.somefield2
    form.somefield3
    ...

>> newcell.innerHTML = table.rows[0].cells[i].innerHTML;

But that's not what the javascript code is doing.  It's copies an existing row .. but it never changes the names of the fields _within_ that row.  So you end up with duplicate fields.  In CF, when 2 or more fields have the same name, the values are submitted as a CSV list (not good).  That's why you're seeing this:

         >> SET nm_quantity = 1,2

Bottom line, it's not as simple as copying the "innerHTML".  The javascript code has to take make sure all of the new field names are unique too.
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 52

Expert Comment

by:_agx_
ID: 33580600
>> to take make sure

Ack... typos are killing me today:  to MAKE sure ....
0
 

Author Comment

by:aprilmfara
ID: 33580617
agx,
thank you for the input.

i actually figured it all out myself and resolved the issue.

i ended up updating the javascript like so...

quite ghetto, but it does the job.  thank you all for your comments/assistance.




function addRow(tableID) 

		{

			var i = (Number(document.getElementById('itemNumber').value)) + 1;

			

			document.lineItem.itemNumber.value = i

			

			var newRow = document.all(tableID).insertRow();

    

    		//add 3 cells (<td>) to the new row and set the innerHTML to contain text boxes



    		var oCell = newRow.insertCell();

    		oCell.innerHTML = "<INPUT type=\"button\" value=\"Del\" onClick=\"deleteRow(this)\" />";

			

			oCell = newRow.insertCell();

			oCell.innerHTML = "<input type=\"text\" name=\"qty"+i+"\" style=\"width: 3.7em\">";

            

			oCell = newRow.insertCell();

			oCell.innerHTML = "<input type=\"text\" name=\"um"+i+"\" style=\"width: 3.7em\">";

            

			oCell = newRow.insertCell();

			oCell.innerHTML = "<input type=\"text\" name=\"desc"+i+"\">";

            

			oCell = newRow.insertCell();

			oCell.innerHTML = "<input type=\"text\" name=\"mfgr"+i+"\">";

            

			oCell = newRow.insertCell();

			oCell.innerHTML = "<input type=\"text\" name=\"product"+i+"\">";

            

			oCell = newRow.insertCell();

			oCell.innerHTML = "<input type=\"text\" name=\"unitCost"+i+"\" style=\"width: 6em\">";

            

			oCell = newRow.insertCell();

			oCell.innerHTML = "<input type=\"text\" name=\"totalCost"+i+"\" style=\"width: 6em\">";

		}

Open in new window

0
 

Author Closing Comment

by:aprilmfara
ID: 33580728
Though I ended up figuring it out in the end, I am dividing the points as evenly as possible amongst you all because you all got me thinking. Thank you, aprilmfara
0
 
LVL 52

Expert Comment

by:_agx_
ID: 33580770
Glad you found something that works.  Don't forget to select your own solution as the answer so it can be added to the archives.  

>> quite ghetto

   Don't insult the ghetto that way! Just joking... ;-)
0
 
LVL 52

Expert Comment

by:_agx_
ID: 33581088
Thanks @aprilmfara
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Hi, Even though I have created this Tutorial on My personal Blog, Some people might not able to find my website, So here i am posting it again Today, from the topic it is very clear that i will be showing you here the very basic usage of how we …
OverviewThis article demonstrates a simple search form using AJAX. The purpose of the article is to demonstrate how to use the same code to render a page and javascript (JQuery) and AJAX to make subsequent calls to refine the results. The princip…
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…

747 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

11 Experts available now in Live!

Get 1:1 Help Now