asked on

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

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

ASKER CERTIFIED SOLUTION

coldfusionwebdev

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

plymelk

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

aprilmfara

ASKER

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.

aprilmfara

ASKER

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?

SOLUTION

_agx_

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

_agx_

>> to take make sure

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

aprilmfara

ASKER

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

aprilmfara

ASKER

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

_agx_

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... ;-)

_agx_

Thanks @aprilmfara