469,300 Members | 2,312 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,300 developers. It's quick & easy.

displaying previously entered multiple fields

769 512MB
Hey Everyone,

Well i am not sure if this is more of a coldfusion problem or a javscript problem. So if i asked my question in the wrong section let me know an all move it to the correct place.

what i am trying to display previously entered multiple fields. I am able to get my serial fields to display correctly, but i can not display my parts fields correctly.

Currently this is what it does

serial information 1
parts 1
parts 2

serial information 2
parts 1
parts 2

and what it needs to do

serial information 1
parts 1
serial information 2
parts 1

the problem is instead of the part going with the correct serial. it basically puts the information for both parts i added under both of the 2 serials I am also having trouble deleting fields an adding fields for the parts fields.

if someone could explain what i am doing wrong with the parts i would really appreciate it. I know it has to do with the count, just not sure what i am doing wrong


here is the javascript for serial and parts that allows me to add serials and parts

Expand|Select|Wrap|Line Numbers
  1. <!---Allows us to add serial information multiple times --->
  2. <script type="text/javascript">
  3. <!---Allows us to add multiple fields --->
  4. function addInput(divName){
  5. var dynamic = document.getElementById('dynamicInput');
  6. var thevalue = document.getElementById('theValue');
  7. var count = (document.getElementById('theValue').value -1)+ 2;
  8. thevalue.value = count;
  9. var newdiv = document.createElement('div');
  10. var getparts = document.createElement('div');
  11. var divIdName = 'dynamic'+count+'Input';
  12. var partc = 'partscount'+count;
  13.  
  14. newdiv.setAttribute('id',divIdName);
  15.  
  16. <!--- Adds Extra fields for Model No, Product Type, and Type of Hardware Failure  --->
  17. newdiv.innerHTML =
  18. "<table class='zpExpandedTable' id='modeltable'>" +
  19. "<th class='sectiontitletick' colspan='7'>Serial Information "+ count +" </th>" +
  20. "<tr>" +
  21. "<td id='paddingformultitop'>Model No:&nbsp;&nbsp;&nbsp;&nbsp;</td>" +
  22. "</td>" +
  23. "<td>" +
  24.  "<select name='modelno_" + count + "' >" +
  25.  "<option value=''>Make A Selection</option>" +
  26. "<cfoutput query='models'>" + 
  27. "<option value='#model#'>#model#</option>" + 
  28. "</cfoutput>" + 
  29.  "</select>" +
  30.  "</td>" +
  31.  "<td>" +
  32. "&nbsp;&nbsp;&nbsp;&nbsp;Product Type:"  +
  33. "</td>" +
  34. "<td>" +
  35. "<select name='producttype_" + count + "'>" +
  36. "<option value='' selected>No Choice</option>" +
  37. "<cfoutput query='getProdType'>" + 
  38. "<option value='#pk_productType#'>#pk_productType#</option>" + 
  39. "</cfoutput>" + 
  40. "</select>" +
  41. "</td>" +
  42. "<td class='red'>" +
  43. "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Type Of Hardware Failure*:" +
  44. "</td>" +
  45. "<td>" +
  46. "<select name='hardwarefailure_" + count + "'>" +
  47. "<option value='' selected>Make A Selection</option>" +
  48. "<cfoutput query='getHardwareFail'>" +
  49. "<option value='#pk_hardwareFailure#'>#pk_hardwareFailure#</option>" +
  50. "</cfoutput>" +
  51. "</select>" +
  52. "</td>" +
  53. "</tr>" +
  54. "<table>";
  55.  
  56. <!--- Adds Extra fields for Serial Number and Software/Hardware  --->
  57. newdiv.innerHTML = newdiv.innerHTML +
  58. "<table class='zpExpandedTable' id='modeltable'>" +
  59. "<tr>" +
  60. "<td id='paddingformultitop'>" +
  61. "Serial Number:&nbsp;&nbsp;" +
  62. "<input type='text' name='serialnum_" + count + "'>" +
  63. "&nbsp;&nbsp;&nbsp;&nbsp;Software/Hardware:&nbsp;&nbsp;" +
  64. "<select name='softhardware_" + count + "'>" +
  65. "<option value='' selected>No Choice</option>" +
  66. "<cfoutput query='getSoftHard'>" + 
  67. "<option value='#pk_softwareHardware#'>#pk_softwareHardware#</option>" + 
  68. "</cfoutput>" + 
  69. "</select>" +
  70. "</td>" +
  71. "</tr>" +
  72. "</table>";
  73.  
  74. <!--- Adds Extra fields for Description  --->
  75. newdiv.innerHTML = newdiv.innerHTML + 
  76. "<table class='zpExpandedTable' id='resoltable' cellpadding='3' cellspacing='0'>" +
  77. "<tr>" +
  78. "<td id='paddingformutli'>" +
  79. "Description:&nbsp;&nbsp;" + 
  80. "</td>" +
  81. "<td class='descriptionmoveinmulti'>" +
  82. "( You may enter up to 1000 characters. )"+
  83. "<br>" +
  84. "<textarea maxlength='1000' onkeyup='return descriptionmaxlength(this)' onkeydown='return descriptionmaxlength(this)'rows='4' cols='60' name='thedescription_" + count + "'></textarea>" +
  85. "</td>" +
  86. "</tr>" +
  87. "</table>";
  88.  
  89. <!--- Adds Extra fields for Resolution  --->
  90. newdiv.innerHTML = newdiv.innerHTML +
  91. "<table class='zpExpandedTable' id='resoltable' cellpadding='1' cellspacing='0'>" +
  92. "<tr>" +
  93. "<td id='paddingformutli'>" +
  94. "Resolution:&nbsp;&nbsp;" +
  95. "</td>" +
  96. "<td class='resolutionmoveinmulti'>" +
  97. "( You may enter up to 1500 characters. )"+
  98. "<br>" +
  99. "<textarea maxlength='1500' onkeyup='return resolutionmaxlength(this)' onkeydown='return resolutionmaxlength(this)' rows='4' cols='60' name='resolution_" + count + "'></textarea>" +
  100. "</td>" +
  101. "</tr>" +
  102. "</table>";
  103.  
  104. <!--- Adds Extra fields for Resolution Date, Current Date (for resolution date) and resolution vertified as effective by  --->
  105. newdiv.innerHTML = newdiv.innerHTML +
  106. "<table class='zpExpandedTable' id='resoldatetab' cellpadding='1' cellspacing='0'>" +
  107. "<tr>" +
  108. "<td id='paddingformultitop'>" +
  109. "Resolution Date:&nbsp;(MM/DD/YYYY)&nbsp;&nbsp;" +
  110. "</td>" +
  111. "<td>" +
  112. "<input type='text' name='resdate_" + count + "' value=''  >&nbsp;&nbsp;" +
  113.  
  114. "&nbsp;&nbsp;&nbsp;&nbsp;Current Date:&nbsp;&nbsp;" +
  115. "<input type='checkbox' name='currentdateresol_" + count + "' onClick=resdate_" + count + ".value=fill_date()>" +
  116. "</td>" +
  117. "<td>" +
  118. "Resolution Verified as effective by:&nbsp;&nbsp;"  +
  119. "</td>" +
  120. "<td>" +
  121. "<select name='resvertified_" + count + "'>" +
  122. "<option value='' selected>Make A Selection</option>" +
  123. "<cfoutput query='gettech'><option value='#fname# #lname#'>#fname# #lname#</option></cfoutput>" +
  124. "</select>" +
  125. "</td>" +
  126. "</tr>" +
  127. "</table>";
  128.  
  129. <!--- Adds Extra fields for Vertification Date, Current Date (for vertification date) and resolution vertified as effective by  --->
  130. newdiv.innerHTML = newdiv.innerHTML +
  131. "<table class='zpExpandedTable' id='resoltable' cellpadding='1' cellspacing='0'>" +
  132. "<tr>" +
  133. "<td id='paddingformultitop'>" +
  134. "Verification Date:&nbsp;(MM/DD/YYYY)&nbsp;&nbsp;" +
  135. "</td>" +
  136. "<td class='vertificationmoveinmulti'>" +
  137. "<input type='text' name='vertifidate_" + count + "'>&nbsp;&nbsp;" +
  138. "&nbsp;&nbsp;&nbsp;&nbsp;Current Date:&nbsp;&nbsp;" +
  139. "<input type='checkbox' name='currentdatevert_" + count + "' onClick=vertifidate_" + count + ".value=fill_date()>" +
  140. "</td>" +
  141. "</tr>" +
  142. "</table>";
  143.  
  144. <!--- Adds Extra fields for Dept/Vendor Responsibility  --->
  145. newdiv.innerHTML = newdiv.innerHTML +
  146. "<table class='zpExpandedTable' id='resoltable' cellpadding='1' cellspacing='0'>" +
  147. "<tr>" +
  148. "<td class='red' id='paddingformultitop'>" +
  149. "Dept/Vendor Responsibility*:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"  +
  150. "<select name='deptvendor_" + count + "'>" +
  151. "<option value='' selected>Make A Selection</option>" +
  152. "<cfoutput query='getDeptVendor'>" +
  153. "<option value='#pk_deptVendor#'>#pk_deptVendor#</option>" +
  154. " </cfoutput>" +
  155. "</select>" +
  156. "</td>" +
  157. "</tr>" +
  158. "</table>";
  159.  
  160. <!--- Adds Extra fields for RMA Data Only  --->
  161. newdiv.innerHTML = newdiv.innerHTML +
  162. "<table class='zpExpandedTable' id='resoltable' cellpadding='1' cellspacing='0'>" +
  163. "<tr>" +
  164. "<td id='paddingformultitop'>" +
  165. "RMA Data Only:&nbsp;&nbsp;&nbsp;&nbsp;" +
  166. "</td>" +
  167. "<td class='rmanmoveinmulti'>" +
  168. "( You may enter up to 1000 characters. )"+
  169. "<br/>" +
  170. "<textarea maxlength='1000' onkeyup='return rmamaxlength(this)' onkeydown='return rmamaxlength(this)' rows='4' cols='60' name='rma_" + count + "'></textarea> " +
  171. "</td>" +
  172. "</tr>" +
  173. "</table>" +
  174. "<input type='hidden' name='"+partc+"' id='"+partc+"' value='0'>" +
  175. "<input type='hidden' name='serialcount' id='serialcount' value='" + count + "'>";
  176.  
  177. <!--- Adds Delete to every ticket  --->
  178. newdiv.innerHTML = newdiv.innerHTML +
  179. "<table class='zpExpandedTable' id='resoltable' cellpadding='1' cellspacing='0'>" +
  180. "<tr>" +
  181. "<td>" +
  182. "<input type='button' class='removeticket' value='Remove Serial &quot;"+ count +"&quot;' onclick=\"removeElement(\'"+divIdName+"\')\"></a>" +
  183. "</td>" +
  184. "</tr>" +
  185. "<tr>" +
  186. "<td>" + 
  187. "<input type='button' class='addticket' value='Add Parts' onclick=\"addpartInput(\'"+divIdName+"\')\">" +
  188. "</td>" +
  189. "</tr>" +
  190. "</table>";
  191.  
  192. document.getElementById(divName).appendChild(newdiv);
  193.  
  194. <!---Allows us to remove multiple fields --->
  195. function removeElement(divNum) {
  196.   var d = document.getElementById('dynamicInput');
  197.   var olddiv = document.getElementById(divNum);
  198.   d.removeChild(olddiv);
  199. }
  200. </script>
  201.  
  202.  
  203.  
  204.  
  205.  
  206. <!---Allows us to add PARTS information multiple times--->
  207. <script type="text/javascript">
  208. <!---Adds multiple fields for parts --->
  209. function addpartInput(partName){
  210. var parts = document.getElementById(partName);
  211. <!---('partsInput')--->
  212.  
  213. <!---this works if only want to add a part under the serial you are working with, but if you have
  214. serial information 1
  215. and serial information 2 an want to add information to serial information 1 you cant because parts always appear under the last serial information added if you click add parts--->
  216. var getparts = document.getElementById('div');
  217. <!---var avalue = document.getElementById('aValue');--->
  218. var serialno = partName.replace("dynamic","").replace("Input","");
  219. var avalue = document.getElementById("partscount"+serialno);
  220. <!---var count = (document.getElementById('avalue').value -1)+ 2;--->
  221. var count = parseInt(avalue.value) + 1;
  222. avalue.value = count;
  223. var partdiv = document.createElement('div');
  224. <!---var partIdName = 'part'+count+'Name';--->
  225. var partIdName = 'part' + count + 'Name' + serialno;
  226. <!---'parts'+count+'Input'--->
  227.  
  228. partdiv.setAttribute('id',partIdName);
  229.  
  230. <!--- Adds Extra fields for parts table  --->
  231. partdiv.innerHTML =
  232. <!---what used originally for this tableclass='createticketables' id='spaceup'--->
  233. "<table class='zpExpandedTable' id='resoltable' cellpadding='1' cellspacing='0' >" +
  234. "<th class='sectiontitle' colspan='7'>Parts Information "+ count +" Serial Information "+serialno+"</th>" +
  235. "<tr>" +
  236. "<td class='indent' id='formfieldpadding'>HC P/N:&nbsp;&nbsp;&nbsp;" +
  237. "<input type='text' name='hcpn_" + count + "_"+serialno+"' style='margin:0px'></td>" +
  238. "<td class='red'>" +
  239. "Parts been returned* " +
  240. "<input type='checkbox' name='partsreturn_" + count + "_"+serialno+"' value='1'>" +
  241. "</td>" +
  242. "<td>" +
  243. "<td class='indent'>Defective<input type='checkbox' name='defective_" + count +"_"+serialno+"' value='1'>" +
  244. "</td>" +
  245. "</td>" +
  246. "</tr>" +
  247. "</table>";
  248. <!---"<table table class='zpExpandedTable' id='resoltable' cellpadding='1' cellspacing='0'  >" +
  249. "<tr>" +
  250. "<td class='indent' id='formfieldpadding'>Follow up Date:(MM/DD/YYYY)&nbsp;&nbsp;&nbsp;"  +
  251. "<input type='text' name='followdate_" + count + "' value='' size='8'/>&nbsp;&nbsp;&nbsp;" +
  252. "Current Date<input type='checkbox' name='followcheck_"+ count +"' value='' onClick='followdate.value=fill_date()'/></td>" +
  253. "<td>On Site:</td>" +
  254. "<td><select name='onsite_" + count +"'>" +
  255. "<option value='No Choice' selected>No Choice</option>" +
  256. "<option value='Yes'>Yes</option>" +
  257. "<option value='No'>No</option>" +
  258. "</select><td>" +
  259. "<td># of Onsite:</td><td><select name='numonsite_" + count +"'>" +
  260. "<option value='' selected>No Choice</option>" +
  261. "<cfloop from='0' to='10' index='nonsite'><cfoutput>" +
  262. "<option value='#nonsite#'>#nonsite#</option></cfoutput></cfloop>" +
  263. "</select><td>" +
  264. "</tr>" +
  265. "</table>";
  266. --->
  267.  
  268. <!--- Adds Delete to every ticket  --->
  269. partdiv.innerHTML = partdiv.innerHTML +
  270. "<table class='zpExpandedTable' id='resoltable' cellpadding='1' cellspacing='0'>" +
  271. "<tr>" +
  272. "<td>" +
  273. "<input type='button' class='removeticket' value='Remove Parts &quot;"+count +"&quot;' onclick=\"removetheElement(\'"+partIdName+"\')\"></a>" +
  274. "</td>" +
  275. "</td>" +
  276. "</tr>" +
  277. "</table>";
  278.  
  279. document.getElementById(partName).appendChild(partdiv);
  280.  
  281. <!---Allows us to remove multiple fields--->
  282. function removetheElement(divNum) {
  283.   var d = document.getElementById('partsInput');
  284.   var olddiv = document.getElementById(divNum);
  285.  olddiv.parentNode.removeChild(olddiv);
  286. }
  287.  
  288. </script>
  289.  
  290.  


here is the html/coldfusion that displays what was previously entered for serials and parts.
Expand|Select|Wrap|Line Numbers
  1. <!--- Ticket Information 
  2.        This display the ticket Information--->
  3.  
  4. <!---<input type="hidden" value="0" id="theValue" />--->
  5.     <div id="dynamicInput"> 
  6.      <!--- All Ticket Information Appears Here--->
  7.  
  8.  
  9.     <!--- Shows what was previously entered for Model No, Product Type, and Type of Hardware Failure  --->
  10. <cfset count = 0>
  11. <!---<cfif serial.recordcount is 0>--->
  12. <cfoutput query="serial">
  13. <cfset model_no = #model_no#>
  14. <cfset product_type = #product_type#>
  15. <cfset type_hardware_failure = #type_hardware_failure#>
  16. <cfset software_hardware = #software_hardware#>
  17. <cfset resolution_verified_by = #resolution_verified_by#>
  18. <cfset dept_responsibility = #dept_responsibility#>
  19. <cfset count = count + 1>
  20. <div id="dynamic#count#Input">
  21. <table class="zpExpandedTable" id="modeltable"> 
  22. <th class="sectiontitletick" colspan="7">
  23. Serial Information #count# </th>
  24. <tr>
  25. <td id="paddingformultitop">Model No:&nbsp;&nbsp;&nbsp;&nbsp;</td>
  26. </td>
  27. <td>
  28. <select name="modelno_#count#">
  29. <option value="">Make A Selection</option>
  30. <cfloop query="models">
  31. <option value="#model#"<cfif #model# is #model_no#>selected</cfif>>#model#</option>
  32. </cfloop> 
  33. </select>
  34. </td>
  35. <td>
  36. &nbsp;&nbsp;&nbsp;&nbsp;Product Type:
  37. </td>
  38. <td>
  39. <select name="producttype_#count#">
  40. <option value="" selected>No Choice</option>
  41. <cfloop query="getProdType">
  42. <option value="#pk_productType#"<cfif #pk_productType# is #product_type#>selected</cfif>>#pk_productType#</option> 
  43. </cfloop>
  44. </select>
  45. </td>
  46. <td class="red">
  47. &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Type Of Hardware Failure*:
  48. </td>
  49. <td>
  50. <select name="hardwarefailure_#count#">
  51. <option value="" selected>Make A Selection</option>
  52. <cfloop query="getHardwareFail">
  53. <option value="#pk_hardwareFailure#"<cfif #pk_hardwareFailure# is #type_hardware_failure#>selected</cfif>>#pk_hardwareFailure#</option>
  54. </cfloop>
  55. </select>
  56. </td>
  57. </tr>
  58. <table>
  59.  
  60. <!--- Shows what was previously entered for Serial Number and Software/Hardware  --->
  61. <table class="zpExpandedTable" id="modeltable">
  62. <tr>
  63. <td id="paddingformultitop">
  64. Serial Number:&nbsp;&nbsp;
  65. <input type="text" name="serialnum_#count#" value="#pka_serialNo#">
  66. &nbsp;&nbsp;&nbsp;&nbsp;Software/Hardware:&nbsp;&nbsp;
  67. <select name="softhardware_#count#">
  68. <option value="" selected>No Choice</option>
  69. <cfloop query="getSoftHard">
  70. <option value="#pk_softwareHardware#"<cfif #pk_softwareHardware# is #software_hardware#>selected</cfif>>#pk_softwareHardware#</option>
  71. </cfloop>
  72. </select>
  73. </td>
  74. </tr>
  75. </table>
  76.  
  77. <!--- Shows what was previously entered for Description ---> 
  78.  
  79. <table class="zpExpandedTable" id="resoltable" cellpadding="3" cellspacing="0">
  80. <tr>
  81. <td id="paddingformutli">
  82. Description:&nbsp;&nbsp;
  83. </td>
  84. <td class="descriptionmoveinmulti">
  85. #description#
  86. ( You may enter up to 1000 characters. )
  87. <br>
  88. <textarea maxlength='1000' onkeyup='return descriptionmaxlength(this)' onkeydown='return descriptionmaxlength(this)'rows='4' cols='60' name="thedescription_#count#"></textarea>
  89. </td>
  90. </tr>
  91. </table>
  92.  
  93. <!---Shows what was previously entered for Resolution  --->
  94.  
  95. <table class="zpExpandedTable" id="resoltable" cellpadding="1" cellspacing="0">
  96. <tr>
  97. <td id="paddingformutli">
  98. Resolution:&nbsp;&nbsp;
  99. </td>
  100. <td class="resolutionmoveinmulti">
  101. ( You may enter up to 1500 characters. )
  102. <br>
  103. <textarea  maxlength="1500" onkeyup="return resolutionismaxlength(this)" onkeydown="return resolutionismaxlength(this)" rows="4" cols="60" name="resolution_#count#">#resolution#</textarea>
  104. </td>
  105. </tr>
  106. </table>
  107.  
  108. <!--- Shows what was previously entered for Resolution Date, Current Date (for resolution date) and resolution vertified as effective by  --->
  109.  
  110. <table class="zpExpandedTable" id="resoldatetab" cellpadding="1" cellspacing="0">
  111. <tr>
  112. <td id="paddingformultitop">
  113. Resolution Date:&nbsp;(MM/DD/YYYY)&nbsp;&nbsp;
  114. </td>
  115. <td>
  116. <input type="text" name="resdate_#count#" value="#DateFormat(resolution_date,'mm/dd/yyyy')#">&nbsp;&nbsp;
  117. &nbsp;&nbsp;&nbsp;&nbsp;Current Date:&nbsp;&nbsp;
  118. <input type="checkbox" name="currentdateresol_#count#" 
  119. onClick="resdate_#count#.value=fill_date()">
  120. </td>
  121. <td>
  122. Resolution Verified as effective by:&nbsp;&nbsp;
  123. </td>
  124. <td>
  125. <select name="resvertified_#count#">
  126. <option value="" selected>Make A Selection</option>
  127. <cfloop query="gettech">
  128. <option value="#fname# #lname#"<cfif "#fname# #lname#" is #resolution_verified_by#>
  129. selected</cfif>>#fname# #lname#</option>
  130. </cfloop>
  131. </select>
  132. </td>
  133. </tr>
  134. </table>
  135. <!--- Shows what was previously entered for Vertification Date, Current Date (for vertification date)   --->
  136. <table class="zpExpandedTable" id="resoltable" cellpadding="1" cellspacing="0">
  137. <tr>
  138. <td id="paddingformultitop">
  139. Verification Date:&nbsp;(MM/DD/YYYY)&nbsp;&nbsp;
  140. </td>
  141. <td class="vertificationmoveinmulti">
  142. <input type="text" name="vertifidate_#count#" value="#DateFormat(verification_date,'mm/dd/yyyy')#">&nbsp;&nbsp;
  143. &nbsp;&nbsp;&nbsp;&nbsp;Current Date:&nbsp;&nbsp;
  144. <input type="checkbox" name="currentdatevert_#count#" 
  145. onClick="vertifidate_#count#.value=fill_date()">
  146. </td>
  147. </tr>
  148. </table>
  149.  
  150. <!--- Shows what was previously entered for Dept/Vendor Responsibility  --->
  151. <table class="zpExpandedTable" id="resoltable" cellpadding="1" cellspacing="0">
  152. <tr>
  153. <td class="red" id="paddingformultitop">
  154. Dept/Vendor Responsibility*:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
  155. <select name="deptvendor_#count#">
  156. <option value="" selected>Make A Selection</option>
  157. <cfloop query="getDeptVendor">
  158. <option value="#pk_deptVendor#"<cfif #pk_deptVendor# is #dept_responsibility#>selected</cfif>>#pk_deptVendor#</option>
  159. </cfloop>
  160. </select>
  161. </td>
  162. </tr>
  163. </table>
  164.  
  165. <!--- Shows what was previously entered for RMA Data Only  --->
  166. <table class="zpExpandedTable" id="resoltable" cellpadding="1" cellspacing="0">
  167. <tr>
  168. <td id="paddingformultitop">
  169. RMA Data Only:&nbsp;&nbsp;&nbsp;&nbsp;
  170. </td>
  171. <td class="rmanmoveinmulti">
  172. ( You may enter up to 1000 characters. )
  173. <br/>
  174. <textarea maxlength="1000" onkeyup="return rmaismaxlength(this)" onkeydown="return rmaismaxlength(this)" rows="4" cols="60" name="rma_#count#" >#rma_data#</textarea>
  175. </td>
  176. </tr>
  177. </table>
  178. <input type="hidden" name="serialcount" value="#count#">
  179.  
  180. <!--- Adds Delete to every ticket  --->
  181. <table class="zpExpandedTable" id="resoltable" cellpadding="1" cellspacing="0">
  182. <tr>
  183. <td>
  184. <input type="button" class="removeticket" value="Remove Serial #count#" onclick="removeElement('dynamic#count#Input')">
  185. </td>
  186. </tr>
  187. <tr>
  188. <td>
  189. <!---<input type='button' class='addticket' value='Add Parts' onclick="addpartInput('divIdName')">--->
  190. </td>
  191. </tr>
  192. </table>
  193.  
  194. <cfset count1 = 0>
  195. <!---<cfif serial.recordcount is 0>--->
  196. <cfloop query="parts">
  197. <cfset count1 = count1 + 1>
  198. <!---THIS IS THE PARTS SECTION--->
  199. <table class="zpExpandedTable" id="resoltable" cellpadding="1" cellspacing="0">
  200. <th class="sectiontitle" colspan="7">Parts Information #count1# Serial Information #count#</th>
  201. <tr>
  202. <td class='indent' id='formfieldpadding'>HC P/N:&nbsp;&nbsp;&nbsp;<input type='text' name="hcpn_#count1#" style="margin:0px" value="#hc_partNo#"></td>
  203. <td class="red">
  204. Parts been returned*
  205. <input type="checkbox" name="partsreturn_#count1#" value="#part_returned#"<cfif #part_returned# eq "1">checked=yes</cfif>/>
  206. </td>
  207. <td>
  208. <td class="indent">Defective<input type="checkbox" name="defective_#count#1" value="#defective#"<cfif #defective# eq "1">checked=yes</cfif>/></td>
  209. </td>
  210. </tr>
  211. </table>
  212.  
  213. <!--- Adds Delete to every ticket  --->
  214. <table class="zpExpandedTable" id="resoltable" cellpadding="1" cellspacing="0">
  215. <tr>
  216. <td>
  217. <input type="button" class="removeticket" value="Remove Parts #count1#" 
  218. onclick="removetheElement('partIdName')"></a>
  219. </td>
  220. </td>
  221. </tr>
  222. </table>
  223. </cfloop>
  224. </div>
  225. </cfoutput>
  226. <input type="hidden" value="<cfoutput>#count#</cfoutput>" name="theValue" id="theValue" />
  227.  
  228.  
  229. <!---If have any blanks in serial table it will make field appear--->
  230. <cfoutput query="serial">
  231. <cfset model_no = #model_no#>
  232. <cfset product_type = #product_type#>
  233. <cfset type_hardware_failure = #type_hardware_failure#>
  234. <cfset pka_serialNo = #pka_serialNo#>
  235. <cfset software_hardware = #software_hardware#>
  236. <cfset description = #description#>
  237. <cfset resolution = #resolution#>
  238. <cfset resolution_date = #resolution_date#>
  239. <cfset resolution_verified_by = #resolution_verified_by#>
  240. <cfset verification_date = #verification_date#>
  241. <cfset dept_responsibility = #dept_responsibility#>
  242. <cfset rma_data = #rma_data#>
  243. </cfoutput>
  244. <cfif serial.recordcount is 0>
  245. <cfset model_no = "">
  246. <cfset product_type = "">
  247. <cfset type_hardware_failure = "">
  248. <cfset pka_serialNo = "">
  249. <cfset software_hardware = "">
  250. <cfset description = "">
  251. <cfset resolution = "">
  252. <cfset resolution_date = "">
  253. <cfset resolution_verified_by = "">
  254. <cfset verification_date = "">
  255. <cfset dept_responsibility = "">
  256. <cfset rma_data = "">
  257. <cfinclude template="serialdisplay.cfm">
  258. </cfif>
  259. <!---
  260. <cfset count = 0>
  261. <cfoutput query="parts">
  262. <cfset count = count + 1>
  263. <div id="partscount#count#">
  264.  
  265. <table class="zpExpandedTable" id="resoltable" cellpadding="1" cellspacing="0">
  266. <tr>
  267. <td class="indent" id="formfieldpadding">Follow up Date:(MM/DD/YYYY)&nbsp;&nbsp;&nbsp;
  268. <input type="text" name="followdate_#count#" value="" size="8"/>&nbsp;&nbsp;&nbsp;
  269. Current Date<input type="checkbox" name="followcheck_#count#" value="" onClick="followdate.value=fill_date()"/></td>
  270. <td>On Site:</td>
  271. <td><select name="onsite_#count#">
  272. <option value="No Choice" selected>No Choice</option>
  273. <option value="Yes">Yes</option>
  274. <option value="No">No</option>
  275. </select><td>
  276. <td># of Onsite:</td><td><select name="numonsite_#count#">
  277. <option value="" selected>No Choice</option>
  278. <cfloop from="0" to="10" index="nonsite"><cfoutput>
  279. <option value="#nonsite#">#nonsite#</option></cfoutput></cfloop>
  280. </select><td>
  281. </tr>
  282. </table>
  283. <input type="hidden" name="serialcount" value="#count#">
  284.  
  285.  
  286.  
  287.  
  288. <!--- Parts Information, display parts if have or don't have a value --->
  289. <cfoutput query="parts">
  290. <cfset hc_partNo = #hc_partNo#>
  291. <cfset part_returned = #part_returned#>
  292. <cfset defective = #defective#>
  293. <cfinclude template="partsdisplay.cfm">
  294. </cfoutput>
  295. <cfif parts.recordcount is 0>
  296. <cfset hc_partNo = "">
  297. <cfset part_returned = "">
  298. <cfset defective = "">
  299. <cfoutput><cfinclude template="partsdisplay.cfm"></cfoutput>
  300. </cfif>
  301.  
  302. ---></div>
  303.      <input type="button" class="addticket" value="Add Serial" onClick="addInput('dynamicInput');" >
  304.  
  305.  
Thank you in advance,
Rach
Oct 16 '08
482 24510
acoder
16,027 Expert Mod 8TB
Ok so would this be right or does this need to be under the cfset or the cfquery for the serialinsetupdate?
No, that would be correct, though you'll need to change the condition to neq. You also need to pass the serial number too.
and here is what i had previously for the stored procedure. But just not sure how to do the part where you said delete from the serial table where the inputs match . are you meaning when serial and the ticket number match?
Yes, and the stored procedure looks right.
Nov 12 '08 #251
bonneylake
769 512MB
No, that would be correct, though you'll need to change the condition to neq. You also need to pass the serial number too.
Yes, and the stored procedure looks right.
Hey Acoder,

So is this all correct an ready to try?

Expand|Select|Wrap|Line Numbers
  1. <CFIF REQUEST_METHOD EQ "POST">
  2.  <!---<CFSET machineListLen = listLen(Form.serialcount)>--->
  3.  <cfset currSerialNo = 1>
  4.  <CFLOOP list="#form.serialcount#" index="machineCount">
  5.   <cfloop condition="currSerialNo neq machinecount">
  6.  <cfquery name="deleteserialparts" datasource="CustomerSupport">
  7.  exec usp_CS_Deleteserialparts '#Form.pk_ticketID#',
  8.   <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">
  9.  </cfquery>
  10.  <cfset currSerialNo = currSerialNo + 1>
  11.  </cfloop>
  12. <cfif machineCount eq currSerialNo>
  13.  <CFSET serialnum       = Form["serialnum_" & machineCount]>
  14.   <CFSET modelno         = Form["modelno_" & machineCount]>
  15.   <CFSET producttype     = Form["producttype_" & machineCount]>
  16.  <CFSET softhardware    = Form["softhardware_" & machineCount]>
  17.   <CFSET resolution      = Form["resolution_" & machineCount]>
  18.   <CFSET resdate         = Form["resdate_" & machineCount]>
  19.   <CFSET resvertified    = Form["resvertified_" & machineCount]>
  20. <CFSET vertifidate     = Form["vertifidate_" & machineCount]>
  21. <CFSET deptvendor      = Form["deptvendor_" & machinecount]>
  22.  <CFSET hardwarefailure = Form["hardwarefailure_" & machineCount]>
  23.  <CFSET thedescription  = Form["thedescription_" & machineCount]>
  24.  
  25.  
  26.  <!--- inserts information into the serial table--->
  27.  <cfquery name="serialinsertupdate" datasource="CustomerSupport">
  28.     exec usp_CS_Updateinsertserial 
  29.      <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  30.      "#Form.pk_ticketID#",
  31.      <cfqueryparam value="#modelno#" CFSQLType = "CF_SQL_VARCHAR">,
  32.       <cfqueryparam value="#producttype#" CFSQLType = "CF_SQL_VARCHAR">,
  33.      <cfqueryparam value="#softhardware#" CFSQLType = "CF_SQL_VARCHAR">,
  34.      <cfqueryparam value="#resolution#" CFSQLType = "CF_SQL_VARCHAR">,
  35.      <cfqueryparam value="#resdate#">,
  36.      <cfqueryparam value="#resvertified#" CFSQLType = "CF_SQL_VARCHAR">,
  37.      <cfqueryparam value="#vertifidate#">,
  38.     <cfqueryparam value="#deptvendor#" CFSQLType = "CF_SQL_VARCHAR">,
  39.      <cfqueryparam value="#hardwarefailure#" CFSQLType = "CF_SQL_VARCHAR">   
  40.      </cfquery>
  41.  
  42.  <!---Inserts information into notes_descr table.--->
  43.  <cfquery name="description" datasource="CustomerSupport">
  44.     exec usp_CS_Insertdescription
  45.    <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  46.    '#Form.pk_ticketID#',
  47.     <cfqueryparam value="#thedescription#" CFSQLType = "CF_SQL_VARCHAR">,
  48.    '#Form.fk_addedBy#'
  49.  </cfquery>
  50.  
  51.   <!---Inserts parts information into parts table.--->
  52.   <!---because it is a bit we don't use 'ticks' around defective for parts table--->
  53.  <cfparam name="form.partcounter#machinecount#" default="">
  54.  <cfloop list="#form['partscount' & machinecount]#" index="ps">
  55. <cfparam name="Form.defective_#ps#_#machinecount#" default="0">
  56.   <cfparam name="Form.partsreturn_#ps#_#machinecount#" default="0">
  57.  <CFSET hcpn            = Form["hcpn_" & "#ps#" & "_#machinecount#"]>
  58.  <CFSET partsreturn     = Form["partsreturn_" & "#ps#" & "_#machinecount#"]>
  59.  <CFSET defective       = Form["defective_" & "#ps#" & "_#machinecount#"]>
  60.  <CFSET rma             = Form["rma_" & "#ps#" & "_#machineCount#"]>
  61. <cfquery name="parts" datasource="CustomerSupport">
  62.    exec usp_CS_Updateinsertparts 
  63.   <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  64.    '#Form.pk_ticketID#',
  65.     <cfqueryparam value="#hcpn#" CFSQLType = "CF_SQL_VARCHAR">,
  66.     <cfqueryparam value="#partsreturn#" CFSQLType = "CF_SQL_VARCHAR">,
  67.     <cfqueryparam value="#rma#" CFSQLType = "CF_SQL_VARCHAR">,
  68.    <cfqueryparam value="#defective#" CFSQLType = "CF_SQL_BIT">
  69.  
  70.  </cfquery> 
  71.  
  72. </cfloop>
  73. </CFLOOP>
  74.  </CFIF>
  75.  
Thank you,
Rach
Nov 12 '08 #252
acoder
16,027 Expert Mod 8TB
If it's test data, it's always ready to try ;)

You just need to swap ticket and serial numbers around for the query.
Nov 12 '08 #253
bonneylake
769 512MB
If it's test data, it's always ready to try ;)

You just need to swap ticket and serial numbers around for the query.
Hey Acoder,

I tried it an its wanting a end </cfif> to go with
<cfif machineCount eq currSerialNo> an i looked back at your example an i think i might of had one part wrong. so does this seem more correct?

Expand|Select|Wrap|Line Numbers
  1. <CFIF REQUEST_METHOD EQ "POST">
  2.  <!---<CFSET machineListLen = listLen(Form.serialcount)>--->
  3.  <cfset currSerialNo = 1>
  4.  <CFLOOP list="#form.serialcount#" index="machineCount">
  5.  <cfif machineCount eq currSerialNo>
  6.   <cfloop condition="currSerialNo neq machinecount">
  7.  <cfquery name="deleteserialparts" datasource="CustomerSupport">
  8.  exec usp_CS_Deleteserialparts   
  9.  <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  10.  '#Form.pk_ticketID#'
  11.  </cfquery>
  12.  <cfset currSerialNo = currSerialNo + 1>
  13.  </cfloop>
  14.  </cfif>
  15.  <CFSET serialnum       = Form["serialnum_" & machineCount]>
  16.   <CFSET modelno         = Form["modelno_" & machineCount]>
  17.   <CFSET producttype     = Form["producttype_" & machineCount]>
  18.  <CFSET softhardware    = Form["softhardware_" & machineCount]>
  19.   <CFSET resolution      = Form["resolution_" & machineCount]>
  20.   <CFSET resdate         = Form["resdate_" & machineCount]>
  21.   <CFSET resvertified    = Form["resvertified_" & machineCount]>
  22. <CFSET vertifidate     = Form["vertifidate_" & machineCount]>
  23. <CFSET deptvendor      = Form["deptvendor_" & machinecount]>
  24.  <CFSET hardwarefailure = Form["hardwarefailure_" & machineCount]>
  25.  <CFSET thedescription  = Form["thedescription_" & machineCount]>

Thank you :),
Rach
Nov 12 '08 #254
acoder
16,027 Expert Mod 8TB
Well, you don't need that cfif line at all, and you also need to add a currSerialNo increment line within the main loop, e.g. after parts on line 73.
Nov 12 '08 #255
bonneylake
769 512MB
Well, you don't need that cfif line at all, and you also need to add a currSerialNo increment line within the main loop, e.g. after parts on line 73.
Hey Acoder,

Ok so this should be correct?

Expand|Select|Wrap|Line Numbers
  1. <CFIF REQUEST_METHOD EQ "POST">
  2.  <!---<CFSET machineListLen = listLen(Form.serialcount)>--->
  3.  <cfset currSerialNo = 1>
  4.  <CFLOOP list="#form.serialcount#" index="machineCount">
  5.  
  6.   <cfloop condition="currSerialNo neq machinecount">
  7.  <cfquery name="deleteserialparts" datasource="CustomerSupport">
  8.  exec usp_CS_Deleteserialparts   
  9.  <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  10.  '#Form.pk_ticketID#'
  11.  </cfquery>
  12.  
  13.  </cfloop>
  14.  </cfif>
  15.  <CFSET serialnum       = Form["serialnum_" & machineCount]>
  16.   <CFSET modelno         = Form["modelno_" & machineCount]>
  17.   <CFSET producttype     = Form["producttype_" & machineCount]>
  18.  <CFSET softhardware    = Form["softhardware_" & machineCount]>
  19.   <CFSET resolution      = Form["resolution_" & machineCount]>
  20.   <CFSET resdate         = Form["resdate_" & machineCount]>
  21.   <CFSET resvertified    = Form["resvertified_" & machineCount]>
  22. <CFSET vertifidate     = Form["vertifidate_" & machineCount]>
  23. <CFSET deptvendor      = Form["deptvendor_" & machinecount]>
  24.  <CFSET hardwarefailure = Form["hardwarefailure_" & machineCount]>
  25.  <CFSET thedescription  = Form["thedescription_" & machineCount]>
  26.  
  27.  
  28.  <!--- inserts information into the serial table--->
  29.  <cfquery name="serialinsertupdate" datasource="CustomerSupport">
  30.     exec usp_CS_Updateinsertserial 
  31.      <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  32.      "#Form.pk_ticketID#",
  33.      <cfqueryparam value="#modelno#" CFSQLType = "CF_SQL_VARCHAR">,
  34.       <cfqueryparam value="#producttype#" CFSQLType = "CF_SQL_VARCHAR">,
  35.      <cfqueryparam value="#softhardware#" CFSQLType = "CF_SQL_VARCHAR">,
  36.      <cfqueryparam value="#resolution#" CFSQLType = "CF_SQL_VARCHAR">,
  37.      <cfqueryparam value="#resdate#">,
  38.      <cfqueryparam value="#resvertified#" CFSQLType = "CF_SQL_VARCHAR">,
  39.      <cfqueryparam value="#vertifidate#">,
  40.     <cfqueryparam value="#deptvendor#" CFSQLType = "CF_SQL_VARCHAR">,
  41.      <cfqueryparam value="#hardwarefailure#" CFSQLType = "CF_SQL_VARCHAR">   
  42.      </cfquery>
  43.  
  44.  <!---Inserts information into notes_descr table.--->
  45.  <cfquery name="description" datasource="CustomerSupport">
  46.     exec usp_CS_Insertdescription
  47.    <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  48.    '#Form.pk_ticketID#',
  49.     <cfqueryparam value="#thedescription#" CFSQLType = "CF_SQL_VARCHAR">,
  50.    '#Form.fk_addedBy#'
  51.  </cfquery>
  52.  
  53.   <!---Inserts parts information into parts table.--->
  54.   <!---because it is a bit we don't use 'ticks' around defective for parts table--->
  55.  <cfparam name="form.partcounter#machinecount#" default="">
  56.  <cfloop list="#form['partscount' & machinecount]#" index="ps">
  57. <cfparam name="Form.defective_#ps#_#machinecount#" default="0">
  58.   <cfparam name="Form.partsreturn_#ps#_#machinecount#" default="0">
  59.  <CFSET hcpn            = Form["hcpn_" & "#ps#" & "_#machinecount#"]>
  60.  <CFSET partsreturn     = Form["partsreturn_" & "#ps#" & "_#machinecount#"]>
  61.  <CFSET defective       = Form["defective_" & "#ps#" & "_#machinecount#"]>
  62.  <CFSET rma             = Form["rma_" & "#ps#" & "_#machineCount#"]>
  63. <cfquery name="parts" datasource="CustomerSupport">
  64.    exec usp_CS_Updateinsertparts 
  65.   <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  66.    '#Form.pk_ticketID#',
  67.     <cfqueryparam value="#hcpn#" CFSQLType = "CF_SQL_VARCHAR">,
  68.     <cfqueryparam value="#partsreturn#" CFSQLType = "CF_SQL_VARCHAR">,
  69.     <cfqueryparam value="#rma#" CFSQLType = "CF_SQL_VARCHAR">,
  70.    <cfqueryparam value="#defective#" CFSQLType = "CF_SQL_BIT">
  71.  
  72.  </cfquery> 
  73.  
  74. </cfloop>
  75.  <cfset currSerialNo = currSerialNo + 1>
  76. </CFLOOP>
  77.  </CFIF>
Thank you,
Rach
Nov 12 '08 #256
acoder
16,027 Expert Mod 8TB
Where did the currSerialNum increment line go - you need it in both places.

You also have a stray ending </cfif > tag on line 14 that's not required.
Nov 12 '08 #257
bonneylake
769 512MB
Where did the currSerialNum increment line go - you need it in both places.

You also have a stray ending </cfif > tag on line 14 that's not required.
Hey Acoder,

Here is what i have in full, is this correct? i think i am still missing something because i tried it an it didn't work
Expand|Select|Wrap|Line Numbers
  1. <CFIF REQUEST_METHOD EQ "POST">
  2.  <!---<CFSET machineListLen = listLen(Form.serialcount)>--->
  3.  <cfset currSerialNo = 1>
  4.  <CFLOOP list="#form.serialcount#" index="machineCount">
  5. <!--- <cfif machineCount eq currSerialNo>--->
  6.   <cfloop condition="currSerialNo neq machinecount">
  7.  <cfquery name="deleteserialparts" datasource="CustomerSupport">
  8.  exec usp_CS_Deleteserialparts   
  9.  <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  10.  '#Form.pk_ticketID#'
  11.  </cfquery>
  12.  <cfset currSerialNo = currSerialNo + 1>
  13.  </cfloop>
  14.  
  15.  <CFSET serialnum       = Form["serialnum_" & machineCount]>
  16.   <CFSET modelno         = Form["modelno_" & machineCount]>
  17.   <CFSET producttype     = Form["producttype_" & machineCount]>
  18.  <CFSET softhardware    = Form["softhardware_" & machineCount]>
  19.   <CFSET resolution      = Form["resolution_" & machineCount]>
  20.   <CFSET resdate         = Form["resdate_" & machineCount]>
  21.   <CFSET resvertified    = Form["resvertified_" & machineCount]>
  22. <CFSET vertifidate     = Form["vertifidate_" & machineCount]>
  23. <CFSET deptvendor      = Form["deptvendor_" & machinecount]>
  24.  <CFSET hardwarefailure = Form["hardwarefailure_" & machineCount]>
  25.  <CFSET thedescription  = Form["thedescription_" & machineCount]>
  26.  
  27.  
  28.  <!--- inserts information into the serial table--->
  29.  <cfquery name="serialinsertupdate" datasource="CustomerSupport">
  30.     exec usp_CS_Updateinsertserial 
  31.      <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  32.      "#Form.pk_ticketID#",
  33.      <cfqueryparam value="#modelno#" CFSQLType = "CF_SQL_VARCHAR">,
  34.       <cfqueryparam value="#producttype#" CFSQLType = "CF_SQL_VARCHAR">,
  35.      <cfqueryparam value="#softhardware#" CFSQLType = "CF_SQL_VARCHAR">,
  36.      <cfqueryparam value="#resolution#" CFSQLType = "CF_SQL_VARCHAR">,
  37.      <cfqueryparam value="#resdate#">,
  38.      <cfqueryparam value="#resvertified#" CFSQLType = "CF_SQL_VARCHAR">,
  39.      <cfqueryparam value="#vertifidate#">,
  40.     <cfqueryparam value="#deptvendor#" CFSQLType = "CF_SQL_VARCHAR">,
  41.      <cfqueryparam value="#hardwarefailure#" CFSQLType = "CF_SQL_VARCHAR">   
  42.      </cfquery>
  43.  
  44.  <!---Inserts information into notes_descr table.--->
  45.  <cfquery name="description" datasource="CustomerSupport">
  46.     exec usp_CS_Insertdescription
  47.    <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  48.    '#Form.pk_ticketID#',
  49.     <cfqueryparam value="#thedescription#" CFSQLType = "CF_SQL_VARCHAR">,
  50.    '#Form.fk_addedBy#'
  51.  </cfquery>
  52.  
  53.   <!---Inserts parts information into parts table.--->
  54.   <!---because it is a bit we don't use 'ticks' around defective for parts table--->
  55.  <cfparam name="form.partcounter#machinecount#" default="">
  56.  <cfloop list="#form['partscount' & machinecount]#" index="ps">
  57. <cfparam name="Form.defective_#ps#_#machinecount#" default="0">
  58.   <cfparam name="Form.partsreturn_#ps#_#machinecount#" default="0">
  59.  <CFSET hcpn            = Form["hcpn_" & "#ps#" & "_#machinecount#"]>
  60.  <CFSET partsreturn     = Form["partsreturn_" & "#ps#" & "_#machinecount#"]>
  61.  <CFSET defective       = Form["defective_" & "#ps#" & "_#machinecount#"]>
  62.  <CFSET rma             = Form["rma_" & "#ps#" & "_#machineCount#"]>
  63. <cfquery name="parts" datasource="CustomerSupport">
  64.    exec usp_CS_Updateinsertparts 
  65.   <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  66.    '#Form.pk_ticketID#',
  67.     <cfqueryparam value="#hcpn#" CFSQLType = "CF_SQL_VARCHAR">,
  68.     <cfqueryparam value="#partsreturn#" CFSQLType = "CF_SQL_VARCHAR">,
  69.     <cfqueryparam value="#rma#" CFSQLType = "CF_SQL_VARCHAR">,
  70.    <cfqueryparam value="#defective#" CFSQLType = "CF_SQL_BIT">
  71.  </cfquery> 
  72.  
  73. </cfloop>
  74.  <cfset currSerialNo = currSerialNo + 1>
  75. </CFLOOP>
  76.  </CFIF>
  77.  
Thank you,
Rach
Nov 12 '08 #258
acoder
16,027 Expert Mod 8TB
When you say it doesn't work, what happens? What did you test with?
Nov 12 '08 #259
bonneylake
769 512MB
When you say it doesn't work, what happens? What did you test with?
Hey Acoder,

I tested with the previous code. An i had the following

serial 1
parts 1
serial 2
parts 2

an i deleted serial 2 and parts 2 so when i submitted i had

serial 1
parts 1

when i submitted i didn't get a single error. However, when i went back to see that serial 2 parts 2 should be gone it was still there.

Thank you,
Rach
Nov 12 '08 #260
acoder
16,027 Expert Mod 8TB
Oh, I see why it didn't work in that scenario. The main cfloop loops till the serial count. Since the serial count would only be 1, it would ignore 2 completely. Now, if you'd had 1, 2 and 3 and deleted 2, then it would've worked.

The way this inner loop works is that any missing serial counts in between are deleted. Since there could be a number of them, you have to use a loop until the number matches the current loop serial count number.

To solve this particular conundrum, find out the number of serials for the ticket. This would be the number of serial counts in total. You have to make this query before any deletions. Then at the end of the main cfloop (outside it), delete the remaining serials in a loop similar to the inner one above. Loop from the last serial count+1 till the number of serials altogether.
Nov 13 '08 #261
bonneylake
769 512MB
Oh, I see why it didn't work in that scenario. The main cfloop loops till the serial count. Since the serial count would only be 1, it would ignore 2 completely. Now, if you'd had 1, 2 and 3 and deleted 2, then it would've worked.

The way this inner loop works is that any missing serial counts in between are deleted. Since there could be a number of them, you have to use a loop until the number matches the current loop serial count number.

To solve this particular conundrum, find out the number of serials for the ticket. This would be the number of serial counts in total. You have to make this query before any deletions. Then at the end of the main cfloop (outside it), delete the remaining serials in a loop similar to the inner one above. Loop from the last serial count+1 till the number of serials altogether.
Hey Acoder,

kinda confused. Ok to find the number of serials for the ticket i would use form.serialcount correct? then i am not sure what you mean about making another query? an then at the very end your saying to make another cfloop, what type of cfloop? an then i would put the cfloop at the end but would i use the delete query i have now inside the last cfloop?

here is what i tried but i think i am still off. still off on how to do the cfloop at the bottom an off on how to find out the number of serials.

at the beginning i added the following

Expand|Select|Wrap|Line Numbers
  1. <CFIF REQUEST_METHOD EQ "POST">
  2.  <!---<CFSET machineListLen = listLen(Form.serialcount)>--->
  3.  <cfset sercount = #form.serialcount#>
  4.  <cfset currSerialNo = 1>
  5.  <CFLOOP list="#form.serialcount#" index="machineCount">
and at the bottom i added this
Expand|Select|Wrap|Line Numbers
  1. </cfloop>
  2.  <cfset currSerialNo = currSerialNo + 1>
  3. </CFLOOP>
  4.  <cfloop list="#Form.serialcount#" index="ds">
  5. <cfquery name="deleteserialparts" datasource="CustomerSupport">
  6. exec usp_CS_Deleteserialparts   
  7. <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  8. '#Form.pk_ticketID#'
  9. </cfquery>
  10. <cfset sercount = sercount + 1>
  11. </cfloop>
  12.  </CFIF>
Thank you,
Rach
Nov 13 '08 #262
acoder
16,027 Expert Mod 8TB
I said:
To solve this particular conundrum, find out the number of serials for the ticket. This would be the number of serial counts in total. You have to make this query before any deletions. Then at the end of the main cfloop (outside it), delete the remaining serials in a loop similar to the inner one above. Loop from the last serial count+1 till the number of serials altogether.
but there's a mistake there. It should be "This would be the number of serials in total." not 'serial counts'. So, you need a query which returns the number of serials for that ticket. You probably won't have this, so create one. For the loop, the condition is that you loop until currSerialNum eq this total number.
Nov 13 '08 #263
bonneylake
769 512MB
I said:but there's a mistake there. It should be "This would be the number of serials in total." not 'serial counts'. So, you need a query which returns the number of serials for that ticket. You probably won't have this, so create one. For the loop, the condition is that you loop until currSerialNum eq this total number.
Hey Acoder,

Is there a way i could do this without making another stored procedure? i would like to not have to do any more stored procedures then i already have but i am not sure how i would go about it i thought about doing something like below but i am not sure what else it would need to be able to accomplish counting.

Expand|Select|Wrap|Line Numbers
  1.  <cfquery name="countserials" datasource="CustomerSupport">
  2. SELECT *
  3.  FROM dbo.tbl_CS_serial
  4.  where pka_serialNo=#pka_serialNo# and pkb_fk_ticketNo = #pkb_fk_ticketNo#
  5.  </cfquery>
  6.  
Thank you,
Rach
Nov 13 '08 #264
acoder
16,027 Expert Mod 8TB
Yes, you could use a normal query.

You need a count:
Expand|Select|Wrap|Line Numbers
  1. SELECT count(*) as serialcount
and only the ticket as the criterion.
Nov 13 '08 #265
bonneylake
769 512MB
Yes, you could use a normal query.

You need a count:
Expand|Select|Wrap|Line Numbers
  1. SELECT count(*) as serialcount
and only the ticket as the criterion.
Hey Acoder,

So would this be correct?

Expand|Select|Wrap|Line Numbers
  1. <CFIF REQUEST_METHOD EQ "POST">
  2.  <!---<CFSET machineListLen = listLen(Form.serialcount)>--->
  3.  <cfquery name="countserials" datasource="CustomerSupport">
  4. SELECT COUNT(*) as serialcount from dbo.tbl_CS_serial where pkb_fk_ticketNo=#pkb_fk_ticketNo#
  5.  </cfquery>
  6.  <cfset currSerialNo = 1>
  7.  <CFLOOP list="#form.serialcount#" index="machineCount">
  8. <!--- <cfif machineCount eq currSerialNo>--->
  9.   <cfloop condition="currSerialNo neq machinecount">
  10.  <cfquery name="deleteserialparts" datasource="CustomerSupport">
  11.  exec usp_CS_Deleteserialparts   
  12.  <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  13.  '#Form.pk_ticketID#'
  14.  </cfquery>
  15.  <cfset currSerialNo = currSerialNo + 1>
  16.  </cfloop>
  17.  
  18.   <CFSET serialnum       = Form["serialnum_" & machineCount]>
  19.   <CFSET modelno         = Form["modelno_" & machineCount]>
  20.   <CFSET producttype     = Form["producttype_" & machineCount]>
  21.   <CFSET softhardware    = Form["softhardware_" & machineCount]>
  22.   <CFSET resolution      = Form["resolution_" & machineCount]>
  23.   <CFSET resdate         = Form["resdate_" & machineCount]>
  24.   <CFSET resvertified    = Form["resvertified_" & machineCount]>
  25.   <CFSET vertifidate     = Form["vertifidate_" & machineCount]>
  26.   <CFSET deptvendor      = Form["deptvendor_" & machinecount]>
  27.   <CFSET hardwarefailure = Form["hardwarefailure_" & machineCount]>
  28.   <CFSET thedescription  = Form["thedescription_" & machineCount]>
  29.  
  30.  
  31.  <!--- inserts information into the serial table--->
  32.  <cfquery name="serialinsertupdate" datasource="CustomerSupport">
  33.     exec usp_CS_Updateinsertserial 
  34.      <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  35.      "#Form.pk_ticketID#",
  36.      <cfqueryparam value="#modelno#" CFSQLType = "CF_SQL_VARCHAR">,
  37.       <cfqueryparam value="#producttype#" CFSQLType = "CF_SQL_VARCHAR">,
  38.      <cfqueryparam value="#softhardware#" CFSQLType = "CF_SQL_VARCHAR">,
  39.      <cfqueryparam value="#resolution#" CFSQLType = "CF_SQL_VARCHAR">,
  40.      <cfqueryparam value="#resdate#">,
  41.      <cfqueryparam value="#resvertified#" CFSQLType = "CF_SQL_VARCHAR">,
  42.      <cfqueryparam value="#vertifidate#">,
  43.     <cfqueryparam value="#deptvendor#" CFSQLType = "CF_SQL_VARCHAR">,
  44.      <cfqueryparam value="#hardwarefailure#" CFSQLType = "CF_SQL_VARCHAR">   
  45.      </cfquery>
  46.  
  47.  <!---Inserts information into notes_descr table.--->
  48.  <cfquery name="description" datasource="CustomerSupport">
  49.     exec usp_CS_Insertdescription
  50.    <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  51.    '#Form.pk_ticketID#',
  52.     <cfqueryparam value="#thedescription#" CFSQLType = "CF_SQL_VARCHAR">,
  53.    '#Form.fk_addedBy#'
  54.  </cfquery>
  55.  
  56.   <!---Inserts parts information into parts table.--->
  57.   <!---because it is a bit we don't use 'ticks' around defective for parts table--->
  58.  <cfparam name="form.partscount#machinecount#" default="">
  59.  <cfloop list="#form['partscount' & machinecount]#" index="ps">
  60. <cfparam name="Form.defective_#ps#_#machinecount#" default="0">
  61.   <cfparam name="Form.partsreturn_#ps#_#machinecount#" default="0">
  62.  <CFSET hcpn            = Form["hcpn_" & "#ps#" & "_#machinecount#"]>
  63.  <CFSET partsreturn     = Form["partsreturn_" & "#ps#" & "_#machinecount#"]>
  64.  <CFSET defective       = Form["defective_" & "#ps#" & "_#machinecount#"]>
  65.  <CFSET rma             = Form["rma_" & "#ps#" & "_#machineCount#"]>
  66. <cfquery name="parts" datasource="CustomerSupport">
  67.    exec usp_CS_Updateinsertparts 
  68.   <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  69.    '#Form.pk_ticketID#',
  70.     <cfqueryparam value="#hcpn#" CFSQLType = "CF_SQL_VARCHAR">,
  71.     <cfqueryparam value="#partsreturn#" CFSQLType = "CF_SQL_VARCHAR">,
  72.     <cfqueryparam value="#rma#" CFSQLType = "CF_SQL_VARCHAR">,
  73.    <cfqueryparam value="#defective#" CFSQLType = "CF_SQL_BIT">
  74.  </cfquery> 
  75. </cfloop>
  76.  <cfset currSerialNo = currSerialNo + 1>
  77. </CFLOOP>
  78.  <cfloop condition="currSerialNo neq countserials">
  79. <cfquery name="deleteserialparts" datasource="CustomerSupport">
  80. exec usp_CS_Deleteserialparts   
  81. <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  82. '#Form.pk_ticketID#'
  83. </cfquery>
  84.  
  85. </cfloop>
  86.  </CFIF>
Thank you,
Rach
Nov 13 '08 #266
acoder
16,027 Expert Mod 8TB
Two more things:
1. The loop should loop till countserials.serialcount.
2. You need to increment currSerialNum within this loop too.
Nov 13 '08 #267
bonneylake
769 512MB
Two more things:
1. The loop should loop till countserials.serialcount.
2. You need to increment currSerialNum within this loop too.
Hey Acoder,

Alright i did what you said an went to try it out an i got the following error

Error Diagnostic Information
ODBC Error Code = 37000 (Syntax error or access violation)


[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near ','.


SQL = "SELECT COUNT(*) as serialcount from dbo.tbl_CS_serial where pkb_fk_ticketNo=148,148"

Data Source = "CUSTOMERSUPPORT"


The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (52:2) to (52:59) in the template file C:\Inetpub\Development\WWWRoot\RachelB\footprints\ form\userformedit.cfm.


an the part its having trouble on is

Expand|Select|Wrap|Line Numbers
  1.  <cfquery name="countserials" datasource="CustomerSupport">
  2. SELECT COUNT(*) as serialcount from dbo.tbl_CS_serial where pkb_fk_ticketNo=#pkb_fk_ticketNo#
  3.  </cfquery>
any ideas?

Thank you :),
Rach
Nov 13 '08 #268
acoder
16,027 Expert Mod 8TB
You'll have problems using pkb_ticket, use form.pk_ticketID instead inside a cfqueryparam.
Nov 13 '08 #269
bonneylake
769 512MB
You'll have problems using pkb_ticket, use form.pk_ticketID instead inside a cfqueryparam.
Hey Acoder,

Alrighty i replaced it with

Expand|Select|Wrap|Line Numbers
  1.  <cfquery name="countserials" datasource="CustomerSupport">
  2. SELECT COUNT(*) as serialcount from dbo.tbl_CS_serial where #Form.pk_ticketID#=#Form.pk_ticketID#
  3.  </cfquery>
and i have noticed 2 problems. the first problem is i can not delete serial1 because it gives me an error saying no value for serialnum. an its got to be able to delete any serial.

an the second problem. i did a test an this is what i had before i submitted

first serial
serial 1
part 1

second serial
serial 2
part 2

i deleted the second serial and submitted. An this is what happened. Instead of the results for the first serial appearing (when i went back to look at it) It instead deleted the first serial (and part) an when it redisplayed it displayed the second serial's results.

This is what i have in full

Expand|Select|Wrap|Line Numbers
  1. <CFIF REQUEST_METHOD EQ "POST">
  2.  <!---<CFSET machineListLen = listLen(Form.serialcount)>--->
  3.  <cfquery name="countserials" datasource="CustomerSupport">
  4. SELECT COUNT(*) as serialcount from dbo.tbl_CS_serial where #Form.pk_ticketID#=#Form.pk_ticketID#
  5.  </cfquery>
  6.  
  7.  
  8.  <cfset currSerialNo = 1>
  9.  <CFLOOP list="#form.serialcount#" index="machineCount">
  10. <!--- <cfif machineCount eq currSerialNo>--->
  11.   <cfloop condition="currSerialNo neq machinecount">
  12.  <cfquery name="deleteserialparts" datasource="CustomerSupport">
  13.  exec usp_CS_Deleteserialparts   
  14.  <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  15.  '#Form.pk_ticketID#'
  16.  </cfquery>
  17.  <cfset currSerialNo = currSerialNo + 1>
  18.  </cfloop>
  19.  
  20.   <CFSET serialnum       = Form["serialnum_" & machineCount]>
  21.   <CFSET modelno         = Form["modelno_" & machineCount]>
  22.   <CFSET producttype     = Form["producttype_" & machineCount]>
  23.   <CFSET softhardware    = Form["softhardware_" & machineCount]>
  24.   <CFSET resolution      = Form["resolution_" & machineCount]>
  25.   <CFSET resdate         = Form["resdate_" & machineCount]>
  26.   <CFSET resvertified    = Form["resvertified_" & machineCount]>
  27.   <CFSET vertifidate     = Form["vertifidate_" & machineCount]>
  28.   <CFSET deptvendor      = Form["deptvendor_" & machinecount]>
  29.   <CFSET hardwarefailure = Form["hardwarefailure_" & machineCount]>
  30.   <CFSET thedescription  = Form["thedescription_" & machineCount]>
  31.  
  32.  
  33.  <!--- inserts information into the serial table--->
  34.  <cfquery name="serialinsertupdate" datasource="CustomerSupport">
  35.     exec usp_CS_Updateinsertserial 
  36.      <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  37.      "#Form.pk_ticketID#",
  38.      <cfqueryparam value="#modelno#" CFSQLType = "CF_SQL_VARCHAR">,
  39.       <cfqueryparam value="#producttype#" CFSQLType = "CF_SQL_VARCHAR">,
  40.      <cfqueryparam value="#softhardware#" CFSQLType = "CF_SQL_VARCHAR">,
  41.      <cfqueryparam value="#resolution#" CFSQLType = "CF_SQL_VARCHAR">,
  42.      <cfqueryparam value="#resdate#">,
  43.      <cfqueryparam value="#resvertified#" CFSQLType = "CF_SQL_VARCHAR">,
  44.      <cfqueryparam value="#vertifidate#">,
  45.     <cfqueryparam value="#deptvendor#" CFSQLType = "CF_SQL_VARCHAR">,
  46.      <cfqueryparam value="#hardwarefailure#" CFSQLType = "CF_SQL_VARCHAR">   
  47.      </cfquery>
  48.  
  49.  <!---Inserts information into notes_descr table.--->
  50.  <cfquery name="description" datasource="CustomerSupport">
  51.     exec usp_CS_Insertdescription
  52.    <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  53.    '#Form.pk_ticketID#',
  54.     <cfqueryparam value="#thedescription#" CFSQLType = "CF_SQL_VARCHAR">,
  55.    '#Form.fk_addedBy#'
  56.  </cfquery>
  57.  
  58.   <!---Inserts parts information into parts table.--->
  59.   <!---because it is a bit we don't use 'ticks' around defective for parts table--->
  60.  <cfparam name="form.partscount#machinecount#" default="">
  61.  <cfloop list="#form['partscount' & machinecount]#" index="ps">
  62. <cfparam name="Form.defective_#ps#_#machinecount#" default="0">
  63.   <cfparam name="Form.partsreturn_#ps#_#machinecount#" default="0">
  64.  <CFSET hcpn            = Form["hcpn_" & "#ps#" & "_#machinecount#"]>
  65.  <CFSET partsreturn     = Form["partsreturn_" & "#ps#" & "_#machinecount#"]>
  66.  <CFSET defective       = Form["defective_" & "#ps#" & "_#machinecount#"]>
  67.  <CFSET rma             = Form["rma_" & "#ps#" & "_#machineCount#"]>
  68. <cfquery name="parts" datasource="CustomerSupport">
  69.    exec usp_CS_Updateinsertparts 
  70.   <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  71.    '#Form.pk_ticketID#',
  72.     <cfqueryparam value="#hcpn#" CFSQLType = "CF_SQL_VARCHAR">,
  73.     <cfqueryparam value="#partsreturn#" CFSQLType = "CF_SQL_VARCHAR">,
  74.     <cfqueryparam value="#rma#" CFSQLType = "CF_SQL_VARCHAR">,
  75.    <cfqueryparam value="#defective#" CFSQLType = "CF_SQL_BIT">
  76.  </cfquery> 
  77. </cfloop>
  78.  <cfset currSerialNo = currSerialNo + 1>
  79. </CFLOOP>
  80.  <cfloop condition="currSerialNo neq countserials.serialcount">
  81. <cfquery name="deleteserialparts" datasource="CustomerSupport">
  82. exec usp_CS_Deleteserialparts   
  83. <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  84. '#Form.pk_ticketID#'
  85. </cfquery>
  86.  <cfset currSerialNo = currSerialNo + 1>
  87. </cfloop>
  88.  </CFIF>
  89.  
Thank you,
Rach
Nov 13 '08 #270
acoder
16,027 Expert Mod 8TB
pkb_fk_ticketNo shouldn't have been replaced:
Expand|Select|Wrap|Line Numbers
  1.  <cfquery name="countserials" datasource="CustomerSupport">
  2. SELECT COUNT(*) as serialcount from dbo.tbl_CS_serial where pkb_fk_ticketNo=<cfqueryparam value="Form.pk_ticketID#">
  3.  </cfquery>
I notice that you have serialnum in both queries, but they're not set correctly. Set serialnum to the correct value in both loops.
Nov 13 '08 #271
bonneylake
769 512MB
pkb_fk_ticketNo shouldn't have been replaced:
Expand|Select|Wrap|Line Numbers
  1.  <cfquery name="countserials" datasource="CustomerSupport">
  2. SELECT COUNT(*) as serialcount from dbo.tbl_CS_serial where pkb_fk_ticketNo=<cfqueryparam value="Form.pk_ticketID#">
  3.  </cfquery>
I notice that you have serialnum in both queries, but they're not set correctly. Set serialnum to the correct value in both loops.
Hey Acoder,

Only part i am not understanding is the part about setting serialnum to the correct value? because in the javascript and html it has the name serialnum followed by the count

Expand|Select|Wrap|Line Numbers
  1. <input type="text" name="serialnum_#count#">
Thank you,
Rach
Nov 13 '08 #272
acoder
16,027 Expert Mod 8TB
I meant in the submitted page, but now I see that the serial numbers to delete are not available anywhere. So you'll have to change the countserials query to list the serial numbers instead of counting - you'll get the count from the recordcount anyway. then use these serial numbers to delete.
Nov 13 '08 #273
bonneylake
769 512MB
I meant in the submitted page, but now I see that the serial numbers to delete are not available anywhere. So you'll have to change the countserials query to list the serial numbers instead of counting - you'll get the count from the recordcount anyway. then use these serial numbers to delete.
Hey Acoder,

Are you talking about something like this

Expand|Select|Wrap|Line Numbers
  1. <cfquery name="countserials" datasource="CustomerSupport">
  2. SELECT COUNT(*) as pka_serialNo from dbo.tbl_CS_serial where pkb_fk_ticketNo=<cfqueryparam value="#Form.pk_ticketID#">
  3.  </cfquery>
an i am still confused about changing the value in the cfloop part

Expand|Select|Wrap|Line Numbers
  1. <cfloop condition="currSerialNo neq machinecount">
  2.  <cfquery name="deleteserialparts" datasource="CustomerSupport">
  3.  exec usp_CS_Deleteserialparts   
  4.  <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  5.  '#Form.pk_ticketID#'
  6.  </cfquery>
Thank you,
Rach
Nov 13 '08 #274
acoder
16,027 Expert Mod 8TB
Not quite - you need the serials:
Expand|Select|Wrap|Line Numbers
  1. <cfquery name="countserials" datasource="CustomerSupport">
  2. SELECT pka_serialNo from dbo.tbl_CS_serial where pkb_fk_ticketNo=<cfqueryparam value="#Form.pk_ticketID#">
  3.  </cfquery>
The #serialnum# variable has to be set to the correct serial number, so you need to get the serial number from this query. Are the serial numbers in the correct order - you may need to add an ORDER BY clause.
Nov 13 '08 #275
bonneylake
769 512MB
Not quite - you need the serials:
Expand|Select|Wrap|Line Numbers
  1. <cfquery name="countserials" datasource="CustomerSupport">
  2. SELECT pka_serialNo from dbo.tbl_CS_serial where pkb_fk_ticketNo=<cfqueryparam value="#Form.pk_ticketID#">
  3.  </cfquery>
The #serialnum# variable has to be set to the correct serial number, so you need to get the serial number from this query. Are the serial numbers in the correct order - you may need to add an ORDER BY clause.
Hey Acoder,

Only part i am confused on is how to set the #serialnum# variable to the correct serial number? i was thinking something like this but i am pretty sure i am wrong

Expand|Select|Wrap|Line Numbers
  1.  <cfquery name="countserials" datasource="CustomerSupport">
  2. SELECT pka_serialNo from dbo.tbl_CS_serial where pka_serialNo=<cfqueryparam value="#serialnum#"> and pkb_fk_ticketNo=<cfqueryparam value="#Form.pk_ticketID#">
  3.  </cfquery>
an i am not sure if the serial numbers are in the correct order. In my sql table they are just scattered everywhere so theres no real order. An afraid if i put them in order that it will mess it up because the first serial could be serial 2 and the second serial could be 1 so kinda nervous about doing that.

Thank you,
Rach
Nov 14 '08 #276
acoder
16,027 Expert Mod 8TB
The query was OK, I meant setting serialnum in the Coldfusion code in the loops.

The order wouldn't change the order in the database - it'd only change the order in which they are retrieved.

I have to say that this could get pretty complex now. Basically, you need to use these serial numbers to determine which ones need to be updated, which need to be deleted and which are new (from the new serial numbers). Now I'm not sure exactly which way would be the best or most efficient way- it depends on a number of factors. All of this could of course be avoided if you just stick with the delete all and then insert all approach, but you wanted to avoid that (we can always go back ;)).
Nov 14 '08 #277
bonneylake
769 512MB
The query was OK, I meant setting serialnum in the Coldfusion code in the loops.

The order wouldn't change the order in the database - it'd only change the order in which they are retrieved.

I have to say that this could get pretty complex now. Basically, you need to use these serial numbers to determine which ones need to be updated, which need to be deleted and which are new (from the new serial numbers). Now I'm not sure exactly which way would be the best or most efficient way- it depends on a number of factors. All of this could of course be avoided if you just stick with the delete all and then insert all approach, but you wanted to avoid that (we can always go back ;)).
Hey Acoder,

how would i set the serialnum in the coldfusion code in the loops? are you saying instead of using

Expand|Select|Wrap|Line Numbers
  1.  <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
to use

Expand|Select|Wrap|Line Numbers
  1. #Form.serialnum#

An i know it will probably get more complicated but i think its the best way. Last few days we been running into internet trouble an well i am just afraid information will be lost if go back in the other direction.

Thank you :),
Rach
Nov 14 '08 #278
acoder
16,027 Expert Mod 8TB
If that's the only reason, then that's what we have cftransaction for. It's either all or nothing. If it hasn't inserted that last record and something happens, then nothing is deleted or affected. Even with the current approach, you'd need cftransaction if you were fearing connection problems.

Anyway, for the serial number, see the cfset serialnum line. You need something similar in each loop which would set the serial number for use in the delete query. However, you can have one loop only by looping over this count query instead. The idea is that you'd first get a serial number. If it's not set from the submitted fields, then it needs to be deleted. If it's there, that means it needs to be updated. Some new ones may need to be inserted, so you'll need some way of determining what has been updated or deleted and the rest need to be inserted.
Nov 14 '08 #279
bonneylake
769 512MB
If that's the only reason, then that's what we have cftransaction for. It's either all or nothing. If it hasn't inserted that last record and something happens, then nothing is deleted or affected. Even with the current approach, you'd need cftransaction if you were fearing connection problems.

Anyway, for the serial number, see the cfset serialnum line. You need something similar in each loop which would set the serial number for use in the delete query. However, you can have one loop only by looping over this count query instead. The idea is that you'd first get a serial number. If it's not set from the submitted fields, then it needs to be deleted. If it's there, that means it needs to be updated. Some new ones may need to be inserted, so you'll need some way of determining what has been updated or deleted and the rest need to be inserted.
Hey Acoder,

Here is what i have in full.I set the serialnum but was not sure if you wanted to do the one loop only part.

Expand|Select|Wrap|Line Numbers
  1. <CFIF REQUEST_METHOD EQ "POST">
  2.  <!---<CFSET machineListLen = listLen(Form.serialcount)>--->
  3.  <cfquery name="countserials" datasource="CustomerSupport">
  4. SELECT pka_serialNo from dbo.tbl_CS_serial where pkb_fk_ticketNo=<cfqueryparam value="#Form.pk_ticketID#">
  5.  </cfquery>
  6.  <cfset currSerialNo = 1>
  7.  <CFLOOP list="#form.serialcount#" index="machineCount">
  8. <!--- <cfif machineCount eq currSerialNo>--->
  9.   <cfloop condition="currSerialNo neq machinecount">
  10.   <CFSET serialnum       = Form["serialnum_" & machineCount]>
  11.  <cfquery name="deleteserialparts" datasource="CustomerSupport">
  12.  exec usp_CS_Deleteserialparts   
  13.  <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  14.  '#Form.pk_ticketID#'
  15.  </cfquery>
  16.  <cfset currSerialNo = currSerialNo + 1>
  17.  </cfloop>
  18.  
  19.   <CFSET serialnum       = Form["serialnum_" & machineCount]>
  20.   <CFSET modelno         = Form["modelno_" & machineCount]>
  21.   <CFSET producttype     = Form["producttype_" & machineCount]>
  22.   <CFSET softhardware    = Form["softhardware_" & machineCount]>
  23.   <CFSET resolution      = Form["resolution_" & machineCount]>
  24.   <CFSET resdate         = Form["resdate_" & machineCount]>
  25.   <CFSET resvertified    = Form["resvertified_" & machineCount]>
  26.   <CFSET vertifidate     = Form["vertifidate_" & machineCount]>
  27.   <CFSET deptvendor      = Form["deptvendor_" & machinecount]>
  28.   <CFSET hardwarefailure = Form["hardwarefailure_" & machineCount]>
  29.   <CFSET thedescription  = Form["thedescription_" & machineCount]>
  30.  
  31.  
  32.  <!--- inserts information into the serial table--->
  33.  <cfquery name="serialinsertupdate" datasource="CustomerSupport">
  34.     exec usp_CS_Updateinsertserial 
  35.      <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  36.      "#Form.pk_ticketID#",
  37.      <cfqueryparam value="#modelno#" CFSQLType = "CF_SQL_VARCHAR">,
  38.       <cfqueryparam value="#producttype#" CFSQLType = "CF_SQL_VARCHAR">,
  39.      <cfqueryparam value="#softhardware#" CFSQLType = "CF_SQL_VARCHAR">,
  40.      <cfqueryparam value="#resolution#" CFSQLType = "CF_SQL_VARCHAR">,
  41.      <cfqueryparam value="#resdate#">,
  42.      <cfqueryparam value="#resvertified#" CFSQLType = "CF_SQL_VARCHAR">,
  43.      <cfqueryparam value="#vertifidate#">,
  44.     <cfqueryparam value="#deptvendor#" CFSQLType = "CF_SQL_VARCHAR">,
  45.      <cfqueryparam value="#hardwarefailure#" CFSQLType = "CF_SQL_VARCHAR">   
  46.      </cfquery>
  47.  
  48.  <!---Inserts information into notes_descr table.--->
  49.  <cfquery name="description" datasource="CustomerSupport">
  50.     exec usp_CS_Insertdescription
  51.    <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  52.    '#Form.pk_ticketID#',
  53.     <cfqueryparam value="#thedescription#" CFSQLType = "CF_SQL_VARCHAR">,
  54.    '#Form.fk_addedBy#'
  55.  </cfquery>
  56.  
  57.   <!---Inserts parts information into parts table.--->
  58.   <!---because it is a bit we don't use 'ticks' around defective for parts table--->
  59.  <cfparam name="form.partscount#machinecount#" default="">
  60.  <cfloop list="#form['partscount' & machinecount]#" index="ps">
  61. <cfparam name="Form.defective_#ps#_#machinecount#" default="0">
  62.   <cfparam name="Form.partsreturn_#ps#_#machinecount#" default="0">
  63.  <CFSET hcpn            = Form["hcpn_" & "#ps#" & "_#machinecount#"]>
  64.  <CFSET partsreturn     = Form["partsreturn_" & "#ps#" & "_#machinecount#"]>
  65.  <CFSET defective       = Form["defective_" & "#ps#" & "_#machinecount#"]>
  66.  <CFSET rma             = Form["rma_" & "#ps#" & "_#machineCount#"]>
  67. <cfquery name="parts" datasource="CustomerSupport">
  68.    exec usp_CS_Updateinsertparts 
  69.   <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  70.    '#Form.pk_ticketID#',
  71.     <cfqueryparam value="#hcpn#" CFSQLType = "CF_SQL_VARCHAR">,
  72.     <cfqueryparam value="#partsreturn#" CFSQLType = "CF_SQL_VARCHAR">,
  73.     <cfqueryparam value="#rma#" CFSQLType = "CF_SQL_VARCHAR">,
  74.    <cfqueryparam value="#defective#" CFSQLType = "CF_SQL_BIT">
  75.  </cfquery> 
  76. </cfloop>
  77.  <cfset currSerialNo = currSerialNo + 1>
  78. </CFLOOP>
  79.  <cfloop condition="currSerialNo neq countserials.pka_serialNo">
  80.  <CFSET serialnum       = Form["serialnum_" & machineCount]>
  81. <cfquery name="deleteserialparts" datasource="CustomerSupport">
  82. exec usp_CS_Deleteserialparts   
  83. <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  84. '#Form.pk_ticketID#'
  85. </cfquery>
  86.  <cfset currSerialNo = currSerialNo + 1>
  87. </cfloop>
  88.  </CFIF>
an i tried what i have above an i got an error with the last loop saying
Error resolving parameter SERIALNUM


ColdFusion was unable to determine the value of the parameter.



Thank you,
Rach
Nov 14 '08 #280
acoder
16,027 Expert Mod 8TB
Yes, it's not going to work because it doesn't exist.

If you look at the second paragraph above, that's one possible way to solve this. For any to-be-deleted serials, you no longer have the serial numbers (in the submitted fields). They're only available in the query. So you're going to have to use the query to get the serial numbers. This will require quite a few changes, the main one being that instead of looping over the list, you'll loop over the query. Then deleting becomes easy, but inserting/updating becomes a bit more difficult.
Nov 14 '08 #281
bonneylake
769 512MB
Hey Acoder,

Ok so the loop would be done like so?an then for machineCount could we do something below the cfloop query like cfset machineCount = 1?


Expand|Select|Wrap|Line Numbers
  1. <CFIF REQUEST_METHOD EQ "POST">
  2.  <!---<CFSET machineListLen = listLen(Form.serialcount)>--->
  3.  <cfquery name="countserials" datasource="CustomerSupport">
  4. SELECT pka_serialNo from dbo.tbl_CS_serial where pkb_fk_ticketNo=<cfqueryparam value="#Form.pk_ticketID#">
  5.  </cfquery>
  6.  <cfset currSerialNo = 1>
  7. <cfloop query="#countserials#">
  8. <!--- <cfif machineCount eq currSerialNo>--->
  9.   <cfloop condition="currSerialNo neq machinecount">
  10.   <CFSET serialnum       = Form["serialnum_" & machineCount]>
  11.  <cfquery name="deleteserialparts" datasource="CustomerSupport">
  12.  exec usp_CS_Deleteserialparts   
  13.  <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  14.  '#Form.pk_ticketID#'
  15.  </cfquery>
  16.  <cfset currSerialNo = currSerialNo + 1>
  17.  </cfloop>
  18.  
  19.   <CFSET serialnum       = Form["serialnum_" & machineCount]>
  20.   <CFSET modelno         = Form["modelno_" & machineCount]>
  21.   <CFSET producttype     = Form["producttype_" & machineCount]>
  22.   <CFSET softhardware    = Form["softhardware_" & machineCount]>
  23.   <CFSET resolution      = Form["resolution_" & machineCount]>
  24.   <CFSET resdate         = Form["resdate_" & machineCount]>
  25.   <CFSET resvertified    = Form["resvertified_" & machineCount]>
  26.   <CFSET vertifidate     = Form["vertifidate_" & machineCount]>
  27.   <CFSET deptvendor      = Form["deptvendor_" & machinecount]>
  28.   <CFSET hardwarefailure = Form["hardwarefailure_" & machineCount]>
  29.   <CFSET thedescription  = Form["thedescription_" & machineCount]>
  30.  
  31.  
  32.  <!--- inserts information into the serial table--->
  33.  <cfquery name="serialinsertupdate" datasource="CustomerSupport">
  34.     exec usp_CS_Updateinsertserial 
  35.      <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  36.      "#Form.pk_ticketID#",
  37.      <cfqueryparam value="#modelno#" CFSQLType = "CF_SQL_VARCHAR">,
  38.       <cfqueryparam value="#producttype#" CFSQLType = "CF_SQL_VARCHAR">,
  39.      <cfqueryparam value="#softhardware#" CFSQLType = "CF_SQL_VARCHAR">,
  40.      <cfqueryparam value="#resolution#" CFSQLType = "CF_SQL_VARCHAR">,
  41.      <cfqueryparam value="#resdate#">,
  42.      <cfqueryparam value="#resvertified#" CFSQLType = "CF_SQL_VARCHAR">,
  43.      <cfqueryparam value="#vertifidate#">,
  44.     <cfqueryparam value="#deptvendor#" CFSQLType = "CF_SQL_VARCHAR">,
  45.      <cfqueryparam value="#hardwarefailure#" CFSQLType = "CF_SQL_VARCHAR">   
  46.      </cfquery>
  47.  
  48.  <!---Inserts information into notes_descr table.--->
  49.  <cfquery name="description" datasource="CustomerSupport">
  50.     exec usp_CS_Insertdescription
  51.    <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  52.    '#Form.pk_ticketID#',
  53.     <cfqueryparam value="#thedescription#" CFSQLType = "CF_SQL_VARCHAR">,
  54.    '#Form.fk_addedBy#'
  55.  </cfquery>
  56.  
  57.   <!---Inserts parts information into parts table.--->
  58.   <!---because it is a bit we don't use 'ticks' around defective for parts table--->
  59.  <cfparam name="form.partscount#machinecount#" default="">
  60.  <cfloop list="#form['partscount' & machinecount]#" index="ps">
  61. <cfparam name="Form.defective_#ps#_#machinecount#" default="0">
  62.   <cfparam name="Form.partsreturn_#ps#_#machinecount#" default="0">
  63.  <CFSET hcpn            = Form["hcpn_" & "#ps#" & "_#machinecount#"]>
  64.  <CFSET partsreturn     = Form["partsreturn_" & "#ps#" & "_#machinecount#"]>
  65.  <CFSET defective       = Form["defective_" & "#ps#" & "_#machinecount#"]>
  66.  <CFSET rma             = Form["rma_" & "#ps#" & "_#machineCount#"]>
  67. <cfquery name="parts" datasource="CustomerSupport">
  68.    exec usp_CS_Updateinsertparts 
  69.   <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  70.    '#Form.pk_ticketID#',
  71.     <cfqueryparam value="#hcpn#" CFSQLType = "CF_SQL_VARCHAR">,
  72.     <cfqueryparam value="#partsreturn#" CFSQLType = "CF_SQL_VARCHAR">,
  73.     <cfqueryparam value="#rma#" CFSQLType = "CF_SQL_VARCHAR">,
  74.    <cfqueryparam value="#defective#" CFSQLType = "CF_SQL_BIT">
  75.  </cfquery> 
  76. </cfloop>
  77.  <cfset currSerialNo = currSerialNo + 1>
  78.  
  79.  
  80. </CFLOOP>
  81. <cfloop condition="currSerialNo neq countserials.pka_serialNo">
  82. <CFSET serialnum       = Form["serialnum_" & machineCount]>
  83. <cfquery name="deleteserialparts" datasource="CustomerSupport">
  84. exec usp_CS_Deleteserialparts   
  85. <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  86. '#Form.pk_ticketID#'
  87. </cfquery>
  88.  <cfset currSerialNo = currSerialNo + 1>
  89. </cfloop>
  90.  </CFIF>


Thank you,
Rach
Nov 14 '08 #282
acoder
16,027 Expert Mod 8TB
I'm now thinking that since you want to avoid problems if something happens in between, it would be better to insert/update, then delete.

So, keep it as it was earlier with the serial counts looping, and then add a delete loop at the bottom. All you need to do is delete all the serials except the ones that have been updated or inserted, so you can use an array to store the serial numbers to match against. In the delete loop, check that the serial number is not in this insert-update array. If it isn't, delete the serial.

An even better way (but a lot more involved from where you're at currently) is to only insert, update or delete one serial or one part at a time. It would avoid all of these problems, but it would mean a lot of to-ing and fro-ing, but you can avoid that too if you use Ajax (but that's a different ball game altogether!).
Nov 14 '08 #283
bonneylake
769 512MB
Hey Acoder,

Alright i went back to cflooping but i was wondering where would the delete loop go? would it be the one i have at the way bottom already like so?
Expand|Select|Wrap|Line Numbers
  1. </cfloop>
  2.  <cfset currSerialNo = currSerialNo + 1>
  3. </CFLOOP>
  4. <cfloop condition="currSerialNo neq countserials.pka_serialNo">
  5. <CFSET serialnum       = Form["serialnum_" & machineCount]>
  6. <cfquery name="deleteserialparts" datasource="CustomerSupport">
  7. exec usp_CS_Deleteserialparts   
  8. <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  9. '#Form.pk_ticketID#'
  10. </cfquery>
  11.  <cfset currSerialNo = currSerialNo + 1>
  12. </cfloop>
  13.  </CFIF>
an then how would i in the delete loop check that the serial number is not in the insert-update array? but i was messing around with arrays, was looking an example online an i was wondering if you were meaning to do something like the following?
Expand|Select|Wrap|Line Numbers
  1. <CFIF REQUEST_METHOD EQ "POST">
  2.  <!---<CFSET machineListLen = listLen(Form.serialcount)>--->
  3.  <cfquery name="countserials" datasource="CustomerSupport">
  4. SELECT pka_serialNo from dbo.tbl_CS_serial where pkb_fk_ticketNo=<cfqueryparam value="#Form.pk_ticketID#">
  5.  </cfquery>
  6.  <cfset currSerialNo = 1>
  7.  <CFLOOP list="#form.serialcount#" index="machineCount">
  8. <!--- <cfif machineCount eq currSerialNo>--->
  9.  
  10.   <cfloop condition="currSerialNo neq machinecount">
  11.   <CFSET serialnum       = Form["serialnum_" & machineCount]>
  12.  <cfquery name="deleteserialparts" datasource="CustomerSupport">
  13.  exec usp_CS_Deleteserialparts   
  14.  <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  15.  '#Form.pk_ticketID#'
  16.  </cfquery>
  17.  <cfset currSerialNo = currSerialNo + 1>
  18.  </cfloop>
  19. <cfset test = ArrayNew(1)>
  20.   <CFSET ArrayAppend(test, "serialnum       = Form["serialnum_" & machineCount]")>
  21.   <CFSET ArrayAppend(test,"modelno         = Form["modelno_" & machineCount]")>
  22.   <CFSET producttype     = Form["producttype_" & machineCount]>
  23.   <CFSET softhardware    = Form["softhardware_" & machineCount]>
  24.   <CFSET resolution      = Form["resolution_" & machineCount]>
Thank you,
Rach
Nov 14 '08 #284
acoder
16,027 Expert Mod 8TB
Yes, the delete would go at the bottom.

You don't need the inner loop in the first one any more (line 10-18 in 2nd code block). Neither do you need currSerialNum anywhere.

For the array, you just need the actual serial number values, not "serialnum = ...".
Nov 14 '08 #285
bonneylake
769 512MB
Yes, the delete would go at the bottom.

You don't need the inner loop in the first one any more (line 10-18 in 2nd code block). Neither do you need currSerialNum anywhere.

For the array, you just need the actual serial number values, not "serialnum = ...".
Hey Acoder,

So for the delete what would i change the condition to because i have the following right now

Expand|Select|Wrap|Line Numbers
  1. <cfloop condition="currSerialNo neq countserials.pka_serialNo">
  2. <CFSET serialnum       = Form["serialnum_" & machineCount]>
  3. <cfquery name="deleteserialparts" datasource="CustomerSupport">
  4. exec usp_CS_Deleteserialparts   
  5. <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  6. '#Form.pk_ticketID#'
  7. </cfquery>
  8. </cfloop>
an so for the array for each one this would be ok?

Expand|Select|Wrap|Line Numbers
  1.   <CFSET ArrayAppend(test, "Form["serialnum_" & machineCount]")>
Thank you,
Rach
Nov 14 '08 #286
acoder
16,027 Expert Mod 8TB
Yes, there's no need for a condition any longer because you'll be looping over the query.

In the array, you need to wrap the form value in #s.
Nov 14 '08 #287
bonneylake
769 512MB
Yes, there's no need for a condition any longer because you'll be looping over the query.

In the array, you need to wrap the form value in #s.
Hey Acoder,

so would this be correct for the delete at the bottom?

Expand|Select|Wrap|Line Numbers
  1. <cfloop query="countserials">
  2. <CFSET serialnum       = Form["serialnum_" & machineCount]>
  3. <cfquery name="deleteserialparts" datasource="CustomerSupport">
  4. exec usp_CS_Deleteserialparts   
  5. <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  6. '#Form.pk_ticketID#'
  7. </cfquery>
  8. </cfloop>
and then for the array this correct?

Expand|Select|Wrap|Line Numbers
  1.   <CFSET ArrayAppend(test, "#Form["serialnum_" & machineCount]#")>
Thank you,
Rach
Nov 14 '08 #288
acoder
16,027 Expert Mod 8TB
The array should be correct. You can cfdump to make sure.

In the delete loop, set serialnum to the field name or you can use the serialNo field name itself. Then you need to compare against the contents of the array. Unfortunately, Coldfusion doesn't have an ArrayFind, but you could use a UDF from CFLib for example, or convert the array to a list and then use ListFind().
Nov 14 '08 #289
bonneylake
769 512MB
The array should be correct. You can cfdump to make sure.

In the delete loop, set serialnum to the field name or you can use the serialNo field name itself. Then you need to compare against the contents of the array. Unfortunately, Coldfusion doesn't have an ArrayFind, but you could use a UDF from CFLib for example, or convert the array to a list and then use ListFind().
Hey Acoder,

are you saying something like this for the delete loop?

Expand|Select|Wrap|Line Numbers
  1. <cfloop query="countserials">
  2. <CFSET serialnum = #Form.serialnum#>
  3. <cfquery name="deleteserialparts" datasource="CustomerSupport">
  4. exec usp_CS_Deleteserialparts   
  5. <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  6. '#Form.pk_ticketID#'
  7. </cfquery>
  8. </cfloop>
an for the array would this convert it ?

Expand|Select|Wrap|Line Numbers
  1. <cfset test = ArrayNew(1)>
  2.   <CFSET ArrayAppend(test, "#Form["serialnum_" & machineCount]#")>
  3.   <CFSET ArrayAppend(test, "#Form["modelno_" & machineCount]#")>
  4.   <CFSET ArrayAppend(test, "#Form["producttype_" & machineCount]#")>
  5.   <CFSET ArrayAppend(test, "#Form["softhardware_" & machineCount]#")>
  6.   <CFSET ArrayAppend(test, "#Form["resolution_" & machineCount]#")>
  7.   <CFSET ArrayAppend(test, "#Form["resdate_" & machineCount]#")>
  8.   <CFSET ArrayAppend(test, "#Form["resvertified_" & machineCount]#")>
  9.   <CFSET ArrayAppend(test, "#Form["vertifidate_" & machineCount]#")>
  10.   <CFSET ArrayAppend(test, "#Form["deptvendor_" & machinecount]#")>
  11.   <CFSET ArrayAppend(test, "#Form["hardwarefailure_" & machineCount]#")>
  12.   <CFSET ArrayAppend(test, "#Form["thedescription_" & machineCount]#")>
  13.   <cfset myList = ArrayToList(test, ",")>
  14.   <cfset anothert = ListFind(myList, form.serialnum)>
Thank you,
Rach
Nov 14 '08 #290
acoder
16,027 Expert Mod 8TB
In the delete loop, the serial number should be the field from the query, not a form field.

In the array, you only need the serial numbers for comparison, not all the fields.
Nov 15 '08 #291
bonneylake
769 512MB
In the delete loop, the serial number should be the field from the query, not a form field.

In the array, you only need the serial numbers for comparison, not all the fields.
Hey Acoder,

so for the delete loop it should be like so

Expand|Select|Wrap|Line Numbers
  1. <cfloop query="countserials">
  2. <CFSET serialnum = <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">>
  3. <cfquery name="deleteserialparts" datasource="CustomerSupport">
  4. exec usp_CS_Deleteserialparts   
  5. <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  6. '#Form.pk_ticketID#'
  7. </cfquery>
an for the array i am confused are you meaning something like this

Expand|Select|Wrap|Line Numbers
  1. <cfset test = ArrayNew(1)>
  2.   <CFSET ArrayAppend(test, "#Form["serialnum_" & machineCount]#")>
  3.   <CFSET ArrayAppend(test, "#Form["modelno_" & machineCount]#")>
  4.   <CFSET ArrayAppend(test, "#Form["producttype_" & machineCount]#")>
  5.   <CFSET ArrayAppend(test, "#Form["softhardware_" & machineCount]#")>
  6.   <CFSET ArrayAppend(test, "#Form["resolution_" & machineCount]#")>
  7.   <CFSET ArrayAppend(test, "#Form["resdate_" & machineCount]#")>
  8.   <CFSET ArrayAppend(test, "#Form["resvertified_" & machineCount]#")>
  9.   <CFSET ArrayAppend(test, "#Form["vertifidate_" & machineCount]#")>
  10.   <CFSET ArrayAppend(test, "#Form["deptvendor_" & machinecount]#")>
  11.   <CFSET ArrayAppend(test, "#Form["hardwarefailure_" & machineCount]#")>
  12.   <CFSET ArrayAppend(test, "#Form["thedescription_" & machineCount]#")>
  13.   <cfset myList = ArrayToList(test, ",")>
  14.   <cfset anothert = ListFind(form.serialnum)>
Thank you,
Rach
Nov 16 '08 #292
acoder
16,027 Expert Mod 8TB
so for the delete loop it should be like so
Not quite. cfqueryparam is only required inside a cfquery tag. You only need the field name which would be pka_serialNo in this case.
an for the array i am confused are you meaning something like this
No. You only need the serial number, so only lines 1-2 and 13-14 - the rest of the lines are not needed because the array is only for comparison and the only value you're comparing against is the serial number.
Nov 17 '08 #293
bonneylake
769 512MB
Not quite. cfqueryparam is only required inside a cfquery tag. You only need the field name which would be pka_serialNo in this case.
No. You only need the serial number, so only lines 1-2 and 13-14 - the rest of the lines are not needed because the array is only for comparison and the only value you're comparing against is the serial number.
Hey Acoder,

Alrighty here is what i have for the delete

Expand|Select|Wrap|Line Numbers
  1. <cfloop query="countserials">
  2. <CFSET serialnum = #pka_serialNo#>
  3. <cfquery name="deleteserialparts" datasource="CustomerSupport">
  4. exec usp_CS_Deleteserialparts   
  5. <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  6. '#Form.pk_ticketID#'
  7. </cfquery>
  8. </cfloop>
  9.  </CFIF>
and here is what i have for the first loop

Expand|Select|Wrap|Line Numbers
  1. <cfset test = ArrayNew(1)>
  2.   <CFSET ArrayAppend(test, "#Form["serialnum_" & machineCount]#")> 
  3.   <cfset myList = ArrayToList(test, ",")>
  4.   <cfset anothert = ListFind(form.serialnum)>
but i was wondering what do i need to do with the rest of the fields, these ones because we had original had them an it was just cfset modelno = #Form["modelno_" * machineCount]#>

Expand|Select|Wrap|Line Numbers
  1.   <CFSET ArrayAppend(test, "#Form["modelno_" & machineCount]#")>
  2.   <CFSET ArrayAppend(test, "#Form["producttype_" & machineCount]#")>
  3.   <CFSET ArrayAppend(test, "#Form["softhardware_" & machineCount]#")>
  4.   <CFSET ArrayAppend(test, "#Form["resolution_" & machineCount]#")>
  5.   <CFSET ArrayAppend(test, "#Form["resdate_" & machineCount]#")>
  6.   <CFSET ArrayAppend(test, "#Form["resvertified_" & machineCount]#")>
  7.   <CFSET ArrayAppend(test, "#Form["vertifidate_" & machineCount]#")>
  8.   <CFSET ArrayAppend(test, "#Form["deptvendor_" & machinecount]#")>
  9.   <CFSET ArrayAppend(test, "#Form["hardwarefailure_" & machineCount]#")>
  10.   <CFSET ArrayAppend(test, "#Form["thedescription_" & machineCount]#")>
Thank you,
Rach
Nov 17 '08 #294
acoder
16,027 Expert Mod 8TB
The array is not meant to replace the cfset lines. Those lines have to stay and the array code is in addition to the previous code, not replace it because they are for different purposes.
Nov 17 '08 #295
bonneylake
769 512MB
The array is not meant to replace the cfset lines. Those lines have to stay and the array code is in addition to the previous code, not replace it because they are for different purposes.
Hey Acoder,

so our you saying to do it like below?

Expand|Select|Wrap|Line Numbers
  1. <cfset test = ArrayNew(1)>
  2.   <CFSET ArrayAppend(test, "#Form["serialnum_" & machineCount]#")> 
  3.   <CFSET ArrayAppend(test, "#Form["modelno_" & machineCount]#")>
  4.   <CFSET ArrayAppend(test, "#Form["producttype_" & machineCount]#")>
  5.   <CFSET ArrayAppend(test, "#Form["softhardware_" & machineCount]#")>
  6.   <CFSET ArrayAppend(test, "#Form["resolution_" & machineCount]#")>
  7.   <CFSET ArrayAppend(test, "#Form["resdate_" & machineCount]#")>
  8.   <CFSET ArrayAppend(test, "#Form["resvertified_" & machineCount]#")>
  9.   <CFSET ArrayAppend(test, "#Form["vertifidate_" & machineCount]#")>
  10.   <CFSET ArrayAppend(test, "#Form["deptvendor_" & machinecount]#")>
  11.   <CFSET ArrayAppend(test, "#Form["hardwarefailure_" & machineCount]#")>
  12.   <CFSET ArrayAppend(test, "#Form["thedescription_" & machineCount]#")>
  13.   <cfset myList = ArrayToList(test, ",")>
  14.   <cfset anothert = ListFind(form.serialnum)>
  15. <cfset test = ArrayNew(1)>
  16.   <CFSET ArrayAppend(test, "#Form["serialnum_" & machineCount]#")> 
  17. <cfset myList = ArrayToList(test, ",")>
  18.   <cfset anothert = ListFind(form.serialnum)>
  19.  
  20.  
Thank you,
Rach
Nov 17 '08 #296
acoder
16,027 Expert Mod 8TB
No, keep it as you had in, for example, post #282. These four array creation/setting lines only add the serial number. The array create line should be outside the loop. Only the array append line should be in the loop.
Nov 17 '08 #297
bonneylake
769 512MB
No, keep it as you had in, for example, post #282. These four array creation/setting lines only add the serial number. The array create line should be outside the loop. Only the array append line should be in the loop.
Hey Acoder,

Ok i am still a bit confused but are you talking about something like this?

here is what i got in full.
Expand|Select|Wrap|Line Numbers
  1. <CFIF REQUEST_METHOD EQ "POST">
  2.  
  3.  <cfquery name="countserials" datasource="CustomerSupport">
  4. SELECT pka_serialNo from dbo.tbl_CS_serial where pkb_fk_ticketNo=<cfqueryparam value="#Form.pk_ticketID#">
  5.  </cfquery>
  6.  <cfset currSerialNo = 1>
  7.  <CFLOOP list="#form.serialcount#" index="machineCount">
  8.   <CFSET serialnum       = Form["serialnum_" & machineCount]>
  9.  <cfquery name="deleteserialparts" datasource="CustomerSupport">
  10.  exec usp_CS_Deleteserialparts   
  11.  <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  12.  '#Form.pk_ticketID#'
  13.  </cfquery>
  14. <CFSET serialnum       = Form["serialnum_" & machineCount]>
  15. <CFSET modelno         = Form["modelno_" & machineCount]>
  16. <CFSET producttype     = Form["producttype_" & machineCount]>
  17. <CFSET softhardware    = Form["softhardware_" & machineCount]>
  18. <CFSET resolution      = Form["resolution_" & machineCount]>
  19. <CFSET resdate         = Form["resdate_" & machineCount]>
  20. <CFSET resvertified    = Form["resvertified_" & machineCount]>
  21. <CFSET vertifidate     = Form["vertifidate_" & machineCount]>
  22. <CFSET deptvendor      = Form["deptvendor_" & machinecount]>
  23. <CFSET hardwarefailure = Form["hardwarefailure_" & machineCount]>
  24. <CFSET thedescription  = Form["thedescription_" & machineCount]>
  25. <cfset test = ArrayNew(1)>
  26.   <CFSET ArrayAppend(test, "#Form["serialnum_" & machineCount]#")> 
  27.  
  28.  
  29.  
  30.  
  31.  <!--- inserts information into the serial table--->
  32.  <cfquery name="serialinsertupdate" datasource="CustomerSupport">
  33.     exec usp_CS_Updateinsertserial 
  34.      <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  35.      "#Form.pk_ticketID#",
  36.      <cfqueryparam value="#modelno#" CFSQLType = "CF_SQL_VARCHAR">,
  37.       <cfqueryparam value="#producttype#" CFSQLType = "CF_SQL_VARCHAR">,
  38.      <cfqueryparam value="#softhardware#" CFSQLType = "CF_SQL_VARCHAR">,
  39.      <cfqueryparam value="#resolution#" CFSQLType = "CF_SQL_VARCHAR">,
  40.      <cfqueryparam value="#resdate#">,
  41.      <cfqueryparam value="#resvertified#" CFSQLType = "CF_SQL_VARCHAR">,
  42.      <cfqueryparam value="#vertifidate#">,
  43.     <cfqueryparam value="#deptvendor#" CFSQLType = "CF_SQL_VARCHAR">,
  44.      <cfqueryparam value="#hardwarefailure#" CFSQLType = "CF_SQL_VARCHAR">   
  45.      </cfquery>
  46.  
  47.  <!---Inserts information into notes_descr table.--->
  48.  <cfquery name="description" datasource="CustomerSupport">
  49.     exec usp_CS_Insertdescription
  50.    <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  51.    '#Form.pk_ticketID#',
  52.     <cfqueryparam value="#thedescription#" CFSQLType = "CF_SQL_VARCHAR">,
  53.    '#Form.fk_addedBy#'
  54.  </cfquery>
  55.  
  56.   <!---Inserts parts information into parts table.--->
  57.   <!---because it is a bit we don't use 'ticks' around defective for parts table--->
  58.  <cfparam name="form.partscount#machinecount#" default="">
  59.  <cfloop list="#form['partscount' & machinecount]#" index="ps">
  60. <cfparam name="Form.defective_#ps#_#machinecount#" default="0">
  61.   <cfparam name="Form.partsreturn_#ps#_#machinecount#" default="0">
  62.  <CFSET hcpn            = Form["hcpn_" & "#ps#" & "_#machinecount#"]>
  63.  <CFSET partsreturn     = Form["partsreturn_" & "#ps#" & "_#machinecount#"]>
  64.  <CFSET defective       = Form["defective_" & "#ps#" & "_#machinecount#"]>
  65.  <CFSET rma             = Form["rma_" & "#ps#" & "_#machineCount#"]>
  66. <cfquery name="parts" datasource="CustomerSupport">
  67.    exec usp_CS_Updateinsertparts 
  68.   <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  69.    '#Form.pk_ticketID#',
  70.     <cfqueryparam value="#hcpn#" CFSQLType = "CF_SQL_VARCHAR">,
  71.     <cfqueryparam value="#partsreturn#" CFSQLType = "CF_SQL_VARCHAR">,
  72.     <cfqueryparam value="#rma#" CFSQLType = "CF_SQL_VARCHAR">,
  73.    <cfqueryparam value="#defective#" CFSQLType = "CF_SQL_BIT">
  74.  </cfquery> 
  75. </cfloop>
  76.  
  77. </CFLOOP>
  78. <cfset myList = ArrayToList(test, ",")>
  79. <cfset anothert = ListFind(form.serialnum)>
  80. <cfloop query="countserials">
  81.  
  82. <CFSET serialnum = #pka_serialNo#>
  83. <cfquery name="deleteserialparts" datasource="CustomerSupport">
  84. exec usp_CS_Deleteserialparts   
  85. <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  86. '#Form.pk_ticketID#'
  87. </cfquery>
  88. </cfloop>
  89.  
  90.  </CFIF>

Thank you,
Rach
Nov 17 '08 #298
acoder
16,027 Expert Mod 8TB
Remove the deleteserialparts query from the first loop. You can also remove line 8 because it's a repeat of line 14.

Line 25 should be above the loop because you don't want to create a new array for each serial.

Finally, line 79 should be inside the countserials loop for comparison though it'll need to be in a cfif tag to actually compare or you could use the variable for comparison.
Nov 17 '08 #299
bonneylake
769 512MB
Remove the deleteserialparts query from the first loop. You can also remove line 8 because it's a repeat of line 14.

Line 25 should be above the loop because you don't want to create a new array for each serial.

Finally, line 79 should be inside the countserials loop for comparison though it'll need to be in a cfif tag to actually compare or you could use the variable for comparison.
Hey Acoder,

Only part i am confused about is the cfif part, how would i make this into a cfif?

Expand|Select|Wrap|Line Numbers
  1. <cfset anothert = ListFind(form.serialnum)>
But here is what i have in full besides that

Expand|Select|Wrap|Line Numbers
  1. <CFIF REQUEST_METHOD EQ "POST">
  2.  
  3.  <cfquery name="countserials" datasource="CustomerSupport">
  4. SELECT pka_serialNo from dbo.tbl_CS_serial where pkb_fk_ticketNo=<cfqueryparam value="#Form.pk_ticketID#">
  5.  </cfquery>
  6.  <cfset currSerialNo = 1>
  7.  <cfset test = ArrayNew(1)>
  8.  <CFLOOP list="#form.serialcount#" index="machineCount">
  9. <CFSET serialnum       = Form["serialnum_" & machineCount]>
  10. <CFSET modelno         = Form["modelno_" & machineCount]>
  11. <CFSET producttype     = Form["producttype_" & machineCount]>
  12. <CFSET softhardware    = Form["softhardware_" & machineCount]>
  13. <CFSET resolution      = Form["resolution_" & machineCount]>
  14. <CFSET resdate         = Form["resdate_" & machineCount]>
  15. <CFSET resvertified    = Form["resvertified_" & machineCount]>
  16. <CFSET vertifidate     = Form["vertifidate_" & machineCount]>
  17. <CFSET deptvendor      = Form["deptvendor_" & machinecount]>
  18. <CFSET hardwarefailure = Form["hardwarefailure_" & machineCount]>
  19. <CFSET thedescription  = Form["thedescription_" & machineCount]>
  20. <CFSET ArrayAppend(test, "#Form["serialnum_" & machineCount]#")> 
  21.  
  22.  
  23.  <!--- inserts information into the serial table--->
  24.  <cfquery name="serialinsertupdate" datasource="CustomerSupport">
  25.     exec usp_CS_Updateinsertserial 
  26.      <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  27.      "#Form.pk_ticketID#",
  28.      <cfqueryparam value="#modelno#" CFSQLType = "CF_SQL_VARCHAR">,
  29.       <cfqueryparam value="#producttype#" CFSQLType = "CF_SQL_VARCHAR">,
  30.      <cfqueryparam value="#softhardware#" CFSQLType = "CF_SQL_VARCHAR">,
  31.      <cfqueryparam value="#resolution#" CFSQLType = "CF_SQL_VARCHAR">,
  32.      <cfqueryparam value="#resdate#">,
  33.      <cfqueryparam value="#resvertified#" CFSQLType = "CF_SQL_VARCHAR">,
  34.      <cfqueryparam value="#vertifidate#">,
  35.     <cfqueryparam value="#deptvendor#" CFSQLType = "CF_SQL_VARCHAR">,
  36.      <cfqueryparam value="#hardwarefailure#" CFSQLType = "CF_SQL_VARCHAR">   
  37.      </cfquery>
  38.  
  39.  <!---Inserts information into notes_descr table.--->
  40.  <cfquery name="description" datasource="CustomerSupport">
  41.     exec usp_CS_Insertdescription
  42.    <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  43.    '#Form.pk_ticketID#',
  44.     <cfqueryparam value="#thedescription#" CFSQLType = "CF_SQL_VARCHAR">,
  45.    '#Form.fk_addedBy#'
  46.  </cfquery>
  47.  
  48.   <!---Inserts parts information into parts table.--->
  49.   <!---because it is a bit we don't use 'ticks' around defective for parts table--->
  50.  <cfparam name="form.partscount#machinecount#" default="">
  51.  <cfloop list="#form['partscount' & machinecount]#" index="ps">
  52. <cfparam name="Form.defective_#ps#_#machinecount#" default="0">
  53.   <cfparam name="Form.partsreturn_#ps#_#machinecount#" default="0">
  54.  <CFSET hcpn            = Form["hcpn_" & "#ps#" & "_#machinecount#"]>
  55.  <CFSET partsreturn     = Form["partsreturn_" & "#ps#" & "_#machinecount#"]>
  56.  <CFSET defective       = Form["defective_" & "#ps#" & "_#machinecount#"]>
  57.  <CFSET rma             = Form["rma_" & "#ps#" & "_#machineCount#"]>
  58. <cfquery name="parts" datasource="CustomerSupport">
  59.    exec usp_CS_Updateinsertparts 
  60.   <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  61.    '#Form.pk_ticketID#',
  62.     <cfqueryparam value="#hcpn#" CFSQLType = "CF_SQL_VARCHAR">,
  63.     <cfqueryparam value="#partsreturn#" CFSQLType = "CF_SQL_VARCHAR">,
  64.     <cfqueryparam value="#rma#" CFSQLType = "CF_SQL_VARCHAR">,
  65.    <cfqueryparam value="#defective#" CFSQLType = "CF_SQL_BIT">
  66.  </cfquery> 
  67. </cfloop>
  68. <cfset anothert = ListFind(form.serialnum)>
  69. </CFLOOP>
  70.  
  71.  
  72. <cfset myList = ArrayToList(test, ",")>
  73. <cfloop query="countserials">
  74. <CFSET serialnum = #pka_serialNo#>
  75. <cfquery name="deleteserialparts" datasource="CustomerSupport">
  76. exec usp_CS_Deleteserialparts   
  77. <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  78. '#Form.pk_ticketID#'
  79. </cfquery>
  80. </cfloop>
  81.  
  82.  </CFIF>
Thank you,
Rach
Nov 17 '08 #300

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by Vigneshwar Pilli via DotNetMonster.com | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.