469,300 Members | 2,278 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
That line (68) needs to be in the second loop and you need to find the serialnum value defined on line 74, so it needs to appear after that line.

The syntax is incorrect - it needs two parameters (see docs).

You can use ListFind in a cfif quite easily. If it finds it, it will return true and false otherwise. You only want to delete if it doesn't find the serial number.

What these changes do are the following:
1. Insert/update serials only in the first loop.
2. In the loop, get the serials that have been inserted and updated in an array to avoid deleting them.
3. When inserting/updating is complete, we now want to delete.
4. All serials that were not inserted or updated are deleted.

You'll eventually have to do something similar for parts, but get this working first.
Nov 17 '08 #301
bonneylake
769 512MB
Hey Acoder,

What line is the synax incorrect on? an then would this work for the ListFind?

Expand|Select|Wrap|Line Numbers
  1.  <cfif ListFind(form.serialnum)>
  2.  <cfelse>
  3. </cfloop>
  4. </CFLOOP>
  5.  
  6. <cfset myList = ArrayToList(test, ",")>
  7. <!---when inserting/updating is done delete ones not used--->
  8. <cfloop query="countserials">
  9. <CFSET serialnum = #pka_serialNo#>
  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. </cfloop>
  16. </cfif>
here is what i have 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. <!--- the array gets serials that have been inserted and updated to avoid deleteing the ones that need to be inserted or updated--->
  8.  <cfset test = ArrayNew(1)>
  9. <!--- first loop inserts/update serials--->
  10.  <CFLOOP list="#form.serialcount#" index="machineCount">
  11. <CFSET serialnum       = Form["serialnum_" & machineCount]>
  12. <CFSET modelno         = Form["modelno_" & machineCount]>
  13. <CFSET producttype     = Form["producttype_" & machineCount]>
  14. <CFSET softhardware    = Form["softhardware_" & machineCount]>
  15. <CFSET resolution      = Form["resolution_" & machineCount]>
  16. <CFSET resdate         = Form["resdate_" & machineCount]>
  17. <CFSET resvertified    = Form["resvertified_" & machineCount]>
  18. <CFSET vertifidate     = Form["vertifidate_" & machineCount]>
  19. <CFSET deptvendor      = Form["deptvendor_" & machinecount]>
  20. <CFSET hardwarefailure = Form["hardwarefailure_" & machineCount]>
  21. <CFSET thedescription  = Form["thedescription_" & machineCount]>
  22. <CFSET ArrayAppend(test, "#Form["serialnum_" & machineCount]#")> 
  23.  
  24.  
  25.  <!--- inserts information into the serial table--->
  26.  <cfquery name="serialinsertupdate" datasource="CustomerSupport">
  27.     exec usp_CS_Updateinsertserial 
  28.      <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  29.      "#Form.pk_ticketID#",
  30.      <cfqueryparam value="#modelno#" CFSQLType = "CF_SQL_VARCHAR">,
  31.       <cfqueryparam value="#producttype#" CFSQLType = "CF_SQL_VARCHAR">,
  32.      <cfqueryparam value="#softhardware#" CFSQLType = "CF_SQL_VARCHAR">,
  33.      <cfqueryparam value="#resolution#" CFSQLType = "CF_SQL_VARCHAR">,
  34.      <cfqueryparam value="#resdate#">,
  35.      <cfqueryparam value="#resvertified#" CFSQLType = "CF_SQL_VARCHAR">,
  36.      <cfqueryparam value="#vertifidate#">,
  37.     <cfqueryparam value="#deptvendor#" CFSQLType = "CF_SQL_VARCHAR">,
  38.      <cfqueryparam value="#hardwarefailure#" CFSQLType = "CF_SQL_VARCHAR">   
  39.      </cfquery>
  40.  
  41.  <!---Inserts information into notes_descr table.--->
  42.  <cfquery name="description" datasource="CustomerSupport">
  43.     exec usp_CS_Insertdescription
  44.    <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  45.    '#Form.pk_ticketID#',
  46.     <cfqueryparam value="#thedescription#" CFSQLType = "CF_SQL_VARCHAR">,
  47.    '#Form.fk_addedBy#'
  48.  </cfquery>
  49.  
  50.   <!---Inserts parts information into parts table.--->
  51.   <!---because it is a bit we don't use 'ticks' around defective for parts table--->
  52.  <cfparam name="form.partscount#machinecount#" default="">
  53.  <cfloop list="#form['partscount' & machinecount]#" index="ps">
  54. <cfparam name="Form.defective_#ps#_#machinecount#" default="0">
  55.   <cfparam name="Form.partsreturn_#ps#_#machinecount#" default="0">
  56.  <CFSET hcpn            = Form["hcpn_" & "#ps#" & "_#machinecount#"]>
  57.  <CFSET partsreturn     = Form["partsreturn_" & "#ps#" & "_#machinecount#"]>
  58.  <CFSET defective       = Form["defective_" & "#ps#" & "_#machinecount#"]>
  59.  <CFSET rma             = Form["rma_" & "#ps#" & "_#machineCount#"]>
  60. <cfquery name="parts" datasource="CustomerSupport">
  61.    exec usp_CS_Updateinsertparts 
  62.   <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  63.    '#Form.pk_ticketID#',
  64.     <cfqueryparam value="#hcpn#" CFSQLType = "CF_SQL_VARCHAR">,
  65.     <cfqueryparam value="#partsreturn#" CFSQLType = "CF_SQL_VARCHAR">,
  66.     <cfqueryparam value="#rma#" CFSQLType = "CF_SQL_VARCHAR">,
  67.    <cfqueryparam value="#defective#" CFSQLType = "CF_SQL_BIT">
  68.  </cfquery> 
  69.  
  70.  <cfif ListFind(form.serialnum)>
  71.  <cfelse>
  72. </cfloop>
  73. </CFLOOP>
  74.  
  75.  
  76. <cfset myList = ArrayToList(test, ",")>
  77. <!---when inserting/updating is done delete ones not used--->
  78. <cfloop query="countserials">
  79. <CFSET serialnum = #pka_serialNo#>
  80. <cfquery name="deleteserialparts" datasource="CustomerSupport">
  81. exec usp_CS_Deleteserialparts   
  82. <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  83. '#Form.pk_ticketID#'
  84. </cfquery>
  85. </cfloop>
  86. </cfif>
  87.  </CFIF>
an thank you for explaining what each part does. that helps a lot because been feeling a bit lost

Thank you :),
Rach
Nov 17 '08 #302
acoder
16,027 Expert Mod 8TB
The listfind syntax is incorrect. It requires two parameters. Also, as I mentioned earlier, it needs to go in the last (delete) loop.
Nov 17 '08 #303
bonneylake
769 512MB
The listfind syntax is incorrect. It requires two parameters. Also, as I mentioned earlier, it needs to go in the last (delete) loop.
Hey Acoder,

Would this be correct?

Expand|Select|Wrap|Line Numbers
  1. </cfloop>
  2. </CFLOOP>
  3.  
  4.  
  5. <cfset myList = ArrayToList(test, ",")>
  6. <!---when inserting/updating is done delete ones not used--->
  7. <cfloop query="countserials">
  8. <cfif ListFind(test,form.serialnum)>
  9.  <cfelse>
  10. <CFSET serialnum = #pka_serialNo#>
  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. </cfloop>
  17. </cfif>
  18.  </CFIF>
Thank you,
Rach
Nov 17 '08 #304
acoder
16,027 Expert Mod 8TB
Not quite.

1. The list should be a valid list. test is an array, so you want myList. BTW, you may want to change some of these names to make them more meaningful to avoid possible problems later.

2. You need to compare against serialnum, so the cfif line needs to appear after it's set, not before.

3. You can either use cfelse for the delete query, or <cfif not listfind(...)>

4. Don't forget the closing </cfif> tag.
Nov 17 '08 #305
bonneylake
769 512MB
Hey Acoder,

So heres the whole thing is this correct?

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. <!--- the array gets serials that have been inserted and updated to avoid deleteing the ones that need to be inserted or updated--->
  8.  <cfset serialcheck = ArrayNew(1)>
  9. <!--- first loop inserts/update serials--->
  10.  <CFLOOP list="#form.serialcount#" index="machineCount">
  11. <CFSET serialnum       = Form["serialnum_" & machineCount]>
  12. <CFSET modelno         = Form["modelno_" & machineCount]>
  13. <CFSET producttype     = Form["producttype_" & machineCount]>
  14. <CFSET softhardware    = Form["softhardware_" & machineCount]>
  15. <CFSET resolution      = Form["resolution_" & machineCount]>
  16. <CFSET resdate         = Form["resdate_" & machineCount]>
  17. <CFSET resvertified    = Form["resvertified_" & machineCount]>
  18. <CFSET vertifidate     = Form["vertifidate_" & machineCount]>
  19. <CFSET deptvendor      = Form["deptvendor_" & machinecount]>
  20. <CFSET hardwarefailure = Form["hardwarefailure_" & machineCount]>
  21. <CFSET thedescription  = Form["thedescription_" & machineCount]>
  22. <CFSET ArrayAppend(serialcheck, "#Form["serialnum_" & machineCount]#")> 
  23.  
  24.  
  25.  <!--- inserts information into the serial table--->
  26.  <cfquery name="serialinsertupdate" datasource="CustomerSupport">
  27.     exec usp_CS_Updateinsertserial 
  28.      <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  29.      "#Form.pk_ticketID#",
  30.      <cfqueryparam value="#modelno#" CFSQLType = "CF_SQL_VARCHAR">,
  31.       <cfqueryparam value="#producttype#" CFSQLType = "CF_SQL_VARCHAR">,
  32.      <cfqueryparam value="#softhardware#" CFSQLType = "CF_SQL_VARCHAR">,
  33.      <cfqueryparam value="#resolution#" CFSQLType = "CF_SQL_VARCHAR">,
  34.      <cfqueryparam value="#resdate#">,
  35.      <cfqueryparam value="#resvertified#" CFSQLType = "CF_SQL_VARCHAR">,
  36.      <cfqueryparam value="#vertifidate#">,
  37.     <cfqueryparam value="#deptvendor#" CFSQLType = "CF_SQL_VARCHAR">,
  38.      <cfqueryparam value="#hardwarefailure#" CFSQLType = "CF_SQL_VARCHAR">   
  39.      </cfquery>
  40.  
  41.  <!---Inserts information into notes_descr table.--->
  42.  <cfquery name="description" datasource="CustomerSupport">
  43.     exec usp_CS_Insertdescription
  44.    <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  45.    '#Form.pk_ticketID#',
  46.     <cfqueryparam value="#thedescription#" CFSQLType = "CF_SQL_VARCHAR">,
  47.    '#Form.fk_addedBy#'
  48.  </cfquery>
  49.  
  50.   <!---Inserts parts information into parts table.--->
  51.   <!---because it is a bit we don't use 'ticks' around defective for parts table--->
  52.  <cfparam name="form.partscount#machinecount#" default="">
  53.  <cfloop list="#form['partscount' & machinecount]#" index="ps">
  54. <cfparam name="Form.defective_#ps#_#machinecount#" default="0">
  55.   <cfparam name="Form.partsreturn_#ps#_#machinecount#" default="0">
  56.  <CFSET hcpn            = Form["hcpn_" & "#ps#" & "_#machinecount#"]>
  57.  <CFSET partsreturn     = Form["partsreturn_" & "#ps#" & "_#machinecount#"]>
  58.  <CFSET defective       = Form["defective_" & "#ps#" & "_#machinecount#"]>
  59.  <CFSET rma             = Form["rma_" & "#ps#" & "_#machineCount#"]>
  60. <cfquery name="parts" datasource="CustomerSupport">
  61.    exec usp_CS_Updateinsertparts 
  62.   <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  63.    '#Form.pk_ticketID#',
  64.     <cfqueryparam value="#hcpn#" CFSQLType = "CF_SQL_VARCHAR">,
  65.     <cfqueryparam value="#partsreturn#" CFSQLType = "CF_SQL_VARCHAR">,
  66.     <cfqueryparam value="#rma#" CFSQLType = "CF_SQL_VARCHAR">,
  67.    <cfqueryparam value="#defective#" CFSQLType = "CF_SQL_BIT">
  68.  </cfquery> 
  69.  
  70.  
  71. </cfloop>
  72. </CFLOOP>
  73.  
  74.  
  75. <cfset serialList = ArrayToList(serialcheck, ",")>
  76. <!---when inserting/updating is done delete ones not used--->
  77. <cfloop query="countserials">
  78. <CFSET serialnum = #pka_serialNo#>
  79. <cfif ListFind(serialList,form.serialnum)>
  80. <cfif not listFind(serialList,form.serialnum)>
  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. </cfif>
  87. </cfloop>
  88.  </CFIF>
  89.  
Thank you :),
Rach
Nov 17 '08 #306
acoder
16,027 Expert Mod 8TB
Just two things.

1. Line 79 is not needed.
2. Line 80 should be using serialnum set on line 78, not form.serialnum.

Much better names, I see :)
Nov 17 '08 #307
bonneylake
769 512MB
Hey Acoder,

Yep i took your advice on the names, makes more sense to me since i defined it better for myself :). But that worked beautifully!!! i tested it a few times an different ways an i ran into no trouble. It deleted from both serials and parts table. Works beautiful. THANK YOU THANK YOU :). i almost want to cry its so awesome!!! But the only thing i need help with is the parts besides for what we just did and applying it to parts. I also need help with just updating parts. Right now if i try to update parts. Let say i had a part assigned to serial 1 but decided to delete it. It basically gives me an error an wont let me update any serials without a part attached to it. If i have a part no problem, no part it screams. But here is what i have in full right now

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. <!--- the array gets serials that have been inserted and updated to avoid deleteing the ones that need to be inserted or updated--->
  8.  <cfset serialcheck = ArrayNew(1)>
  9. <!--- first loop inserts/update serials--->
  10.  <CFLOOP list="#form.serialcount#" index="machineCount">
  11. <CFSET serialnum       = Form["serialnum_" & machineCount]>
  12. <CFSET modelno         = Form["modelno_" & machineCount]>
  13. <CFSET producttype     = Form["producttype_" & machineCount]>
  14. <CFSET softhardware    = Form["softhardware_" & machineCount]>
  15. <CFSET resolution      = Form["resolution_" & machineCount]>
  16. <CFSET resdate         = Form["resdate_" & machineCount]>
  17. <CFSET resvertified    = Form["resvertified_" & machineCount]>
  18. <CFSET vertifidate     = Form["vertifidate_" & machineCount]>
  19. <CFSET deptvendor      = Form["deptvendor_" & machinecount]>
  20. <CFSET hardwarefailure = Form["hardwarefailure_" & machineCount]>
  21. <CFSET thedescription  = Form["thedescription_" & machineCount]>
  22. <CFSET ArrayAppend(serialcheck, "#Form["serialnum_" & machineCount]#")> 
  23.  
  24.  
  25.  <!--- inserts information into the serial table--->
  26.  <cfquery name="serialinsertupdate" datasource="CustomerSupport">
  27.     exec usp_CS_Updateinsertserial 
  28.      <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  29.      "#Form.pk_ticketID#",
  30.      <cfqueryparam value="#modelno#" CFSQLType = "CF_SQL_VARCHAR">,
  31.       <cfqueryparam value="#producttype#" CFSQLType = "CF_SQL_VARCHAR">,
  32.      <cfqueryparam value="#softhardware#" CFSQLType = "CF_SQL_VARCHAR">,
  33.      <cfqueryparam value="#resolution#" CFSQLType = "CF_SQL_VARCHAR">,
  34.      <cfqueryparam value="#resdate#">,
  35.      <cfqueryparam value="#resvertified#" CFSQLType = "CF_SQL_VARCHAR">,
  36.      <cfqueryparam value="#vertifidate#">,
  37.     <cfqueryparam value="#deptvendor#" CFSQLType = "CF_SQL_VARCHAR">,
  38.      <cfqueryparam value="#hardwarefailure#" CFSQLType = "CF_SQL_VARCHAR">   
  39.      </cfquery>
  40.  
  41.  <!---Inserts information into notes_descr table.--->
  42.  <cfquery name="description" datasource="CustomerSupport">
  43.     exec usp_CS_Insertdescription
  44.    <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  45.    '#Form.pk_ticketID#',
  46.     <cfqueryparam value="#thedescription#" CFSQLType = "CF_SQL_VARCHAR">,
  47.    '#Form.fk_addedBy#'
  48.  </cfquery>
  49.  
  50.   <!---Inserts parts information into parts table.--->
  51.   <!---because it is a bit we don't use 'ticks' around defective for parts table--->
  52.  <cfparam name="form.partscount#machinecount#" default="">
  53.  <cfloop list="#form['partscount' & machinecount]#" index="ps">
  54. <cfparam name="Form.defective_#ps#_#machinecount#" default="0">
  55.   <cfparam name="Form.partsreturn_#ps#_#machinecount#" default="0">
  56.  <CFSET hcpn            = Form["hcpn_" & "#ps#" & "_#machinecount#"]>
  57.  <CFSET partsreturn     = Form["partsreturn_" & "#ps#" & "_#machinecount#"]>
  58.  <CFSET defective       = Form["defective_" & "#ps#" & "_#machinecount#"]>
  59.  <CFSET rma             = Form["rma_" & "#ps#" & "_#machineCount#"]>
  60. <cfquery name="parts" datasource="CustomerSupport">
  61.    exec usp_CS_Updateinsertparts 
  62.   <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  63.    '#Form.pk_ticketID#',
  64.     <cfqueryparam value="#hcpn#" CFSQLType = "CF_SQL_VARCHAR">,
  65.     <cfqueryparam value="#partsreturn#" CFSQLType = "CF_SQL_VARCHAR">,
  66.     <cfqueryparam value="#rma#" CFSQLType = "CF_SQL_VARCHAR">,
  67.    <cfqueryparam value="#defective#" CFSQLType = "CF_SQL_BIT">
  68.  </cfquery> 
  69.  
  70.  
  71. </cfloop>
  72. </CFLOOP>
  73.  
  74.  
  75. <cfset serialList = ArrayToList(serialcheck, ",")>
  76. <!---when inserting/updating is done delete ones not used--->
  77. <cfloop query="countserials">
  78. <CFSET serialnum = #pka_serialNo#>
  79. <!---<cfif ListFind(serialList,form.serialnum)>--->
  80. <cfif not listFind(serialList,serialnum)>
  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. </cfif>
  87. </cfloop>
  88.  </CFIF>
Thank you :),
Rach
Nov 17 '08 #308
acoder
16,027 Expert Mod 8TB
Good to see that that part's working. Approaching the end of the journey, but not quite there yet!

Re. parts. The delete parts hasn't been added yet. Did you mean the delete isn't working or the update?
Nov 17 '08 #309
bonneylake
769 512MB
Good to see that that part's working. Approaching the end of the journey, but not quite there yet!

Re. parts. The delete parts hasn't been added yet. Did you mean the delete isn't working or the update?
Hey Acoder,

I am meaning if i go to insert a serial, if i do not add a part to the serial i get an error. This error occurs when i try to insert just serials (with no parts) an also occurs when i try to just update serials (with no parts).

Thank you :),
Rach
Nov 17 '08 #310
acoder
16,027 Expert Mod 8TB
What's the exact error message and on what line?
Nov 17 '08 #311
bonneylake
769 512MB
What's the exact error message and on what line?
Hey Acoder,

i get the following error

An error occurred while evaluating the expression:


hcpn = Form["hcpn_" & "#ps#" & "_#machinecount#"]


Error near line 106, column 8.
--------------------------------------------------------------------------------

The member "HCPN_1_1" in dimension 1 of object "Form" cannot be found. Please, modify the member name.

on my submit page here is what i have for the parts section

Expand|Select|Wrap|Line Numbers
  1.  <cfloop list="#form['partscount' & machinecount]#" index="ps">
  2. <cfparam name="Form.defective_#ps#_#machinecount#" default="0">
  3.   <cfparam name="Form.partsreturn_#ps#_#machinecount#" default="0">
  4.  <CFSET hcpn            = Form["hcpn_" & "#ps#" & "_#machinecount#"]>
  5.  <CFSET partsreturn     = Form["partsreturn_" & "#ps#" & "_#machinecount#"]>
  6.  <CFSET defective       = Form["defective_" & "#ps#" & "_#machinecount#"]>
  7.  <CFSET rma             = Form["rma_" & "#ps#" & "_#machineCount#"]>
  8. <cfquery name="parts" datasource="CustomerSupport">
  9.    exec usp_CS_Updateinsertparts 
  10.   <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  11.    '#Form.pk_ticketID#',
  12.     <cfqueryparam value="#hcpn#" CFSQLType = "CF_SQL_VARCHAR">,
  13.     <cfqueryparam value="#partsreturn#" CFSQLType = "CF_SQL_VARCHAR">,
  14.     <cfqueryparam value="#rma#" CFSQLType = "CF_SQL_VARCHAR">,
  15.    <cfqueryparam value="#defective#" CFSQLType = "CF_SQL_BIT">
  16.  </cfquery> 
  17. </cfloop>
Thank you,
Rach
Nov 17 '08 #312
acoder
16,027 Expert Mod 8TB
Add cfparams for all the fields, not just defective and partsreturn.
Nov 17 '08 #313
bonneylake
769 512MB
Add cfparams for all the fields, not just defective and partsreturn.
Hey Acoder,

I had tried that before. But the thing is when i insert it. It adds a blank record. So when i go back to view it you will see a blank parts section. An i can imagine a lot of blank records going in there if i do it this way. Any other way to go about it? here is how i had added it
Expand|Select|Wrap|Line Numbers
  1. <cfloop list="#form['partscount' & machinecount]#" index="ps">
  2. <cfparam name="Form.hcpn_#ps#_#machinecount#" default="">
  3. <cfparam name="Form.defective_#ps#_#machinecount#" default="0">
  4. <cfparam name="Form.partsreturn_#ps#_#machinecount#" default="0">
  5. <cfparam name="Form.rma_#ps#_#machinecount#" default="">
  6. <CFSET hcpn            = Form["hcpn_" & "#ps#" & "_#machinecount#"]>
  7. <CFSET partsreturn     = Form["partsreturn_" & "#ps#" & "_#machinecount#"]>
  8. <CFSET defective       = Form["defective_" & "#ps#" & "_#machinecount#"]>
  9. <CFSET rma             = Form["rma_" & "#ps#" & "_#machineCount#"]>
  10. <cfquery name="parts" datasource="CustomerSupport">
  11.   exec usp_CS_Insertparts 
  12.  <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  13.     '#Form.ID#',
  14.      <cfqueryparam value="#hcpn#" CFSQLType = "CF_SQL_VARCHAR">,
  15.    <cfqueryparam value="#partsreturn#" CFSQLType = "CF_SQL_VARCHAR">,
  16.    <cfqueryparam value="#defective#" CFSQLType = "CF_SQL_BIT">,
  17.     <cfqueryparam value="#rma#" CFSQLType = "CF_SQL_VARCHAR">
  18. </cfquery>
  19.  </cfloop>
Thank you,
Rach
Nov 17 '08 #314
acoder
16,027 Expert Mod 8TB
Now I see what's happening. It's obviously being caused by the blank form for parts. What you can do is check that one of the required fields is filled in, otherwise just ignore. So, for example, if defective is 0 or partsreturn is empty, then the parts is not filled in and shouldn't be inserted.
Nov 18 '08 #315
bonneylake
769 512MB
Now I see what's happening. It's obviously being caused by the blank form for parts. What you can do is check that one of the required fields is filled in, otherwise just ignore. So, for example, if defective is 0 or partsreturn is empty, then the parts is not filled in and shouldn't be inserted.
Hey Acoder,

So how would i check to see if a field is filled in? i know that defective and partreturn is not important enough because there both checkbox's. The only field that would be important is the hcpn. Could i do something like

Expand|Select|Wrap|Line Numbers
  1. <cfif hcpn is true>
  2.  <cfparam name="form.partscount#machinecount#" default="">
  3.  <cfloop list="#form['partscount' & machinecount]#" index="ps">
  4. <cfparam name="Form.defective_#ps#_#machinecount#" default="0">
  5.   <cfparam name="Form.partsreturn_#ps#_#machinecount#" default="0">
  6.  <CFSET hcpn            = Form["hcpn_" & "#ps#" & "_#machinecount#"]>
  7.  <CFSET partsreturn     = Form["partsreturn_" & "#ps#" & "_#machinecount#"]>
  8.  <CFSET defective       = Form["defective_" & "#ps#" & "_#machinecount#"]>
  9.  <CFSET rma             = Form["rma_" & "#ps#" & "_#machineCount#"]>
  10. <cfquery name="parts" datasource="CustomerSupport">
  11.    exec usp_CS_Updateinsertparts 
  12.   <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  13.    '#Form.pk_ticketID#',
  14.     <cfqueryparam value="#hcpn#" CFSQLType = "CF_SQL_VARCHAR">,
  15.     <cfqueryparam value="#partsreturn#" CFSQLType = "CF_SQL_VARCHAR">,
  16.     <cfqueryparam value="#rma#" CFSQLType = "CF_SQL_VARCHAR">,
  17.    <cfqueryparam value="#defective#" CFSQLType = "CF_SQL_BIT">
  18.  </cfquery> 
  19. </cfloop>
  20. <cfelse>
  21. </cfif>
Thank you,
Rach
Nov 18 '08 #316
acoder
16,027 Expert Mod 8TB
Use cfparam for hcpn and default to "". If it's not empty, run the query on line 10, so the cfif would be after the cfsets.
Nov 18 '08 #317
bonneylake
769 512MB
Use cfparam for hcpn and default to "". If it's not empty, run the query on line 10, so the cfif would be after the cfsets.
Hey Acoder,

It worked an then it didn't work. When i tried just not adding a part to any serial worked great. But then when i did the following

serial 1
serial 2
part (for serial 2)
serial 3

it didn't show the part an when i looked it didn't even insert the part at all. Here is what i have
Expand|Select|Wrap|Line Numbers
  1. <cfparam name="form.partscount#machinecount#" default="">
  2.  <cfloop list="#form['partscount' & machinecount]#" index="ps">
  3.  <cfparam name="Form.hcpn_#ps#_#machinecount#" default="">
  4.  <cfparam name="Form.defective_#ps#_#machinecount#" default="0">
  5.  <cfparam name="Form.partsreturn_#ps#_#machinecount#" default="0">
  6.  <cfparam name="Form.rma_#ps#_#machinecount#" default="">
  7.  <CFSET hcpn            = Form["hcpn_" & "#ps#" & "_#machinecount#"]>
  8.  <CFSET partsreturn     = Form["partsreturn_" & "#ps#" & "_#machinecount#"]>
  9.  <CFSET defective       = Form["defective_" & "#ps#" & "_#machinecount#"]>
  10.  <CFSET rma             = Form["rma_" & "#ps#" & "_#machineCount#"]>
  11.  <cfif hcpn is true>
  12. <cfquery name="parts" datasource="CustomerSupport">
  13.    exec usp_CS_Updateinsertparts 
  14.   <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  15.    '#Form.pk_ticketID#',
  16.     <cfqueryparam value="#hcpn#" CFSQLType = "CF_SQL_VARCHAR">,
  17.     <cfqueryparam value="#partsreturn#" CFSQLType = "CF_SQL_VARCHAR">,
  18.     <cfqueryparam value="#rma#" CFSQLType = "CF_SQL_VARCHAR">,
  19.    <cfqueryparam value="#defective#" CFSQLType = "CF_SQL_BIT">
  20.  </cfquery> 
  21.  <cfelse>
  22.  </cfif>
  23. </cfloop>
Thank you,
Rach
Nov 18 '08 #318
acoder
16,027 Expert Mod 8TB
Rather than
Expand|Select|Wrap|Line Numbers
  1. <cfif hcpn is true>
try
Expand|Select|Wrap|Line Numbers
  1. <cfif hcpn neq "">
Nov 18 '08 #319
bonneylake
769 512MB
Hey Acoder,

That worked perfectly :), what needs to happen next? here is what i have 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. <!--- the array gets serials that have been inserted and updated to avoid deleteing the ones that need to be inserted or updated--->
  8.  <cfset serialcheck = ArrayNew(1)>
  9. <!--- first loop inserts/update serials--->
  10.  <CFLOOP list="#form.serialcount#" index="machineCount">
  11. <CFSET serialnum       = Form["serialnum_" & machineCount]>
  12. <CFSET modelno         = Form["modelno_" & machineCount]>
  13. <CFSET producttype     = Form["producttype_" & machineCount]>
  14. <CFSET softhardware    = Form["softhardware_" & machineCount]>
  15. <CFSET resolution      = Form["resolution_" & machineCount]>
  16. <CFSET resdate         = Form["resdate_" & machineCount]>
  17. <CFSET resvertified    = Form["resvertified_" & machineCount]>
  18. <CFSET vertifidate     = Form["vertifidate_" & machineCount]>
  19. <CFSET deptvendor      = Form["deptvendor_" & machinecount]>
  20. <CFSET hardwarefailure = Form["hardwarefailure_" & machineCount]>
  21. <CFSET thedescription  = Form["thedescription_" & machineCount]>
  22. <!---the array checks to see what serials are there--->
  23. <CFSET ArrayAppend(serialcheck, "#Form["serialnum_" & 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.partscount#machinecount#" default="">
  54.  <cfloop list="#form['partscount' & machinecount]#" index="ps">
  55.  <cfparam name="Form.hcpn_#ps#_#machinecount#" default="">
  56.  <cfparam name="Form.defective_#ps#_#machinecount#" default="0">
  57.  <cfparam name="Form.partsreturn_#ps#_#machinecount#" default="0">
  58.  <cfparam name="Form.rma_#ps#_#machinecount#" default="">
  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. <cfif hcpn neq "">
  64. <cfquery name="parts" datasource="CustomerSupport">
  65.    exec usp_CS_Updateinsertparts 
  66.   <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  67.    '#Form.pk_ticketID#',
  68.     <cfqueryparam value="#hcpn#" CFSQLType = "CF_SQL_VARCHAR">,
  69.     <cfqueryparam value="#partsreturn#" CFSQLType = "CF_SQL_VARCHAR">,
  70.     <cfqueryparam value="#rma#" CFSQLType = "CF_SQL_VARCHAR">,
  71.    <cfqueryparam value="#defective#" CFSQLType = "CF_SQL_BIT">
  72.  </cfquery> 
  73.  <cfelse>
  74.  </cfif>
  75. </cfloop>
  76. </CFLOOP>
  77.  
  78.  
  79. <cfset serialList = ArrayToList(serialcheck, ",")>
  80. <!---when inserting/updating for serial's table is done delete serials not being updated/inserted or was deleted on purpose--->
  81. <cfloop query="countserials">
  82. <CFSET serialnum = #pka_serialNo#>
  83. <!---<cfif ListFind(serialList,form.serialnum)>--->
  84. <!---for the serials numbers not found in the serialList it will delete those serials --->
  85. <cfif not listFind(serialList,serialnum)>
  86. <cfquery name="deleteserialparts" datasource="CustomerSupport">
  87. exec usp_CS_Deleteserialparts   
  88. <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  89. '#Form.pk_ticketID#'
  90. </cfquery>
  91. </cfif>
  92. </cfloop>
  93.  </CFIF>
Thank you,
Rach
Nov 18 '08 #320
acoder
16,027 Expert Mod 8TB
What you have left is the delete for parts.

The best way would probably be to use a multi-dimensional array, i.e. make serialcheck into one. See Basic array techniques. Then you can use it to check part numbers for each serial and delete appropriately.
Nov 18 '08 #321
bonneylake
769 512MB
What you have left is the delete for parts.

The best way would probably be to use a multi-dimensional array, i.e. make serialcheck into one. See Basic array techniques. Then you can use it to check part numbers for each serial and delete appropriately.
Hey Acoder,

i must admit i am pretty confused. Are you saying something like the following

Expand|Select|Wrap|Line Numbers
  1. <cfparam name="form.partscount#machinecount#" default="">
  2.   <cfset serialcheck = ArrayNew(2)>
  3.  <cfloop list="#form['partscount' & machinecount]#" index="ps">
  4.  <cfparam name="Form.hcpn_#ps#_#machinecount#" default="">
  5.  <cfparam name="Form.defective_#ps#_#machinecount#" default="0">
  6.  <cfparam name="Form.partsreturn_#ps#_#machinecount#" default="0">
  7.  <cfparam name="Form.rma_#ps#_#machinecount#" default="">
  8.  <CFSET hcpn            = Form["hcpn_" & "#ps#" & "_#machinecount#"]>
  9.  <CFSET partsreturn     = Form["partsreturn_" & "#ps#" & "_#machinecount#"]>
  10.  <CFSET defective       = Form["defective_" & "#ps#" & "_#machinecount#"]>
  11.  <CFSET rma             = Form["rma_" & "#ps#" & "_#machineCount#"]>
  12.  <CFSET ArrayAppend(serialcheck, "#Form["serialnum_" & machineCount]#")> 
  13. <cfif hcpn neq "">
  14. <cfquery name="parts" datasource="CustomerSupport">
  15.    exec usp_CS_Updateinsertparts 
  16.   <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  17.    '#Form.pk_ticketID#',
  18.     <cfqueryparam value="#hcpn#" CFSQLType = "CF_SQL_VARCHAR">,
  19.     <cfqueryparam value="#partsreturn#" CFSQLType = "CF_SQL_VARCHAR">,
  20.     <cfqueryparam value="#rma#" CFSQLType = "CF_SQL_VARCHAR">,
  21.    <cfqueryparam value="#defective#" CFSQLType = "CF_SQL_BIT">
  22.  </cfquery> 
  23.  <cfelse>
  24.  </cfif>
  25. </cfloop>
the only thing i am confused on is the following on this line, can i change the name from serialcheck to partcheck or does it need to remain the same?

Expand|Select|Wrap|Line Numbers
  1.   <cfset serialcheck = ArrayNew(2)>
and then i am confused on this line. I don't know if i need to still be checking the serialnum field or if i need to be checking another field that appears in the cfset part of my parts section?

Expand|Select|Wrap|Line Numbers
  1. <CFSET ArrayAppend(serialcheck, "#Form["serialnum_" & machineCount]#")> 
Thank you,
Rach
Nov 18 '08 #322
acoder
16,027 Expert Mod 8TB
On second thoughts, avoid using a multi-dimensional array and use a simple one as you had earlier with serialcheck. Change it back to what you had before the last post.

What you can do is in the serial loop, create a partcheck array for checking against. This will be overwritten on each round of the loop. You will need the same components that you had for serials, i.e. a query for the parts numbers, the partcheck array being appended with the parts numbers in the parts loop, a delete loop after the parts loop that deletes the parts to be deleted.
Nov 18 '08 #323
bonneylake
769 512MB
Hey Acoder,

Well i got i think most of it but i mostly am unsure where everything needs to go (its exact place). An i am confused on what field i need to get. For serial we used serialnum so i am unsure what i am suppose to use for parts. Here is what i have including what i added. i wrote added for parts on top of each thing i added.

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.  
  7.  added for parts
  8.  <cfquery name="countparts" datasource="CustomerSupport">
  9. SELECT fk_serialNo from dbo.tbl_CS_parts where fk_ticketNo=<cfqueryparam value="#Form.pk_ticketID#">
  10.  </cfquery>
  11.  
  12.  <cfset currSerialNo = 1>
  13. <!--- the array gets serials that have been inserted and updated to avoid deleteing the ones that need to be inserted or updated--->
  14.  <cfset serialcheck = ArrayNew(1)>
  15.  
  16.  added for parts
  17.  <cfset partcheck = ArrayNew(2)>
  18. <!--- first loop inserts/update serials--->
  19.  <CFLOOP list="#form.serialcount#" index="machineCount">
  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. <!---the array checks to see what serials are there--->
  32. <CFSET ArrayAppend(serialcheck, "#Form["serialnum_" & machineCount]#")> 
  33.  
  34. added for parts
  35. <CFSET ArrayAppend(partcheck, "#Form["_" & machineCount]#")>
  36.  
  37.  
  38.  <!--- inserts information into the serial table--->
  39.  <cfquery name="serialinsertupdate" datasource="CustomerSupport">
  40.     exec usp_CS_Updateinsertserial 
  41.      <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  42.      "#Form.pk_ticketID#",
  43.      <cfqueryparam value="#modelno#" CFSQLType = "CF_SQL_VARCHAR">,
  44.       <cfqueryparam value="#producttype#" CFSQLType = "CF_SQL_VARCHAR">,
  45.      <cfqueryparam value="#softhardware#" CFSQLType = "CF_SQL_VARCHAR">,
  46.      <cfqueryparam value="#resolution#" CFSQLType = "CF_SQL_VARCHAR">,
  47.      <cfqueryparam value="#resdate#">,
  48.      <cfqueryparam value="#resvertified#" CFSQLType = "CF_SQL_VARCHAR">,
  49.      <cfqueryparam value="#vertifidate#">,
  50.     <cfqueryparam value="#deptvendor#" CFSQLType = "CF_SQL_VARCHAR">,
  51.      <cfqueryparam value="#hardwarefailure#" CFSQLType = "CF_SQL_VARCHAR">   
  52.      </cfquery>
  53.  
  54.  <!---Inserts information into notes_descr table.--->
  55.  <cfquery name="description" datasource="CustomerSupport">
  56.     exec usp_CS_Insertdescription
  57.    <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  58.    '#Form.pk_ticketID#',
  59.     <cfqueryparam value="#thedescription#" CFSQLType = "CF_SQL_VARCHAR">,
  60.    '#Form.fk_addedBy#'
  61.  </cfquery>
  62.  
  63.   <!---Inserts parts information into parts table.--->
  64.   <!---because it is a bit we don't use 'ticks' around defective for parts table--->
  65.  <cfparam name="form.partscount#machinecount#" default="">
  66.  <cfloop list="#form['partscount' & machinecount]#" index="ps">
  67.  <cfparam name="Form.hcpn_#ps#_#machinecount#" default="">
  68.  <cfparam name="Form.defective_#ps#_#machinecount#" default="0">
  69.  <cfparam name="Form.partsreturn_#ps#_#machinecount#" default="0">
  70.  <cfparam name="Form.rma_#ps#_#machinecount#" default="">
  71.  <CFSET hcpn            = Form["hcpn_" & "#ps#" & "_#machinecount#"]>
  72.  <CFSET partsreturn     = Form["partsreturn_" & "#ps#" & "_#machinecount#"]>
  73.  <CFSET defective       = Form["defective_" & "#ps#" & "_#machinecount#"]>
  74.  <CFSET rma             = Form["rma_" & "#ps#" & "_#machineCount#"]>
  75. <cfif hcpn neq "">
  76. <cfquery name="parts" datasource="CustomerSupport">
  77.    exec usp_CS_Updateinsertparts 
  78.   <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  79.    '#Form.pk_ticketID#',
  80.     <cfqueryparam value="#hcpn#" CFSQLType = "CF_SQL_VARCHAR">,
  81.     <cfqueryparam value="#partsreturn#" CFSQLType = "CF_SQL_VARCHAR">,
  82.     <cfqueryparam value="#rma#" CFSQLType = "CF_SQL_VARCHAR">,
  83.    <cfqueryparam value="#defective#" CFSQLType = "CF_SQL_BIT">
  84.  </cfquery> 
  85.  <cfelse>
  86.  </cfif>
  87. </cfloop>
  88. </CFLOOP>
  89.  
  90.  
  91. <cfset serialList = ArrayToList(serialcheck, ",")>
  92. <!---when inserting/updating for serial's table is done delete serials not being updated/inserted or was deleted on purpose--->
  93. <cfloop query="countserials">
  94. <CFSET serialnum = #pka_serialNo#>
  95. <!---<cfif ListFind(serialList,form.serialnum)>--->
  96. <!---for the serials numbers not found in the serialList it will delete those serials --->
  97. <cfif not listFind(serialList,serialnum)>
  98. <cfquery name="deleteserialparts" datasource="CustomerSupport">
  99. exec usp_CS_Deleteserialparts   
  100. <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  101. '#Form.pk_ticketID#'
  102. </cfquery>
  103. </cfif>
  104. </cfloop>
  105.  
  106. added for parts
  107. <cfset partList = ArrayToList(partcheck, ",")>
  108. <cfloop query="countparts">
  109. <cfset =>
  110. <cfif not listFind(partList,)>
  111. exec usp_CS_Deleteparts
  112. <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  113. '#Form.pk_ticketID#'
  114. </cfquery>
  115. </cfif>
  116. </cfloop>
  117.  </CFIF>
Thank you :),
Rach
Nov 18 '08 #324
acoder
16,027 Expert Mod 8TB
Firstly, the query needs to be altered. Is the the primary key a combination of the ticket number and serial number? You should select whatever is the primary key.

It needs to be moved to inside the serial loop as does the partcheck array which needs to be 1-dimensional.

The array append for parts should be inside the parts loop. Finally, the parts delete should be inside the serial loop after the parts insert loop.
Nov 19 '08 #325
bonneylake
769 512MB
Hey Acoder,

Well in the parts table the primary key is pk_partID an the only thing it does is keep count of how many parts have been added and i have not used it anywhere else in the form so wanted to ask if you still wanted to use the pk_partID?

an then here is what i got for the rest of it but i am still unsure what field i should be using for the array
Expand|Select|Wrap|Line Numbers
  1. <CFIF REQUEST_METHOD EQ "POST">
  2.  <cfquery name="countserials" datasource="CustomerSupport">
  3. SELECT pka_serialNo from dbo.tbl_CS_serial where pkb_fk_ticketNo=<cfqueryparam value="#Form.pk_ticketID#">
  4.  </cfquery>
  5.  <cfset currSerialNo = 1>
  6. <!--- the array gets serials that have been inserted and updated to avoid deleteing the ones that need to be inserted or updated--->
  7.  <cfset serialcheck = ArrayNew(1)>
  8. <!--- first loop inserts/update serials--->
  9.  <CFLOOP list="#form.serialcount#" index="machineCount">
  10.  
  11.  added for parts
  12.  <cfquery name="countparts" datasource="CustomerSupport">
  13. SELECT fk_serialNo from dbo.tbl_CS_parts where fk_ticketNo=<cfqueryparam value="#Form.pk_ticketID#">
  14.  </cfquery>
  15.  
  16.   added for parts
  17.  <cfset partcheck = ArrayNew(1)>
  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. <!---the array checks to see what serials are there--->
  31. <CFSET ArrayAppend(serialcheck, "#Form["serialnum_" & machineCount]#")> 
  32.  
  33.  
  34.  
  35.  
  36.  <!--- inserts information into the serial table--->
  37.  <cfquery name="serialinsertupdate" datasource="CustomerSupport">
  38.     exec usp_CS_Updateinsertserial 
  39.      <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  40.      "#Form.pk_ticketID#",
  41.      <cfqueryparam value="#modelno#" CFSQLType = "CF_SQL_VARCHAR">,
  42.       <cfqueryparam value="#producttype#" CFSQLType = "CF_SQL_VARCHAR">,
  43.      <cfqueryparam value="#softhardware#" CFSQLType = "CF_SQL_VARCHAR">,
  44.      <cfqueryparam value="#resolution#" CFSQLType = "CF_SQL_VARCHAR">,
  45.      <cfqueryparam value="#resdate#">,
  46.      <cfqueryparam value="#resvertified#" CFSQLType = "CF_SQL_VARCHAR">,
  47.      <cfqueryparam value="#vertifidate#">,
  48.     <cfqueryparam value="#deptvendor#" CFSQLType = "CF_SQL_VARCHAR">,
  49.      <cfqueryparam value="#hardwarefailure#" CFSQLType = "CF_SQL_VARCHAR">   
  50.      </cfquery>
  51.  
  52.  <!---Inserts information into notes_descr table.--->
  53.  <cfquery name="description" datasource="CustomerSupport">
  54.     exec usp_CS_Insertdescription
  55.    <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  56.    '#Form.pk_ticketID#',
  57.     <cfqueryparam value="#thedescription#" CFSQLType = "CF_SQL_VARCHAR">,
  58.    '#Form.fk_addedBy#'
  59.  </cfquery>
  60.  
  61.   <!---Inserts parts information into parts table.--->
  62.   <!---because it is a bit we don't use 'ticks' around defective for parts table--->
  63.  <cfparam name="form.partscount#machinecount#" default="">
  64.  <cfloop list="#form['partscount' & machinecount]#" index="ps">
  65.  <cfparam name="Form.hcpn_#ps#_#machinecount#" default="">
  66.  <cfparam name="Form.defective_#ps#_#machinecount#" default="0">
  67.  <cfparam name="Form.partsreturn_#ps#_#machinecount#" default="0">
  68.  <cfparam name="Form.rma_#ps#_#machinecount#" default="">
  69.  <CFSET hcpn            = Form["hcpn_" & "#ps#" & "_#machinecount#"]>
  70.  <CFSET partsreturn     = Form["partsreturn_" & "#ps#" & "_#machinecount#"]>
  71.  <CFSET defective       = Form["defective_" & "#ps#" & "_#machinecount#"]>
  72.  <CFSET rma             = Form["rma_" & "#ps#" & "_#machineCount#"]>
  73.  
  74.  added for parts
  75. <CFSET ArrayAppend(partcheck, "#Form["_" & machineCount]#")>
  76.  
  77. <cfif hcpn neq "">
  78. <cfquery name="parts" datasource="CustomerSupport">
  79.    exec usp_CS_Updateinsertparts 
  80.   <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  81.    '#Form.pk_ticketID#',
  82.     <cfqueryparam value="#hcpn#" CFSQLType = "CF_SQL_VARCHAR">,
  83.     <cfqueryparam value="#partsreturn#" CFSQLType = "CF_SQL_VARCHAR">,
  84.     <cfqueryparam value="#rma#" CFSQLType = "CF_SQL_VARCHAR">,
  85.    <cfqueryparam value="#defective#" CFSQLType = "CF_SQL_BIT">
  86.  </cfquery> 
  87.  <cfelse>
  88.  </cfif>
  89. </cfloop>
  90.  
  91. added for parts
  92. <cfset partList = ArrayToList(partcheck, ",")>
  93. <cfloop query="countparts">
  94. <cfset =>
  95. <cfif not listFind(partList,)>
  96. exec usp_CS_Deleteparts
  97. <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  98. '#Form.pk_ticketID#'
  99. </cfquery>
  100. </cfif>
  101. </cfloop>
  102. </CFLOOP>
  103.  
  104.  
  105. <cfset serialList = ArrayToList(serialcheck, ",")>
  106. <!---when inserting/updating for serial's table is done delete serials not being updated/inserted or was deleted on purpose--->
  107. <cfloop query="countserials">
  108. <CFSET serialnum = #pka_serialNo#>
  109. <!---<cfif ListFind(serialList,form.serialnum)>--->
  110. <!---for the serials numbers not found in the serialList it will delete those serials --->
  111. <cfif not listFind(serialList,serialnum)>
  112. <cfquery name="deleteserialparts" datasource="CustomerSupport">
  113. exec usp_CS_Deleteserialparts   
  114. <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  115. '#Form.pk_ticketID#'
  116. </cfquery>
  117. </cfif>
  118. </cfloop>
  119.  </CFIF>

Thank you :),
Rach
Nov 19 '08 #326
acoder
16,027 Expert Mod 8TB
Is it not a composite primary key? So is the pk_partID completely unique that would uniquely identify only one part?
Nov 19 '08 #327
bonneylake
769 512MB
Is it not a composite primary key? So is the pk_partID completely unique that would uniquely identify only one part?
Hey Acoder,

Yes it would uniquely identify each part. See your point now on what your saying. Because 2 parts could have the same serial number and ticket number an instead of deleting only the one it would delete both. However with pk_partID it would only delete the one part.So would something like this work?

Expand|Select|Wrap|Line Numbers
  1.  <cfquery name="countparts" datasource="CustomerSupport">
  2. SELECT pk_partID from dbo.tbl_CS_parts where fk_ticketNo=<cfqueryparam value="#Form.pk_ticketID#">
  3.  </cfquery>
an then for this line i am not sure what field i am suppose to be getting for it
Expand|Select|Wrap|Line Numbers
  1. <CFSET ArrayAppend(partcheck, "#Form[]#")>
  2.  
Thank you :),
Rach
Nov 19 '08 #328
acoder
16,027 Expert Mod 8TB
Yes it would uniquely identify each part. See your point now on what your saying. Because 2 parts could have the same serial number and ticket number an instead of deleting only the one it would delete both.
Exactly :)
For the query, also match the serial number too to get the parts for one serial at a time.

The field that you add to the array depends on what you have to identify each part. In the parts, do you have something that corresponds to partID or anything which relates to a field in the parts table?
Nov 19 '08 #329
bonneylake
769 512MB
Hey Acoder,

Here is the query for parts.

Expand|Select|Wrap|Line Numbers
  1. <cfquery name="countparts" datasource="CustomerSupport">
  2. SELECT pk_partID from dbo.tbl_CS_parts where fk_serialNo=<cfqueryparam value="#serialnum#"> and fk_ticketNo=<cfqueryparam value="#Form.pk_ticketID#">
an for the array i really don't got anything that relates (i don't believe). In the part table all i have are the following fields pk_partID,fk_serialNo,fk_ticketNo,hc_partNo,part_r eturned,rma_number,and defective. The only thing i can think of is to use the hcpn. The part_returned and defective are checkboxes and the rma_number they might never fill out (because its a textarea) so i would think the hcpn be the best or else find someway to use the serialNo or ticketNo. I am pretty sure each hcpn for one serial be unique, only concern is if they have 2 computers an both need the same part with the same number.

Thank you,
Rach
Nov 19 '08 #330
acoder
16,027 Expert Mod 8TB
What you can do is add a hidden field for the part ID in the display page, so when submitted, the parts number/ID is submitted with each part.
Nov 19 '08 #331
bonneylake
769 512MB
What you can do is add a hidden field for the part ID in the display page, so when submitted, the parts number/ID is submitted with each part.
Hey Acoder,

In the cticketpage2edit.cfm (before submit page) i added the hidden field as such

Expand|Select|Wrap|Line Numbers
  1. <cfoutput query="parts">
  2. <input type="hidden" name="pk_partID" id="pk_partID" value="#pk_partID#" />
  3. </cfoutput>
an then when i tried to submit the following

Expand|Select|Wrap|Line Numbers
  1. <CFIF REQUEST_METHOD EQ "POST">
  2. <!---counts how many serials are there--->
  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. <!--- the array gets serials that have been inserted and updated to avoid deleteing the ones that need to be inserted or updated--->
  8.  <cfset serialcheck = ArrayNew(1)>
  9. <!--- first loop inserts/update serials--->
  10.  <CFLOOP list="#form.serialcount#" index="machineCount">
  11.  
  12.  added for parts
  13.  <cfquery name="countparts" datasource="CustomerSupport">
  14. SELECT pk_partID from dbo.tbl_CS_parts where fk_serialNo=<cfqueryparam value="#serialnum#"> and fk_ticketNo=<cfqueryparam value="#Form.pk_ticketID#">
  15.  
  16.  </cfquery>
  17.  
  18.   added for parts
  19.  <cfset partcheck = ArrayNew(1)>
  20.  
  21. <CFSET serialnum       = Form["serialnum_" & machineCount]>
  22. <CFSET modelno         = Form["modelno_" & machineCount]>
  23. <CFSET producttype     = Form["producttype_" & machineCount]>
  24. <CFSET softhardware    = Form["softhardware_" & machineCount]>
  25. <CFSET resolution      = Form["resolution_" & machineCount]>
  26. <CFSET resdate         = Form["resdate_" & machineCount]>
  27. <CFSET resvertified    = Form["resvertified_" & machineCount]>
  28. <CFSET vertifidate     = Form["vertifidate_" & machineCount]>
  29. <CFSET deptvendor      = Form["deptvendor_" & machinecount]>
  30. <CFSET hardwarefailure = Form["hardwarefailure_" & machineCount]>
  31. <CFSET thedescription  = Form["thedescription_" & machineCount]>
  32. <!---the array checks to see what serials are there--->
  33. <CFSET ArrayAppend(serialcheck, "#Form["serialnum_" & machineCount]#")> 
  34.  
  35.  
  36.  
  37.  
  38.  <!--- inserts information into the serial table--->
  39.  <cfquery name="serialinsertupdate" datasource="CustomerSupport">
  40.     exec usp_CS_Updateinsertserial 
  41.      <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  42.      "#Form.pk_ticketID#",
  43.      <cfqueryparam value="#modelno#" CFSQLType = "CF_SQL_VARCHAR">,
  44.       <cfqueryparam value="#producttype#" CFSQLType = "CF_SQL_VARCHAR">,
  45.      <cfqueryparam value="#softhardware#" CFSQLType = "CF_SQL_VARCHAR">,
  46.      <cfqueryparam value="#resolution#" CFSQLType = "CF_SQL_VARCHAR">,
  47.      <cfqueryparam value="#resdate#">,
  48.      <cfqueryparam value="#resvertified#" CFSQLType = "CF_SQL_VARCHAR">,
  49.      <cfqueryparam value="#vertifidate#">,
  50.     <cfqueryparam value="#deptvendor#" CFSQLType = "CF_SQL_VARCHAR">,
  51.      <cfqueryparam value="#hardwarefailure#" CFSQLType = "CF_SQL_VARCHAR">   
  52.      </cfquery>
  53.  
  54.  <!---Inserts information into notes_descr table.--->
  55.  <cfquery name="description" datasource="CustomerSupport">
  56.     exec usp_CS_Insertdescription
  57.    <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  58.    '#Form.pk_ticketID#',
  59.     <cfqueryparam value="#thedescription#" CFSQLType = "CF_SQL_VARCHAR">,
  60.    '#Form.fk_addedBy#'
  61.  </cfquery>
  62.  
  63.   <!---Inserts parts information into parts table.--->
  64.   <!---because it is a bit we don't use 'ticks' around defective for parts table--->
  65.  <cfparam name="form.partscount#machinecount#" default="">
  66.  <cfloop list="#form['partscount' & machinecount]#" index="ps">
  67.  <cfparam name="Form.hcpn_#ps#_#machinecount#" default="">
  68.  <cfparam name="Form.defective_#ps#_#machinecount#" default="0">
  69.  <cfparam name="Form.partsreturn_#ps#_#machinecount#" default="0">
  70.  <cfparam name="Form.rma_#ps#_#machinecount#" default="">
  71.   <CFSET pk_partID           = Form["pk_partID_" & "#ps#" & "_#machinecount#"]>
  72.  <CFSET hcpn            = Form["hcpn_" & "#ps#" & "_#machinecount#"]>
  73.  <CFSET partsreturn     = Form["partsreturn_" & "#ps#" & "_#machinecount#"]>
  74.  <CFSET defective       = Form["defective_" & "#ps#" & "_#machinecount#"]>
  75.  <CFSET rma             = Form["rma_" & "#ps#" & "_#machineCount#"]>
  76.  
  77.  added for parts
  78. <CFSET ArrayAppend(partcheck, "#Form["pk_partID_" & "#ps#" & "_#machinecount#"]#")>
  79.  
  80. <cfif hcpn neq "">
  81. <cfquery name="parts" datasource="CustomerSupport">
  82.    exec usp_CS_Updateinsertparts 
  83.   <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  84.    '#Form.pk_ticketID#',
  85.     <cfqueryparam value="#hcpn#" CFSQLType = "CF_SQL_VARCHAR">,
  86.     <cfqueryparam value="#partsreturn#" CFSQLType = "CF_SQL_VARCHAR">,
  87.     <cfqueryparam value="#rma#" CFSQLType = "CF_SQL_VARCHAR">,
  88.    <cfqueryparam value="#defective#" CFSQLType = "CF_SQL_BIT">
  89.  </cfquery> 
  90.  <cfelse>
  91.  </cfif>
  92. </cfloop>
  93.  
  94. added for parts
  95. <cfset partList = ArrayToList(partcheck, ",")>
  96. <cfloop query="countparts">
  97. <cfset pk_partID= #pk_partID#>
  98. <cfif not listFind(partList,pk_partID)>
  99. <cfquery name="deleteparts" datasource="CustomerSupport">
  100. exec usp_CS_Deleteparts
  101. <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  102. '#Form.pk_ticketID#'
  103. </cfquery>
  104. </cfif>
  105. </cfloop>
  106. </CFLOOP>
  107.  
  108.  
  109. <cfset serialList = ArrayToList(serialcheck, ",")>
  110. <!---when inserting/updating for serial's table is done delete serials not being updated/inserted or was deleted on purpose--->
  111. <cfloop query="countserials">
  112. <CFSET serialnum = #pka_serialNo#>
  113. <!---<cfif ListFind(serialList,form.serialnum)>--->
  114. <!---for the serials numbers not found in the serialList it will delete those serials --->
  115. <cfif not listFind(serialList,serialnum)>
  116. <cfquery name="deleteserialparts" datasource="CustomerSupport">
  117. exec usp_CS_Deleteserialparts   
  118. <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  119. '#Form.pk_ticketID#'
  120. </cfquery>
  121. </cfif>
  122. </cfloop>
  123.  </CFIF>

i got the error.Error resolving parameter SERIALNUM
ColdFusion was unable to determine the value of the parameter.
it said it had trouble with this line

Expand|Select|Wrap|Line Numbers
  1.  <cfquery name="countparts" datasource="CustomerSupport">
  2. SELECT pk_partID from dbo.tbl_CS_parts where fk_serialNo=<cfqueryparam value="#serialnum#"> and fk_ticketNo=<cfqueryparam value="#Form.pk_ticketID#">
Thank you,
Rach
Nov 19 '08 #332
acoder
16,027 Expert Mod 8TB
That error is because serialnum hasn't been defined at that point, so you need to move the query lower down to after the serialnum variable has been set.

What you've added as a hidden field isn't enough. You need to add a unique field name for each part like serials, e.g. partid_1_1 (where 1 and 1 would be the part and serial numbers) and you don't need to use a separate cfoutput for it.
Nov 19 '08 #333
bonneylake
769 512MB
Hey Acoder,

Well i added the hidden field into the parts section of the div like so

Expand|Select|Wrap|Line Numbers
  1. <cfquery name="getparts" dbtype="query" >
  2. SELECT *
  3. FROM parts
  4. WHERE fk_serialNo=<cfqueryparam value="#pka_serialNo#" 
  5. cfsqltype="cf_sql_char" maxLength="20">
  6. </cfquery>
  7. <cfset count1 = 0>
  8. <cfloop query="getparts">
  9. <cfset count1 = count1 + 1>
  10. <div id="part#count1#Name#count#">
  11.  
  12. <!---THIS IS THE PARTS SECTION--->
  13.  
  14. <input type="hidden" name="pk_partID_#count1#_#count#" id="pk_partID" value="#pk_partID#" />
  15.  
an then here is how i did the other page would this be ok?


Expand|Select|Wrap|Line Numbers
  1. <CFIF REQUEST_METHOD EQ "POST">
  2. <!---counts how many serials are there--->
  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. <!--- the array gets serials that have been inserted and updated to avoid deleteing the ones that need to be inserted or updated--->
  8.  <cfset serialcheck = ArrayNew(1)>
  9. <!--- first loop inserts/update serials--->
  10.  <CFLOOP list="#form.serialcount#" index="machineCount">
  11. <CFSET serialnum       = Form["serialnum_" & machineCount]>
  12. <CFSET modelno         = Form["modelno_" & machineCount]>
  13. <CFSET producttype     = Form["producttype_" & machineCount]>
  14. <CFSET softhardware    = Form["softhardware_" & machineCount]>
  15. <CFSET resolution      = Form["resolution_" & machineCount]>
  16. <CFSET resdate         = Form["resdate_" & machineCount]>
  17. <CFSET resvertified    = Form["resvertified_" & machineCount]>
  18. <CFSET vertifidate     = Form["vertifidate_" & machineCount]>
  19. <CFSET deptvendor      = Form["deptvendor_" & machinecount]>
  20. <CFSET hardwarefailure = Form["hardwarefailure_" & machineCount]>
  21. <CFSET thedescription  = Form["thedescription_" & machineCount]>
  22. <!---the array checks to see what serials are there--->
  23. <CFSET ArrayAppend(serialcheck, "#Form["serialnum_" & machineCount]#")> 
  24.  added for parts
  25.  <cfquery name="countparts" datasource="CustomerSupport">
  26. SELECT pk_partID from dbo.tbl_CS_parts where fk_serialNo=<cfqueryparam value="#serialnum#"> and fk_ticketNo=<cfqueryparam value="#Form.pk_ticketID#">
  27.  </cfquery>
  28.    added for parts
  29.  <cfset partcheck = ArrayNew(1)>
  30.  
  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.hcpn_#ps#_#machinecount#" default="">
  63.  <cfparam name="Form.defective_#ps#_#machinecount#" default="0">
  64.  <cfparam name="Form.partsreturn_#ps#_#machinecount#" default="0">
  65.  <cfparam name="Form.rma_#ps#_#machinecount#" default="">
  66.   <CFSET pk_partID           = Form["pk_partID_" & "#ps#" & "_#machinecount#"]>
  67.  <CFSET hcpn            = Form["hcpn_" & "#ps#" & "_#machinecount#"]>
  68.  <CFSET partsreturn     = Form["partsreturn_" & "#ps#" & "_#machinecount#"]>
  69.  <CFSET defective       = Form["defective_" & "#ps#" & "_#machinecount#"]>
  70.  <CFSET rma             = Form["rma_" & "#ps#" & "_#machineCount#"]>
  71.  
  72.  added for parts
  73. <CFSET ArrayAppend(partcheck, "#Form["pk_partID_" & "#ps#" & "_#machinecount#"]#")>
  74.  
  75. <cfif hcpn neq "">
  76. <cfquery name="parts" datasource="CustomerSupport">
  77.    exec usp_CS_Updateinsertparts 
  78.   <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  79.    '#Form.pk_ticketID#',
  80.     <cfqueryparam value="#hcpn#" CFSQLType = "CF_SQL_VARCHAR">,
  81.     <cfqueryparam value="#partsreturn#" CFSQLType = "CF_SQL_VARCHAR">,
  82.     <cfqueryparam value="#rma#" CFSQLType = "CF_SQL_VARCHAR">,
  83.    <cfqueryparam value="#defective#" CFSQLType = "CF_SQL_BIT">
  84.  </cfquery> 
  85.  <cfelse>
  86.  </cfif>
  87. </cfloop>
  88.  
  89. added for parts
  90. <cfset partList = ArrayToList(partcheck, ",")>
  91. <cfloop query="countparts">
  92. <cfset pk_partID= #pk_partID#>
  93. <cfif not listFind(partList,pk_partID)>
  94. <cfquery name="deleteparts" datasource="CustomerSupport">
  95. exec usp_CS_Deleteparts
  96. <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  97. '#Form.pk_ticketID#'
  98. </cfquery>
  99. </cfif>
  100. </cfloop>
  101. </CFLOOP>
  102.  
  103.  
  104. <cfset serialList = ArrayToList(serialcheck, ",")>
  105. <!---when inserting/updating for serial's table is done delete serials not being updated/inserted or was deleted on purpose--->
  106. <cfloop query="countserials">
  107. <CFSET serialnum = #pka_serialNo#>
  108. <!---<cfif ListFind(serialList,form.serialnum)>--->
  109. <!---for the serials numbers not found in the serialList it will delete those serials --->
  110. <cfif not listFind(serialList,serialnum)>
  111. <cfquery name="deleteserialparts" datasource="CustomerSupport">
  112. exec usp_CS_Deleteserialparts   
  113. <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  114. '#Form.pk_ticketID#'
  115. </cfquery>
  116. </cfif>
  117. </cfloop>
  118.  </CFIF>
  119.  
Thank you,
Rach
Nov 19 '08 #334
acoder
16,027 Expert Mod 8TB
Good work there! That looks right from a cursory glance, but you can only test to find out.
Nov 19 '08 #335
bonneylake
769 512MB
Hey Acoder,

I got it! YAY, THANK YOU THANK YOU!!!. I got one more question which i think should be an easy one. But anyway i was thinking about it on the way to work. An well when we choose a serial to delete each serial will have one or more descriptions associated with it so i was thinking that the descriptions associated with the serial should be deleted as well (if they choose that particular serial to delete). So i was wondering could i do the same thing i did for serial and parts for description as well?

Thank you :),
Rach
Nov 19 '08 #336
acoder
16,027 Expert Mod 8TB
Excellent.

For the description, you could just add a delete cascade to avoid requiring any Coldfusion code.
Nov 19 '08 #337
bonneylake
769 512MB
Hey Acoder,

Only question i got left is where would i put the delete description amongst everything?

Expand|Select|Wrap|Line Numbers
  1. <CFIF REQUEST_METHOD EQ "POST">
  2. <!---counts how many serials are there--->
  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. <!--- the array gets serials that have been inserted and updated to avoid deleteing the ones that need to be inserted or updated--->
  8.  <cfset serialcheck = ArrayNew(1)>
  9. <!--- first loop inserts/update serials--->
  10.  <CFLOOP list="#form.serialcount#" index="machineCount">
  11. <CFSET serialnum       = Form["serialnum_" & machineCount]>
  12. <CFSET modelno         = Form["modelno_" & machineCount]>
  13. <CFSET producttype     = Form["producttype_" & machineCount]>
  14. <CFSET softhardware    = Form["softhardware_" & machineCount]>
  15. <CFSET resolution      = Form["resolution_" & machineCount]>
  16. <CFSET resdate         = Form["resdate_" & machineCount]>
  17. <CFSET resvertified    = Form["resvertified_" & machineCount]>
  18. <CFSET vertifidate     = Form["vertifidate_" & machineCount]>
  19. <CFSET deptvendor      = Form["deptvendor_" & machinecount]>
  20. <CFSET hardwarefailure = Form["hardwarefailure_" & machineCount]>
  21. <CFSET thedescription  = Form["thedescription_" & machineCount]>
  22. <!---the array checks to see what serials are there--->
  23. <CFSET ArrayAppend(serialcheck, "#Form["serialnum_" & machineCount]#")> 
  24.  added for parts
  25.  <cfquery name="countparts" datasource="CustomerSupport">
  26. SELECT pk_partID from dbo.tbl_CS_parts where fk_serialNo=<cfqueryparam value="#serialnum#"> and fk_ticketNo=<cfqueryparam value="#Form.pk_ticketID#">
  27.  </cfquery>
  28.  
  29.  
  30.  <!--- inserts information into the serial table--->
  31.  <cfquery name="serialinsertupdate" datasource="CustomerSupport">
  32.     exec usp_CS_Updateinsertserial 
  33.      <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  34.      "#Form.pk_ticketID#",
  35.      <cfqueryparam value="#modelno#" CFSQLType = "CF_SQL_VARCHAR">,
  36.       <cfqueryparam value="#producttype#" CFSQLType = "CF_SQL_VARCHAR">,
  37.      <cfqueryparam value="#softhardware#" CFSQLType = "CF_SQL_VARCHAR">,
  38.      <cfqueryparam value="#resolution#" CFSQLType = "CF_SQL_VARCHAR">,
  39.      <cfqueryparam value="#resdate#">,
  40.      <cfqueryparam value="#resvertified#" CFSQLType = "CF_SQL_VARCHAR">,
  41.      <cfqueryparam value="#vertifidate#">,
  42.     <cfqueryparam value="#deptvendor#" CFSQLType = "CF_SQL_VARCHAR">,
  43.      <cfqueryparam value="#hardwarefailure#" CFSQLType = "CF_SQL_VARCHAR">   
  44.      </cfquery>
  45.  
  46.  <!---Inserts information into notes_descr table.--->
  47.  <cfquery name="description" datasource="CustomerSupport">
  48.     exec usp_CS_Insertdescription
  49.    <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  50.    '#Form.pk_ticketID#',
  51.     <cfqueryparam value="#thedescription#" CFSQLType = "CF_SQL_VARCHAR">,
  52.    '#Form.fk_addedBy#'
  53.  </cfquery>
  54.  
  55.   <!---Inserts parts information into parts table.--->
  56.   <!---because it is a bit we don't use 'ticks' around defective for parts table--->
  57.  <cfparam name="form.partscount#machinecount#" default="">
  58.  <cfloop list="#form['partscount' & machinecount]#" index="ps">
  59.   <cfparam name="Form.pkpartID_#ps#_#machinecount#" default="">
  60.  <cfparam name="Form.hcpn_#ps#_#machinecount#" default="">
  61.  <cfparam name="Form.defective_#ps#_#machinecount#" default="0">
  62.  <cfparam name="Form.partsreturn_#ps#_#machinecount#" default="0">
  63.  <cfparam name="Form.rma_#ps#_#machinecount#" default="">
  64.  <CFSET pkpartID           = Form["pkpartID_" & "#ps#" & "_#machinecount#"]>
  65.  <CFSET hcpn            = Form["hcpn_" & "#ps#" & "_#machinecount#"]>
  66.  <CFSET partsreturn     = Form["partsreturn_" & "#ps#" & "_#machinecount#"]>
  67.  <CFSET defective       = Form["defective_" & "#ps#" & "_#machinecount#"]>
  68.  <CFSET rma             = Form["rma_" & "#ps#" & "_#machineCount#"]>
  69.  
  70.  added for parts
  71. <CFSET ArrayAppend(partcheck, "#Form["pkpartID_" & "#ps#" & "_#machinecount#"]#")>
  72.  
  73. <cfif hcpn neq "">
  74. <cfquery name="parts" datasource="CustomerSupport">
  75.    exec usp_CS_Updateinsertparts 
  76.   <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  77.    '#Form.pk_ticketID#',
  78.     <cfqueryparam value="#hcpn#" CFSQLType = "CF_SQL_VARCHAR">,
  79.     <cfqueryparam value="#partsreturn#" CFSQLType = "CF_SQL_VARCHAR">,
  80.     <cfqueryparam value="#rma#" CFSQLType = "CF_SQL_VARCHAR">,
  81.    <cfqueryparam value="#defective#" CFSQLType = "CF_SQL_BIT">
  82.  </cfquery> 
  83.  <cfelse>
  84.  </cfif>
  85. </cfloop>
  86.  
  87. added for parts
  88. <cfset partList = ArrayToList(partcheck, ",")>
  89. <cfloop query="countparts">
  90. <cfset pk_partID= #pk_partID#>
  91. <cfif not listFind(partList,pk_partID)>
  92. <cfquery name="deleteparts" datasource="CustomerSupport">
  93. exec usp_CS_Deleteparts
  94. <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  95. '#Form.pk_ticketID#'
  96. </cfquery>
  97. </cfif>
  98. </cfloop>
  99. </CFLOOP>
  100.  
  101.  
  102. <cfset serialList = ArrayToList(serialcheck, ",")>
  103. <!---when inserting/updating for serial's table is done delete serials not being updated/inserted or was deleted on purpose--->
  104. <cfloop query="countserials">
  105. <CFSET serialnum = #pka_serialNo#>
  106. <!---<cfif ListFind(serialList,form.serialnum)>--->
  107. <!---for the serials numbers not found in the serialList it will delete those serials --->
  108. <cfif not listFind(serialList,serialnum)>
  109. <cfquery name="deleteserialparts" datasource="CustomerSupport">
  110. exec usp_CS_Deleteserialparts   
  111. <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  112. '#Form.pk_ticketID#'
  113. </cfquery>
  114. </cfif>
  115. </cfloop>
  116. </CFIF>
Thank you :),
Rach
Nov 19 '08 #338
acoder
16,027 Expert Mod 8TB
No, I meant a delete cascade in your foreign key constraint in SQL Server.
Nov 19 '08 #339
bonneylake
769 512MB
No, I meant a delete cascade in your foreign key constraint in SQL Server.
Hey Acoder,

I already created the stored procedure for the delete which is

Expand|Select|Wrap|Line Numbers
  1. set ANSI_NULLS ON
  2. set QUOTED_IDENTIFIER ON
  3. go
  4.  
  5.  
  6.  
  7.  
  8. -- =============================================
  9. -- Author:        <Author,,Name>
  10. -- Create date: <Create Date,,>
  11. -- Description:    <Description,,>
  12.  -- =============================================
  13. ALTER PROCEDURE [dbo].[usp_CS_Deletenotesdescription]
  14. -- Add the parameters for the stored procedure here
  15. (@fk_serialNo nvarchar(100),
  16. @fk_ticketNo nvarchar(100)) as
  17.  
  18. delete from dbo.tbl_CS_notes_descr where (fk_serialNo = @fk_serialNo and fk_ticketNo = @fk_ticketNo)
  19.  
i was asking where the exec for it needed to ago amongst everything. or am i getting delete cascade confused with something else?

Thank you,
Rach
Nov 19 '08 #340
acoder
16,027 Expert Mod 8TB
I see. In that case, you could call it in Coldfusion, though if you want, you could still just use a delete cascade. For an idea of where it would go, if you recall from the thread in the SQL Server forum, when you create a foreign key constraint, if you specify ON DELETE CASCADE, the description would automatically get deleted when you delete the parent table (which would be serial). It saves out on defining and calling a separate stored procedure.

Anyway, if you still want to use this delete proc. then call it in the delete serial loop just after the "deleteserialparts" query.
Nov 20 '08 #341
bonneylake
769 512MB
Hey Acoder,

Well i guess i need to do things your way. Because when i tried to delete for some reason it deleted everything that was written for the description field but it didn't delete the record.But how would i go about creating the delete cascade?

I also have another question. I wanted to add back in the blank fields (if they didn't fill out a serial/part) an well for some reason if i go back an look it will say serial 1 like its suppose to. But when i click add when i add a serial it will also say the new serial i added was 1.An also i am unable to add parts on the first serial (that was not filled out) for some reason. here is what i have on my cticketpage2edit.cfm page

<!---If have any blanks in serial table it will make field appear--->
Expand|Select|Wrap|Line Numbers
  1. <cfoutput query="serial">
  2. <cfset model_no = #model_no#>
  3. <cfset product_type = #product_type#>
  4. <cfset type_hardware_failure = #type_hardware_failure#>
  5. <cfset pka_serialNo = #pka_serialNo#>
  6. <cfset software_hardware = #software_hardware#>
  7. <cfloop query="getnotes">
  8. <cfset description = #description#></cfloop>
  9. <cfset resolution = #resolution#>
  10. <cfset resolution_date = #resolution_date#>
  11. <cfset resolution_verified_by = #resolution_verified_by#>
  12. <cfset verification_date = #verification_date#>
  13. <cfset dept_responsibility = #dept_responsibility#>
  14. </cfoutput>
  15. <cfif serial.recordcount is 0>
  16. <cfset model_no = "">
  17. <cfset product_type = "">
  18. <cfset type_hardware_failure = "">
  19. <cfset pka_serialNo = "">
  20. <cfset software_hardware = "">
  21. <cfif getnotes.recordcount is 0>
  22. <cfset description = ""></cfif>
  23. <cfset resolution = "">
  24. <cfset resolution_date = "">
  25. <cfset resolution_verified_by = "">
  26. <cfset verification_date = "">
  27. <cfset dept_responsibility = "">
  28. <cfinclude template="serialdisplay.cfm">
  29. </cfif>
serialdisplay.cfm
Expand|Select|Wrap|Line Numbers
  1. <cfif serial.recordcount is 0>  
  2. <cfset count = 0>
  3. <cfset count = count + 1>
  4. <cfoutput>
  5. <div id="dynamic#count#Input">
  6. <table class="zpExpandedTable" id="modeltable"> 
  7. <th class="sectiontitletick" colspan="7">
  8. Serial Information #count# </th>
  9. <tr>
  10. <td id="paddingformultitop">Model No:&nbsp;&nbsp;&nbsp;&nbsp;</td>
  11. </td>
  12. <td>
  13. <select name="modelno_#count#">
  14. <option value="">Make A Selection</option>
  15. <cfloop query="models">
  16. <option value="#model#">#model#</option>
  17.  
  18. </cfloop> 
  19. </select>
  20.  
  21. </td>
  22. <td>
  23. &nbsp;&nbsp;&nbsp;&nbsp;Product Type:
  24. </td>
  25. <td>
  26. <select name="producttype_#count#">
  27. <option value="" selected>No Choice</option>
  28. <cfloop query="getProdType">
  29. <option value="#pk_productType#">#pk_productType#</option> 
  30. </cfloop>
  31. </select>
  32. </td>
  33. <td class="red'">
  34. &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Type Of Hardware Failure*:
  35. </td>
  36. <td>
  37. <select name="hardwarefailure_#count#">
  38. <option value="" selected>Make A Selection</option>
  39. <cfloop query="getHardwareFail">
  40. <option value="#pk_hardwareFailure#">#pk_hardwareFailure#</option>
  41. </cfloop>
  42. </select>
  43. </td>
  44. </tr>
  45. <table>
  46.  
  47. <!--- Shows what was previously entered for Serial Number and Software/Hardware  --->
  48. <table class="zpExpandedTable" id="modeltable">
  49. <tr>
  50. <td id="paddingformultitop">
  51. Serial Number:&nbsp;&nbsp;
  52. <input type="text" name="serialnum_#count#" value="">
  53. &nbsp;&nbsp;&nbsp;&nbsp;Software/Hardware:&nbsp;&nbsp;
  54. <select name="softhardware_#count#">
  55. <option value="" selected>No Choice</option>
  56. <cfloop query="getSoftHard">
  57. <option value="#pk_softwareHardware#">#pk_softwareHardware#</option>
  58. </cfloop>
  59. </select>
  60. </td>
  61. </tr>
  62. </table>
  63.  
  64. <!--- Shows what was previously entered for Description ---> 
  65.  
  66. <table class="zpExpandedTable" id="resoltable" cellpadding="3" cellspacing="0">
  67. <tr>
  68. <td id="paddingformutli">
  69. Description:&nbsp;&nbsp;
  70. </td>
  71. <td class="descriptionmoveinmulti">
  72. ( You may enter up to 1000 characters. )
  73. <br>
  74. <textarea maxlength='1000' onkeyup='return descriptionmaxlength(this)' onkeydown='return descriptionmaxlength(this)'rows='4' cols='60' name="thedescription_#count#"></textarea>
  75. </td>
  76. </tr>
  77. </table>
  78.  
  79. <!---Shows what was previously entered for Resolution  --->
  80.  
  81. <table class="zpExpandedTable" id="resoltable" cellpadding="1" cellspacing="0">
  82. <tr>
  83. <td id="paddingformutli">
  84. Resolution:&nbsp;&nbsp;
  85. </td>
  86. <td class="resolutionmoveinmulti">
  87. ( You may enter up to 1500 characters. )
  88. <br>
  89. <textarea maxlength='1500' onkeyup='return resolutionmaxlength(this)' onkeydown='return resolutionmaxlength(this)' rows='4' cols='60' name="resolution_#count#"></textarea>
  90. </td>
  91. </tr>
  92. </table>
  93.  
  94. <!--- Shows what was previously entered for Resolution Date, Current Date (for resolution date) and resolution vertified as effective by  --->
  95.  
  96. <table class="zpExpandedTable" id="resoldatetab" cellpadding="1" cellspacing="0">
  97. <tr>
  98. <td id="paddingformultitop">
  99. Resolution Date:&nbsp;(MM/DD/YYYY)&nbsp;&nbsp;
  100. </td>
  101. <td>
  102. <input type="text" name="resdate_#count#" value="">&nbsp;&nbsp;
  103.  
  104. &nbsp;&nbsp;&nbsp;&nbsp;Current Date:&nbsp;&nbsp;
  105. <input type="checkbox" name="currentdateresol_#count#" 
  106. onClick="resdate_#count#.value=fill_date()">
  107.  
  108. </td>
  109. <td>
  110. Resolution Verified as effective by:&nbsp;&nbsp;
  111. </td>
  112. <td>
  113. <select name="resvertified_#count#">
  114. <option value="" selected>Make A Selection</option>
  115. <cfloop query="gettech">
  116. <option value="#fname# #lname#">#fname# #lname#</option>
  117. </cfloop>
  118. </select>
  119. </td>
  120. </tr>
  121. </table>
  122. <!--- Shows what was previously entered for Vertification Date, Current Date (for vertification date)   --->
  123. <table class="zpExpandedTable" id="resoltable" cellpadding="1" cellspacing="0">
  124. <tr>
  125. <td id="paddingformultitop">
  126. Verification Date:&nbsp;(MM/DD/YYYY)&nbsp;&nbsp;
  127. </td>
  128. <td class="vertificationmoveinmulti">
  129. <input type="text" name="vertifidate_#count#" value="">&nbsp;&nbsp;
  130. &nbsp;&nbsp;&nbsp;&nbsp;Current Date:&nbsp;&nbsp;
  131. <input type="checkbox" name="currentdatevert_#count#" 
  132. onClick="vertifidate_#count#.value=fill_date()">
  133.  
  134. </td>
  135. </tr>
  136. </table>
  137.  
  138. <!--- Shows what was previously entered for Dept/Vendor Responsibility  --->
  139. <table class="zpExpandedTable" id="resoltable" cellpadding="1" cellspacing="0">
  140. <tr>
  141. <td class="red" id="paddingformultitop">
  142. Dept/Vendor Responsibility*:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
  143. <select name="deptvendor_#count#">
  144. <option value="" selected>Make A Selection</option>
  145. <cfloop query="getDeptVendor">
  146. <option value="#pk_deptVendor#">#pk_deptVendor#</option>
  147. </cfloop>
  148. </select>
  149. </td>
  150. </tr>
  151. </table>
  152.  
  153. <input type="hidden" name="serialcount" value="#count#">
  154.  <!--- Adds Delete to every ticket  --->
  155. <table class="zpExpandedTable" id="resoltable" cellpadding="1" cellspacing="0">
  156. <tr>
  157. <td>
  158. <input type="button" class="removeticket" value="Remove Serial #count#" onclick="removeElement('dynamic#count#Input')">
  159. </td>
  160. </tr>
  161. <tr>
  162. <td>
  163. <input type="button" class="addticket" value="Add Parts" onclick=
  164. "addpartInput('dynamic#count#Input')">
  165. </td>
  166. </tr>
  167. </table>
  168. </div>
  169. </cfoutput>
  170. <input type="hidden" value="<cfoutput>#count#</cfoutput>" name="theValue" id="theValue" />
  171. </cfif>
partsdisplay.cfm
Expand|Select|Wrap|Line Numbers
  1. <!---IF HAVE ANY BLANKS IN ANY FIELDS THIS MAKES IT SO THE FIELD WILL SHOW UP--->
  2.  
  3. <cfif parts.recordcount is 0>  
  4. <cfset count1 = 0>
  5. <cfoutput>
  6. <cfset count1 = count1 + 1>
  7. <div id="part#count1#Name#count#">
  8. <!---THIS IS THE PARTS SECTION--->
  9. <table class="zpExpandedTable" id="resoltable" cellpadding="1" cellspacing="0">
  10. <th class="sectiontitle" colspan="7">Parts Information #count1# Serial Information #count#</th>
  11. <tr>
  12. <td class='indent' id='formfieldpadding'>HC P/N:&nbsp;&nbsp;&nbsp;<input type='text' name="hcpn_#count1#_#count#" style="margin:0px" value=""></td>
  13. <td>
  14. Parts been returned*
  15. <input type="checkbox" name="partsreturn_#count1#_#count#" value=""<cfif #part_returned# eq "1">checked=yes</cfif>/>
  16. </td>
  17. <td>
  18. <td class="indent">Defective<input type="checkbox" name="defective_#count1#_#count#" value=""<cfif #defective# eq "1">checked=yes</cfif>/></td>
  19. </td>
  20. </tr>
  21. </table>
  22. <input type="hidden" name="partscount#count#" id="partscount#count#" value="#count1#">
  23.  
  24. <!--- Shows what was previously entered for RMA Data Only  --->
  25. <table class="zpExpandedTable" id="resoltable" cellpadding="1" cellspacing="0">
  26. <tr>
  27. <td id="paddingformultitop">
  28. RMA Data Only:&nbsp;&nbsp;&nbsp;&nbsp;
  29. </td>
  30. <td class="rmanmoveinmulti">
  31. ( You may enter up to 1000 characters. )
  32. <br/>
  33. <textarea maxlength="1000" onkeyup="return rmaismaxlength(this)" onkeydown="return rmaismaxlength(this)" rows="4" cols="60" name="rma_#count1#_#count#" ></textarea>
  34. </td>
  35. </tr>
  36. </table>
  37.  
  38.  
  39. <!--- Adds Delete to every ticket  --->
  40. <table class="zpExpandedTable" id="resoltable" cellpadding="1" cellspacing="0">
  41. <tr>
  42. <td>
  43. <input type="button" class="removeticket" value="Remove Parts #count1#" 
  44. onclick="removetheElement('part#count1#Name#count#')"></a>
  45. </td>
  46. </td>
  47. </tr>
  48. </table>
  49. </div>
  50. </cfoutput>
  51. <input type="hidden" value="<cfoutput>#count#</cfoutput>" name="theValue" id="theValue" />
  52. </cfif>
Thank you :),
Rach
Nov 20 '08 #342
acoder
16,027 Expert Mod 8TB
Well i guess i need to do things your way. Because when i tried to delete for some reason it deleted everything that was written for the description field but it didn't delete the record.But how would i go about creating the delete cascade?
See your thread in the SQL Server forum.
Nov 20 '08 #343
bonneylake
769 512MB
Hey Acoder,

Let me make sure i understand.Basically i need to do open a new query an do the following

Expand|Select|Wrap|Line Numbers
  1. alter table dbo.tbl_CS_notes_descr
  2. add constraint thefkserial FOREIGN KEY (fk_serialNo,fk_ticketNo) REFERENCES dbo.tbl_CS_serial (pka_serialNo,pkb_fk_ticketNo) on delete cascade
and then for my stored procedure do something like

Expand|Select|Wrap|Line Numbers
  1.  set ANSI_NULLS ON
  2.  set QUOTED_IDENTIFIER ON
  3. go
  4.  
  5.  
  6.  
  7.  -- =============================================
  8.  -- Author:        <Author,,Name>
  9. -- Create date: <Create Date,,>
  10.  -- Description:    <Description,,>
  11.  -- =============================================
  12. CREATE PROCEDURE [dbo].[usp_CS_Deletenotesdescription]
  13.   -- Add the parameters for the stored procedure here
  14.       (@fk_serialNo nvarchar(50),
  15.     @fk_ticketNo numeric(18,0))
  16.  
  17.  delete from dbo.tbl_CS_notes_descr where (fk_serialNo = @fk_serialNo and fk_ticketNo = @fk_ticketNo)
  18.  
would this be right?

Thank you,
Rach
Nov 20 '08 #344
acoder
16,027 Expert Mod 8TB
Then you wouldn't need a delete description stored procedure. When you delete a serial, the corresponding description will automatically be deleted. Try it.
Nov 20 '08 #345
bonneylake
769 512MB
Then you wouldn't need a delete description stored procedure. When you delete a serial, the corresponding description will automatically be deleted. Try it.
Hey Acoder,

I don't think its going to work because it gave me the following error when i tried the new query.An can't mess with any of the sizes of the fields. I tried even deleteing every record i had an redoing it so i know its referring to the field itself.

Msg 1753, Level 16, State 0, Line 1
Column 'dbo.tbl_CS_serial.pka_serialNo' is not the same length as referencing column 'tbl_CS_notes_descr.fk_serialNo' in foreign key 'thefkserial'. Columns participating in a foreign key relationship must be defined with the same length.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

how could i make it work the other way because i tried putting it in the following

Expand|Select|Wrap|Line Numbers
  1. <cfset serialList = ArrayToList(serialcheck, ",")>
  2. <!---when inserting/updating for serial's table is done it then delete serials not being updated/inserted or was to be deleted on purpose--->
  3. <cfloop query="countserials">
  4. <CFSET serialnum = #pka_serialNo#>
  5. <!---if the serial is not found in the list it begins deleteing--->
  6. <cfif not listFind(serialList,serialnum)>
  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. <cfquery name="deletedescription" datasource="CustomerSupport">
  13. exec usp_CS_Deletenotesdescription
  14. <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  15. '#Form.pk_ticketID#'
  16. </cfquery>
  17. </cfif>
  18. </cfloop>
and used the stored procedure below but it didn't delete the record just what was written in the description field
Expand|Select|Wrap|Line Numbers
  1. set ANSI_NULLS ON
  2. set QUOTED_IDENTIFIER ON
  3. go
  4.  
  5.  
  6.  
  7.  
  8. -- =============================================
  9. -- Author:        <Author,,Name>
  10. -- Create date: <Create Date,,>
  11. -- Description:    <Description,,>
  12.  -- =============================================
  13. ALTER PROCEDURE [dbo].[usp_CS_Deletenotesdescription]
  14. -- Add the parameters for the stored procedure here
  15. (@fk_serialNo nvarchar(100),
  16. @fk_ticketNo nvarchar(100)) as
  17.  
  18. delete from dbo.tbl_CS_notes_descr where (fk_serialNo = @fk_serialNo and fk_ticketNo = @fk_ticketNo)



Thank you,
Rach
Nov 20 '08 #346
acoder
16,027 Expert Mod 8TB
An can't mess with any of the sizes of the fields.
Why not? If you don't, you'll get inconsistency if serial number exceeds the length in the other table.

but it didn't delete the record just what was written in the description field
What do you mean by this?
Nov 20 '08 #347
bonneylake
769 512MB
Why not? If you don't, you'll get inconsistency if serial number exceeds the length in the other table.

What do you mean by this?
Hey Acoder,

Well reason i can't mess with it is because the other programmer came up with it not i .so for now basically suppose to leave it alone for now. Now i do know when the time comes then all have to mess with it again but for now not suppose to touch it and leave it be.

An for the description. I have no troubled inserting a description. But when i go to delete instead of deleteing the whole record it deletes just what is in the description field

so if i have this

pk_Num
996

fk_serialno
test2

fk_ticketno
1

descriptoin
the description

when i go to delete i end up with the following

pk_Num
996

fk_serialno
test2

fk_ticketno
1

description

so basically the description field value gets deleted but not the record.

Thank you,
Rach
Nov 20 '08 #348
acoder
16,027 Expert Mod 8TB
Try the query/stored procedure in the SQL Query Analyzer. Do you get the same result?
Nov 20 '08 #349
bonneylake
769 512MB
Try the query/stored procedure in the SQL Query Analyzer. Do you get the same result?
Hey Acoder,

This is just really really weird. I tried the test you had an well it wouldn't delete the record test (with serialNo having the word test) but it would delete if i used a number instead.

But then i decided to try insterting 2 records one being

serial no: 1 fk_ticketNo: 7 description: d1
serial no: t2 fk_ticketNo: 7 description: d2

when i deleted the serialno: 1 this is what happened in the table

serial no: t2 fk_ticketNo: 7 description: d2
serial no: t2 fk_ticketNo: 7 description:

the only thing i can think of is in the loop at the beginning i have it insert (not update). An then at the end i delete.So i am not sure if i need to make the description an update/insert or what.here is what i have

Expand|Select|Wrap|Line Numbers
  1. <CFIF REQUEST_METHOD EQ "POST">
  2.  
  3. <!---counts how many serials are there--->
  4.  <cfquery name="countserials" datasource="CustomerSupport">
  5. SELECT pka_serialNo from dbo.tbl_CS_serial where pkb_fk_ticketNo=<cfqueryparam value="#Form.pk_ticketID#">
  6.  </cfquery>
  7.  <cfset currSerialNo = 1>
  8.  
  9. <!--- the array gets serials that have been inserted and updated to avoid deleteing the ones that need to be inserted or updated--->
  10.  <cfset serialcheck = ArrayNew(1)>
  11. <!--- first loop inserts/update serials--->
  12.  <CFLOOP list="#form.serialcount#" index="machineCount">
  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. <!---the array checks to see what serials are there--->
  25. <CFSET ArrayAppend(serialcheck, "#Form["serialnum_" & machineCount]#")> 
  26. <!--- counts how many parts are available--->
  27.  <cfquery name="countparts" datasource="CustomerSupport">
  28. SELECT pk_partID from dbo.tbl_CS_parts where fk_serialNo=<cfqueryparam value="#serialnum#"> and fk_ticketNo=<cfqueryparam value="#Form.pk_ticketID#">
  29.  </cfquery>
  30. <!--- the array gets parts that have been inserted and updated to avoid deleteing the ones that need to be inserted or updated--->
  31. <cfset partcheck = ArrayNew(1)>
  32.  
  33.  
  34.  <!--- inserts information into the serial table--->
  35.  <cfquery name="serialinsertupdate" datasource="CustomerSupport">
  36.     exec usp_CS_Updateinsertserial 
  37.      <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  38.      "#Form.pk_ticketID#",
  39.      <cfqueryparam value="#modelno#" CFSQLType = "CF_SQL_VARCHAR">,
  40.       <cfqueryparam value="#producttype#" CFSQLType = "CF_SQL_VARCHAR">,
  41.      <cfqueryparam value="#softhardware#" CFSQLType = "CF_SQL_VARCHAR">,
  42.      <cfqueryparam value="#resolution#" CFSQLType = "CF_SQL_VARCHAR">,
  43.      <cfqueryparam value="#resdate#">,
  44.      <cfqueryparam value="#resvertified#" CFSQLType = "CF_SQL_VARCHAR">,
  45.      <cfqueryparam value="#vertifidate#">,
  46.     <cfqueryparam value="#deptvendor#" CFSQLType = "CF_SQL_VARCHAR">,
  47.      <cfqueryparam value="#hardwarefailure#" CFSQLType = "CF_SQL_VARCHAR">   
  48.      </cfquery>
  49.  
  50. THIS INSERTS INTO DESCRIPTION TABLE
  51.  <!---Inserts information into notes_descr table.--->
  52.  <cfquery name="description" datasource="CustomerSupport">
  53.     exec usp_CS_Insertdescription
  54.    <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  55.    '#Form.pk_ticketID#',
  56.     <cfqueryparam value="#thedescription#" CFSQLType = "CF_SQL_VARCHAR">,
  57.    '#Form.fk_addedBy#'
  58.  </cfquery>
  59.  
  60.   <!---Inserts  information into parts table.--->
  61.   <!---because it is a bit we don't use 'ticks' around defective for parts table--->
  62.  <cfparam name="form.partscount#machinecount#" default="">
  63.  <cfloop list="#form['partscount' & machinecount]#" index="ps">
  64.  <!--- the pkpartID makes each part added unique, this allows us to delete one part instead of all parts associated with a serial--->
  65.   <cfparam name="Form.pkpartID_#ps#_#machinecount#" default="">
  66.  <cfparam name="Form.hcpn_#ps#_#machinecount#" default="">
  67.  <cfparam name="Form.defective_#ps#_#machinecount#" default="0">
  68.  <cfparam name="Form.partsreturn_#ps#_#machinecount#" default="0">
  69.  <cfparam name="Form.rma_#ps#_#machinecount#" default="">
  70. <!--- the pkpartID makes each part added unique, this allows us to delete one part instead of all parts associated with a serial--->
  71.  <CFSET pkpartID           = Form["pkpartID_" & "#ps#" & "_#machinecount#"]>
  72.  <CFSET hcpn            = Form["hcpn_" & "#ps#" & "_#machinecount#"]>
  73.  <CFSET partsreturn     = Form["partsreturn_" & "#ps#" & "_#machinecount#"]>
  74.  <CFSET defective       = Form["defective_" & "#ps#" & "_#machinecount#"]>
  75.  <CFSET rma             = Form["rma_" & "#ps#" & "_#machineCount#"]>
  76. <!---the array checks to see what parts are there--->
  77. <CFSET ArrayAppend(partcheck, "#Form["pkpartID_" & "#ps#" & "_#machinecount#"]#")>
  78. <!---if hcpn is not equal to blank (meaning if it doesn't have a value) it will not insert a part. However if hcpn 
  79. has a value it will insert the part into the table.--->
  80. <cfif hcpn neq "">
  81. <cfquery name="parts" datasource="CustomerSupport">
  82.    exec usp_CS_Updateinsertparts 
  83.   <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  84.    '#Form.pk_ticketID#',
  85.     <cfqueryparam value="#hcpn#" CFSQLType = "CF_SQL_VARCHAR">,
  86.     <cfqueryparam value="#partsreturn#" CFSQLType = "CF_SQL_VARCHAR">,
  87.     <cfqueryparam value="#rma#" CFSQLType = "CF_SQL_VARCHAR">,
  88.    <cfqueryparam value="#defective#" CFSQLType = "CF_SQL_BIT">
  89.  </cfquery> 
  90.  <cfelse>
  91.  </cfif>
  92. </cfloop>
  93.  
  94. <!---THIS DELETES FOR THE PARTS TABLE--->
  95. <!---setting a list for parts--->
  96. <cfset partList = ArrayToList(partcheck, ",")>
  97. <!---when inserting/updating for part's table is done it then delete parts not being updated/inserted or was to be deleted on purpose--->
  98. <cfloop query="countparts">
  99. <cfset pk_partID= #pk_partID#>
  100. <!---if the part is not found in the list it begins deleteing--->
  101. <cfif not listFind(partList,pk_partID)>
  102. <cfquery name="deleteparts" datasource="CustomerSupport">
  103. exec usp_CS_Deleteparts
  104. <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  105. '#Form.pk_ticketID#'
  106. </cfquery>
  107. </cfif>
  108. </cfloop>
  109.  
  110. </CFLOOP>
  111.  
  112. <!---THIS DELETES FOR SERIAL TABLE AND ANY PARTS ASSOCIATED WITH THE SERIAL--->
  113. <!---setting a list for serial--->
  114. <cfset serialList = ArrayToList(serialcheck, ",")>
  115. <!---when inserting/updating for serial's table is done it then delete serials not being updated/inserted or was to be deleted on purpose--->
  116. <cfloop query="countserials">
  117. <CFSET serialnum = #pka_serialNo#>
  118. <!---if the serial is not found in the list it begins deleteing--->
  119. <cfif not listFind(serialList,serialnum)>
  120. <cfquery name="deleteserialparts" datasource="CustomerSupport">
  121. exec usp_CS_Deleteserialparts   
  122. <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  123. '#Form.pk_ticketID#'
  124. </cfquery>
  125. THIS DELETES THE DESCRIPTION
  126. <cfquery name="deletedescription" datasource="CustomerSupport">
  127. exec usp_CS_Deletenotesdescription
  128. <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  129. '#Form.pk_ticketID#'
  130. </cfquery>
  131. </cfif>
  132. </cfloop>
  133.  
  134. </CFIF>

Thank you,
Rach
Nov 20 '08 #350

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
1 post views Thread by CARIGAR | last post: by
reply views Thread by harlem98 | last post: by
1 post views Thread by Geralt96 | 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.