Insert Multiple Parts Problem

[bonneylake]

Hey Everyone,

Well recently i been inserting multiple fields for a section in my form called "serial". Well now i am trying to insert multiple fields for the not only the serial section but also the parts section an i seem to be having trouble.

When i try to insert into the parts section i get the error Invalid character value for cast specification. But not sure what i am doing wrong. Here is what i am using to insert. All the sections below work except the.Inserts parts information into parts table section.

<!---Inserts information into serial table.--->
<CFIF REQUEST_METHOD EQ "POST">
<CFSET machineListLen = listLen(Form.serialcount)>
<CFLOOP from="1" to="#machineListLen#" index="machineCount">
 <CFSET serialnum       = Form["serialnum_" & machineCount]>
 <CFSET modelno         = Form["modelno_" & machineCount]>
 <CFSET producttype     = Form["producttype_" & machineCount]>
 <CFSET softhardware    = Form["softhardware_" & machineCount]>
 <CFSET resolution      = Form["resolution_" & machineCount]>
 <CFSET resdate         = Form["resdate_" & machineCount]>
 <CFSET resvertified    = Form["resvertified_" & machineCount]>
 <CFSET vertifidate     = Form["vertifidate_" & machineCount]>
 <CFSET deptvendor      = Form["deptvendor_" & machinecount]>
 <CFSET hardwarefailure = Form["hardwarefailure_" & machineCount]>
 <CFSET rma             = Form["rma_" & machineCount]>
 <CFSET thedescription  = Form["thedescription_" & machineCount]>
 <CFSET hcpn            = Form["hcpn_" & machineCount]>
 <CFSET partsreturn     = Form["partsreturn_" & machineCount]>
 <CFSET defective       = Form["defective_" & machineCount]>
<!--- <CFSET followdate      = Form["followdate_" & machineCount]>
 <CFSET onsite          = Form["onsite_" & machineCount]>
 <CFSET numonsite       = Form["numonsite_" & machineCount]>--->
 
<cfquery name="serial" datasource="CustomerSupport">
   exec usp_CS_Insertserial 
     <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
     '#Form.ID#',
     <cfqueryparam value="#modelno#" CFSQLType = "CF_SQL_VARCHAR">,
     <cfqueryparam value="#producttype#" CFSQLType = "CF_SQL_VARCHAR">,
     <cfqueryparam value="#softhardware#" CFSQLType = "CF_SQL_VARCHAR">,
     <cfqueryparam value="#resolution#" CFSQLType = "CF_SQL_VARCHAR">,
     <cfqueryparam value="#resdate#">,
     <cfqueryparam value="#resvertified#" CFSQLType = "CF_SQL_VARCHAR">,
     <cfqueryparam value="#vertifidate#">,
     <cfqueryparam value="#deptvendor#" CFSQLType = "CF_SQL_VARCHAR">,
     <cfqueryparam value="#hardwarefailure#" CFSQLType = "CF_SQL_VARCHAR">,
     <cfqueryparam value="#rma#" CFSQLType = "CF_SQL_VARCHAR">
   </cfquery>
 
<!---Inserts information into notes_descr table.--->
<cfquery name="description" datasource="CustomerSupport">
    exec usp_CS_Insertdescription
   <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
   '#Form.ID#',
   <cfqueryparam value="#thedescription#" CFSQLType = "CF_SQL_VARCHAR">,
   '#Form.fk_addedBy#'
</cfquery>
 
<!---Inserts parts information into parts table.--->
<!---because it is a bit we don't use 'ticks' around defective for parts table--->
<cfquery name="parts" datasource="CustomerSupport">
    exec usp_CS_Insertparts
    <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
    '#Form.ID#',
    <cfqueryparam value="#hcpn#" CFSQLType = "CF_SQL_VARCHAR">,
    <cfqueryparam value="#partsreturn#" CFSQLType = "CF_SQL_VARCHAR">,
    <cfqueryparam value="#defective#" CFSQLType = "CF_SQL_BIT">
<!---    <cfquery param value="#submission#" CFSQLType = "CF_SQL_VARCHAR">--->
</cfquery>
 
</CFLOOP>
</CFIF>
 

here is the multiple fields section in my form for the serial section.

<!---Allows us to add serial information multiple times --->
<script type="text/javascript">
<!---Allows us to add multiple fields --->
function addInput(divName){
var dynamic = document.getElementById('dynamicInput');
var thevalue = document.getElementById('theValue');
var count = (document.getElementById('theValue').value -1)+ 2;
thevalue.value = count;
var newdiv = document.createElement('div');
var getparts = document.createElement('div');
var divIdName = 'dynamic'+count+'Input';
var partc = 'partscount'+count;
 
newdiv.setAttribute('id',divIdName);
 
<!--- Adds Extra fields for Model No, Product Type, and Type of Hardware Failure  --->
newdiv.innerHTML =
"<table class='zpExpandedTable' id='modeltable'>" +
"<th class='sectiontitletick' colspan='7'>Serial Information "+ count +" </th>" +
"<tr>" +
"<td id='paddingformultitop'>Model No:&nbsp;&nbsp;&nbsp;&nbsp;</td>" +
"</td>" +
"<td>" +
 "<select name='modelno_" + count + "' >" +
 "<option value=''>Make A Selection</option>" +
"<cfoutput query='models'>" + 
"<option value='#model#'>#model#</option>" + 
"</cfoutput>" + 
 "</select>" +
 "</td>" +
 "<td>" +
"&nbsp;&nbsp;&nbsp;&nbsp;Product Type:"  +
"</td>" +
"<td>" +
"<select name='producttype_" + count + "'>" +
"<option value='' selected>No Choice</option>" +
"<cfoutput query='getProdType'>" + 
"<option value='#pk_productType#'>#pk_productType#</option>" + 
"</cfoutput>" + 
"</select>" +
"</td>" +
"<td class='red'>" +
"&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Type Of Hardware Failure*:" +
"</td>" +
"<td>" +
"<select name='hardwarefailure_" + count + "'>" +
"<option value='' selected>Make A Selection</option>" +
"<cfoutput query='getHardwareFail'>" +
"<option value='#pk_hardwareFailure#'>#pk_hardwareFailure#</option>" +
"</cfoutput>" +
"</select>" +
"</td>" +
"</tr>" +
"<table>";
 
<!--- Adds Extra fields for Serial Number and Software/Hardware  --->
newdiv.innerHTML = newdiv.innerHTML +
"<table class='zpExpandedTable' id='modeltable'>" +
"<tr>" +
"<td id='paddingformultitop'>" +
"Serial Number:&nbsp;&nbsp;" +
"<input type='text' name='serialnum_" + count + "'>" +
"&nbsp;&nbsp;&nbsp;&nbsp;Software/Hardware:&nbsp;&nbsp;" +
"<select name='softhardware_" + count + "'>" +
"<option value='' selected>No Choice</option>" +
"<cfoutput query='getSoftHard'>" + 
"<option value='#pk_softwareHardware#'>#pk_softwareHardware#</option>" + 
"</cfoutput>" + 
"</select>" +
"</td>" +
"</tr>" +
"</table>";
 
<!--- Adds Extra fields for Description  --->
newdiv.innerHTML = newdiv.innerHTML + 
"<table class='zpExpandedTable' id='resoltable' cellpadding='3' cellspacing='0'>" +
"<tr>" +
"<td id='paddingformutli'>" +
"Description:&nbsp;&nbsp;" + 
"</td>" +
"<td class='descriptionmoveinmulti'>" +
"( You may enter up to 1000 characters. )"+
"<br>" +
"<textarea maxlength='1000' onkeyup='return descriptionmaxlength(this)' onkeydown='return descriptionmaxlength(this)'rows='4' cols='60' name='thedescription_" + count + "'></textarea>" +
"</td>" +
"</tr>" +
"</table>";
 
<!--- Adds Extra fields for Resolution  --->
newdiv.innerHTML = newdiv.innerHTML +
"<table class='zpExpandedTable' id='resoltable' cellpadding='1' cellspacing='0'>" +
"<tr>" +
"<td id='paddingformutli'>" +
"Resolution:&nbsp;&nbsp;" +
"</td>" +
"<td class='resolutionmoveinmulti'>" +
"( You may enter up to 1500 characters. )"+
"<br>" +
"<textarea maxlength='1500' onkeyup='return resolutionmaxlength(this)' onkeydown='return resolutionmaxlength(this)' rows='4' cols='60' name='resolution_" + count + "'></textarea>" +
"</td>" +
"</tr>" +
"</table>";
 
<!--- Adds Extra fields for Resolution Date, Current Date (for resolution date) and resolution vertified as effective by  --->
newdiv.innerHTML = newdiv.innerHTML +
"<table class='zpExpandedTable' id='resoldatetab' cellpadding='1' cellspacing='0'>" +
"<tr>" +
"<td id='paddingformultitop'>" +
"Resolution Date:&nbsp;(MM/DD/YYYY)&nbsp;&nbsp;" +
"</td>" +
"<td>" +
"<input type='text' name='resdate_" + count + "' value=''  >&nbsp;&nbsp;" +
 
"&nbsp;&nbsp;&nbsp;&nbsp;Current Date:&nbsp;&nbsp;" +
"<input type='checkbox' name='currentdateresol_" + count + "' onClick=resdate_" + count + ".value=fill_date()>" +
"</td>" +
"<td>" +
"Resolution Verified as effective by:&nbsp;&nbsp;"  +
"</td>" +
"<td>" +
"<select name='resvertified_" + count + "'>" +
"<option value='' selected>Make A Selection</option>" +
"<cfoutput query='gettech'><option value='#fname# #lname#'>#fname# #lname#</option></cfoutput>" +
"</select>" +
"</td>" +
"</tr>" +
"</table>";
 
<!--- Adds Extra fields for Vertification Date, Current Date (for vertification date) and resolution vertified as effective by  --->
newdiv.innerHTML = newdiv.innerHTML +
"<table class='zpExpandedTable' id='resoltable' cellpadding='1' cellspacing='0'>" +
"<tr>" +
"<td id='paddingformultitop'>" +
"Verification Date:&nbsp;(MM/DD/YYYY)&nbsp;&nbsp;" +
"</td>" +
"<td class='vertificationmoveinmulti'>" +
"<input type='text' name='vertifidate_" + count + "'>&nbsp;&nbsp;" +
"&nbsp;&nbsp;&nbsp;&nbsp;Current Date:&nbsp;&nbsp;" +
"<input type='checkbox' name='currentdatevert_" + count + "' onClick=vertifidate_" + count + ".value=fill_date()>" +
"</td>" +
"</tr>" +
"</table>";
 
<!--- Adds Extra fields for Dept/Vendor Responsibility  --->
newdiv.innerHTML = newdiv.innerHTML +
"<table class='zpExpandedTable' id='resoltable' cellpadding='1' cellspacing='0'>" +
"<tr>" +
"<td class='red' id='paddingformultitop'>" +
"Dept/Vendor Responsibility*:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"  +
"<select name='deptvendor_" + count + "'>" +
"<option value='' selected>Make A Selection</option>" +
"<cfoutput query='getDeptVendor'>" +
"<option value='#pk_deptVendor#'>#pk_deptVendor#</option>" +
" </cfoutput>" +
"</select>" +
"</td>" +
"</tr>" +
"</table>";
 
<!--- Adds Extra fields for RMA Data Only  --->
newdiv.innerHTML = newdiv.innerHTML +
"<table class='zpExpandedTable' id='resoltable' cellpadding='1' cellspacing='0'>" +
"<tr>" +
"<td id='paddingformultitop'>" +
"RMA Data Only:&nbsp;&nbsp;&nbsp;&nbsp;" +
"</td>" +
"<td class='rmanmoveinmulti'>" +
"( You may enter up to 1000 characters. )"+
"<br/>" +
"<textarea maxlength='1000' onkeyup='return rmamaxlength(this)' onkeydown='return rmamaxlength(this)' rows='4' cols='60' name='rma_" + count + "'></textarea> " +
"</td>" +
"</tr>" +
"</table>" +
"<input type='hidden' value='0' id='"+partc+"' \>" +
"<input type='hidden' name='serialcount' value='" + count + "'>";
 
<!--- Adds Delete to every ticket  --->
newdiv.innerHTML = newdiv.innerHTML +
"<table class='zpExpandedTable' id='resoltable' cellpadding='1' cellspacing='0'>" +
"<tr>" +
"<td>" +
"<input type='button' class='removeticket' value='Remove Serial &quot;"+ count +"&quot;' onclick=\"removeElement(\'"+divIdName+"\')\"></a>" +
"</td>" +
"</tr>" +
"<tr>" +
"<td>" + 
"<input type='button' class='addticket' value='Add Parts' onclick=\"addpartInput(\'"+divIdName+"\')\">" +
"</td>" +
"</tr>" +
"</table>";
 
document.getElementById(divName).appendChild(newdiv);
} 
 
<!---Allows us to remove multiple fields --->
function removeElement(divNum) {
  var d = document.getElementById('dynamicInput');
  var olddiv = document.getElementById(divNum);
  d.removeChild(olddiv);
}
</script>

here is the multiple field section for the parts section

<!---Allows us to add PARTS information multiple times--->
<script type="text/javascript">
<!---Adds multiple fields for parts --->
function addpartInput(partName){
var parts = document.getElementById(partName);
var getparts = document.getElementById('div');
var serialno = partName.replace("dynamic","").replace("Input","");
var avalue = document.getElementById("partscount"+serialno);
var count = parseInt(avalue.value) + 1;
avalue.value = count;
var partdiv = document.createElement('div');
var partIdName = 'part' + count + 'Name' + serialno;
 
partdiv.setAttribute('id',partIdName);
 
<!--- Adds Extra fields for parts table  --->
partdiv.innerHTML =
<!---what used originally for this tableclass='createticketables' id='spaceup'--->
"<table class='zpExpandedTable' id='resoltable' cellpadding='1' cellspacing='0' >" +
"<th class='sectiontitle' colspan='7'>Parts Information "+ count +" Serial Information "+serialno+"</th>" +
"<tr>" +
"<td class='indent' id='formfieldpadding'>HC P/N:&nbsp;&nbsp;&nbsp;<input type='text' name='hcpn_" + count + "' style='margin:0px'></td>" +
"<td class='red'>" +
"Parts been returned* " +
"<input type='checkbox' name='partsreturn_" + count +"' value='1'/>" +
"</td>" +
"<td>" +
"<td class='indent'>Defective<input type='checkbox' name='defective_" + count +"' value='1'/></td>" +
"</td>" +
"</tr>" +
"</table>" +
"<table table class='zpExpandedTable' id='resoltable' cellpadding='1' cellspacing='0'  >" +
"<tr>" +
"<td class='indent' id='formfieldpadding'>Follow up Date:(MM/DD/YYYY)&nbsp;&nbsp;&nbsp;"  +
"<input type='text' name='followdate_" + count + "' value='' size='8'/>&nbsp;&nbsp;&nbsp;" +
"Current Date<input type='checkbox' name='followcheck_"+ count +"' value='' onClick='followdate.value=fill_date()'/></td>" +
"<td>On Site:</td>" +
"<td><select name='onsite_" + count +"'>" +
"<option value='No Choice' selected>No Choice</option>" +
"<option value='Yes'>Yes</option>" +
"<option value='No'>No</option>" +
"</select><td>" +
"<td># of Onsite:</td><td><select name='numonsite_" + count +"'>" +
"<option value='' selected>No Choice</option>" +
"<cfloop from='0' to='10' index='nonsite'><cfoutput>" +
"<option value='#nonsite#'>#nonsite#</option></cfoutput></cfloop>" +
"</select><td>" +
"</tr>" +
"</table>" +
<!---"<input type='hidden' value='0' id='aValue' />" +--->
"<input type='hidden' name='serialcount' value='" + count + "'>";
 
 
<!--- Adds Delete to every ticket  --->
partdiv.innerHTML = partdiv.innerHTML +
"<table class='zpExpandedTable' id='resoltable' cellpadding='1' cellspacing='0'>" +
"<tr>" +
"<td>" +
"<input type='button' class='removeticket' value='Remove Parts &quot;"+count +"&quot;' onclick=\"removetheElement(\'"+partIdName+"\')\"></a>" +
"</td>" +
"</td>" +
"</tr>" +
"</table>";
 
document.getElementById(partName).appendChild(partdiv);
} 
 
<!---Allows us to remove multiple fields--->
function removetheElement(divNum) {
  var d = document.getElementById('partsInput');
  var olddiv = document.getElementById(divNum);
 olddiv.parentNode.removeChild(olddiv);
}
 
</script>

Thank you in advance,

Rach

[bonneylake]

Yes, it also needs a name. Note that partc is not the ID/name - it's the variable holding the string value which you set earlier in the function.

Hey Acoder,

Ok so like this

"<input type='hidden' value='0' name="partscount" id="partscount" value='"+partc+"' \>" +

an then do i use the name in the "to" on my cfloop?

Thank you,
Rach

[acoder]

No, don't set the name/ID to "partscount ". Set it to partc as you had before (same for both ID and name. Note also that you now have two values (which is also incorrect). You need something like:

 name='"+partc+"' id='"+partc+"'

[bonneylake]

No, don't set the name/ID to "partscount ". Set it to partc as you had before (same for both ID and name. Note also that you now have two values (which is also incorrect). You need something like:

 name='"+partc+"' id='"+partc+"'

Hey Acoder,

So like this

"<input type='hidden' name='"+partc+"' id='"+partc+"' value='"+partc+"' \>" +

an then for the to in the cfloop i call '"+partc+"' correct?

Thank you,
Rach

[acoder]

The value has to be a number, 0 initially. Look at what partc is set to (look earlier in the function, not sure which line). That's what Coldfusion will see. To see what this turns out in the browser, check the generated source in the browser and you will see that the name and ID are set to "partscount 1" or "partscount 2", etc. depending on the serial number. This is what is required in the cfloop.

[bonneylake]

The value has to be a number, 0 initially. Look at what partc is set to (look earlier in the function, not sure which line). That's what Coldfusion will see. To see what this turns out in the browser, check the generated source in the browser and you will see that the name and ID are set to "partscount 1" or "partscount 2", etc. depending on the serial number. This is what is required in the cfloop.

Hey Acoder,

So your saying it needs 'partscount'+co unt (which is what partc is set to) in the "to" in the cfloop ?

Thank you,
Rach

[acoder]

Yes, with 'count' being the serial number.

[bonneylake]

Yes, with 'count' being the serial number.

Hey Acoder,

So something like this correct?

<cfloop from="1" to="'partscount'+serialcount">
<cfquery name="parts" datasource="CustomerSupport">
    exec usp_CS_Insertparts
    <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
    '#Form.ID#',
    <cfqueryparam value="#hcpn#" CFSQLType = "CF_SQL_VARCHAR">,
    <cfqueryparam value="#partsreturn#" CFSQLType = "CF_SQL_VARCHAR">,
    <cfqueryparam value="#defective#" CFSQLType = "CF_SQL_BIT">
<!---    <cfquery param value="#submission#" CFSQLType = "CF_SQL_VARCHAR">--->
</cfquery>
</cfloop>

Thank you,
Rach

[acoder]

Not quite. It'd have to be a Coldfusion form variable like the serial count for the first loop.

[bonneylake]

Not quite. It'd have to be a Coldfusion form variable like the serial count for the first loop.

Hey Acoder,

So what would i use because i don't have any hidden fields in the parts function.

an all i have in the serial function is

"<input type='hidden' name='"+partc+"' id='"+partc+"' value='0' \>" +
"<input type='hidden' name='serialcount' id='serialcount' value='" + count + "'>";

an then is what i have for the html

<input type="hidden" value="0" id="theValue" name="theValue"/>

Thank you,
Rach

[acoder]

You don't need any in the parts function because the one(s) in the serial function is/are the count(s) for parts. What I suggest you do is turn on debugging so you can see what's being passed to your Coldfusion script. You should see all the form element names and values.