displaying previously entered multiple fields

**acoder** · Nov 17 '08, 05:50 PM

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.

**bonneylake** · Nov 17 '08, 06:39 PM

Hey Acoder,

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

Code:

 <cfif ListFind(form.serialnum)>
 <cfelse>
</cfloop>
</CFLOOP>

<cfset myList = ArrayToList(test, ",")>
<!---when inserting/updating is done delete ones not used--->
<cfloop query="countserials">
<CFSET serialnum = #pka_serialNo#>
<cfquery name="deleteserialparts" datasource="CustomerSupport">
exec usp_CS_Deleteserialparts   
<cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
'#Form.pk_ticketID#'
</cfquery>
</cfloop>
</cfif>

here is what i have in full

Code:

<CFIF REQUEST_METHOD EQ "POST">
 
 <cfquery name="countserials" datasource="CustomerSupport">
SELECT pka_serialNo from dbo.tbl_CS_serial where pkb_fk_ticketNo=<cfqueryparam value="#Form.pk_ticketID#">
 </cfquery>
 <cfset currSerialNo = 1>
<!--- the array gets serials that have been inserted and updated to avoid deleteing the ones that need to be inserted or updated--->
 <cfset test = ArrayNew(1)>
<!--- first loop inserts/update serials--->
 <CFLOOP list="#form.serialcount#" index="machineCount">
<CFSET serialnum       = Form["serialnum_" & machineCount]>
<CFSET modelno         = Form["modelno_" & machineCount]>
<CFSET producttype     = Form["producttype_" & machineCount]>
<CFSET softhardware    = Form["softhardware_" & machineCount]>
<CFSET resolution      = Form["resolution_" & machineCount]>
<CFSET resdate         = Form["resdate_" & machineCount]>
<CFSET resvertified    = Form["resvertified_" & machineCount]>
<CFSET vertifidate     = Form["vertifidate_" & machineCount]>
<CFSET deptvendor      = Form["deptvendor_" & machinecount]>
<CFSET hardwarefailure = Form["hardwarefailure_" & machineCount]>
<CFSET thedescription  = Form["thedescription_" & machineCount]>
<CFSET ArrayAppend(test, "#Form["serialnum_" & machineCount]#")> 


 <!--- inserts information into the serial table--->
 <cfquery name="serialinsertupdate" datasource="CustomerSupport">
    exec usp_CS_Updateinsertserial 
     <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
     "#Form.pk_ticketID#",
     <cfqueryparam value="#modelno#" CFSQLType = "CF_SQL_VARCHAR">,
      <cfqueryparam value="#producttype#" CFSQLType = "CF_SQL_VARCHAR">,
     <cfqueryparam value="#softhardware#" CFSQLType = "CF_SQL_VARCHAR">,
     <cfqueryparam value="#resolution#" CFSQLType = "CF_SQL_VARCHAR">,
     <cfqueryparam value="#resdate#">,
     <cfqueryparam value="#resvertified#" CFSQLType = "CF_SQL_VARCHAR">,
     <cfqueryparam value="#vertifidate#">,
    <cfqueryparam value="#deptvendor#" CFSQLType = "CF_SQL_VARCHAR">,
     <cfqueryparam value="#hardwarefailure#" CFSQLType = "CF_SQL_VARCHAR">   
     </cfquery>

 <!---Inserts information into notes_descr table.--->
 <cfquery name="description" datasource="CustomerSupport">
    exec usp_CS_Insertdescription
   <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
   '#Form.pk_ticketID#',
    <cfqueryparam value="#thedescription#" CFSQLType = "CF_SQL_VARCHAR">,
   '#Form.fk_addedBy#'
 </cfquery>
  
  <!---Inserts parts information into parts table.--->
  <!---because it is a bit we don't use 'ticks' around defective for parts table--->
 <cfparam name="form.partscount#machinecount#" default="">
 <cfloop list="#form['partscount' & machinecount]#" index="ps">
<cfparam name="Form.defective_#ps#_#machinecount#" default="0">
  <cfparam name="Form.partsreturn_#ps#_#machinecount#" default="0">
 <CFSET hcpn            = Form["hcpn_" & "#ps#" & "_#machinecount#"]>
 <CFSET partsreturn     = Form["partsreturn_" & "#ps#" & "_#machinecount#"]>
 <CFSET defective       = Form["defective_" & "#ps#" & "_#machinecount#"]>
 <CFSET rma             = Form["rma_" & "#ps#" & "_#machineCount#"]>
<cfquery name="parts" datasource="CustomerSupport">
   exec usp_CS_Updateinsertparts 
  <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
   '#Form.pk_ticketID#',
    <cfqueryparam value="#hcpn#" CFSQLType = "CF_SQL_VARCHAR">,
    <cfqueryparam value="#partsreturn#" CFSQLType = "CF_SQL_VARCHAR">,
    <cfqueryparam value="#rma#" CFSQLType = "CF_SQL_VARCHAR">,
   <cfqueryparam value="#defective#" CFSQLType = "CF_SQL_BIT">
 </cfquery> 
 
 <cfif ListFind(form.serialnum)>
 <cfelse>
</cfloop>
</CFLOOP>


<cfset myList = ArrayToList(test, ",")>
<!---when inserting/updating is done delete ones not used--->
<cfloop query="countserials">
<CFSET serialnum = #pka_serialNo#>
<cfquery name="deleteserialparts" datasource="CustomerSupport">
exec usp_CS_Deleteserialparts   
<cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
'#Form.pk_ticketID#'
</cfquery>
</cfloop>
</cfif>
 </CFIF>

an thank you for explaining what each part does. that helps a lot because been feeling a bit lost

Thank you :),
Rach

**acoder** · Nov 17 '08, 06:43 PM

The listfind syntax is incorrect. It requires two parameters. Also, as I mentioned earlier, it needs to go in the last (delete) loop.

**bonneylake** · Nov 17 '08, 06:47 PM

Originally posted by acoder

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?

Code:

</cfloop>
</CFLOOP>


<cfset myList = ArrayToList(test, ",")>
<!---when inserting/updating is done delete ones not used--->
<cfloop query="countserials">
<cfif ListFind(test,form.serialnum)>
 <cfelse>
<CFSET serialnum = #pka_serialNo#>
<cfquery name="deleteserialparts" datasource="CustomerSupport">
exec usp_CS_Deleteserialparts   
<cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
'#Form.pk_ticketID#'
</cfquery>
</cfloop>
</cfif>
 </CFIF>

Thank you,
Rach

**acoder** · Nov 17 '08, 06:53 PM

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.

**bonneylake** · Nov 17 '08, 06:59 PM

Hey Acoder,

So heres the whole thing is this correct?

Code:

<CFIF REQUEST_METHOD EQ "POST">
 
 <cfquery name="countserials" datasource="CustomerSupport">
SELECT pka_serialNo from dbo.tbl_CS_serial where pkb_fk_ticketNo=<cfqueryparam value="#Form.pk_ticketID#">
 </cfquery>
 <cfset currSerialNo = 1>
<!--- the array gets serials that have been inserted and updated to avoid deleteing the ones that need to be inserted or updated--->
 <cfset serialcheck = ArrayNew(1)>
<!--- first loop inserts/update serials--->
 <CFLOOP list="#form.serialcount#" index="machineCount">
<CFSET serialnum       = Form["serialnum_" & machineCount]>
<CFSET modelno         = Form["modelno_" & machineCount]>
<CFSET producttype     = Form["producttype_" & machineCount]>
<CFSET softhardware    = Form["softhardware_" & machineCount]>
<CFSET resolution      = Form["resolution_" & machineCount]>
<CFSET resdate         = Form["resdate_" & machineCount]>
<CFSET resvertified    = Form["resvertified_" & machineCount]>
<CFSET vertifidate     = Form["vertifidate_" & machineCount]>
<CFSET deptvendor      = Form["deptvendor_" & machinecount]>
<CFSET hardwarefailure = Form["hardwarefailure_" & machineCount]>
<CFSET thedescription  = Form["thedescription_" & machineCount]>
<CFSET ArrayAppend(serialcheck, "#Form["serialnum_" & machineCount]#")> 


 <!--- inserts information into the serial table--->
 <cfquery name="serialinsertupdate" datasource="CustomerSupport">
    exec usp_CS_Updateinsertserial 
     <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
     "#Form.pk_ticketID#",
     <cfqueryparam value="#modelno#" CFSQLType = "CF_SQL_VARCHAR">,
      <cfqueryparam value="#producttype#" CFSQLType = "CF_SQL_VARCHAR">,
     <cfqueryparam value="#softhardware#" CFSQLType = "CF_SQL_VARCHAR">,
     <cfqueryparam value="#resolution#" CFSQLType = "CF_SQL_VARCHAR">,
     <cfqueryparam value="#resdate#">,
     <cfqueryparam value="#resvertified#" CFSQLType = "CF_SQL_VARCHAR">,
     <cfqueryparam value="#vertifidate#">,
    <cfqueryparam value="#deptvendor#" CFSQLType = "CF_SQL_VARCHAR">,
     <cfqueryparam value="#hardwarefailure#" CFSQLType = "CF_SQL_VARCHAR">   
     </cfquery>

 <!---Inserts information into notes_descr table.--->
 <cfquery name="description" datasource="CustomerSupport">
    exec usp_CS_Insertdescription
   <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
   '#Form.pk_ticketID#',
    <cfqueryparam value="#thedescription#" CFSQLType = "CF_SQL_VARCHAR">,
   '#Form.fk_addedBy#'
 </cfquery>
  
  <!---Inserts parts information into parts table.--->
  <!---because it is a bit we don't use 'ticks' around defective for parts table--->
 <cfparam name="form.partscount#machinecount#" default="">
 <cfloop list="#form['partscount' & machinecount]#" index="ps">
<cfparam name="Form.defective_#ps#_#machinecount#" default="0">
  <cfparam name="Form.partsreturn_#ps#_#machinecount#" default="0">
 <CFSET hcpn            = Form["hcpn_" & "#ps#" & "_#machinecount#"]>
 <CFSET partsreturn     = Form["partsreturn_" & "#ps#" & "_#machinecount#"]>
 <CFSET defective       = Form["defective_" & "#ps#" & "_#machinecount#"]>
 <CFSET rma             = Form["rma_" & "#ps#" & "_#machineCount#"]>
<cfquery name="parts" datasource="CustomerSupport">
   exec usp_CS_Updateinsertparts 
  <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
   '#Form.pk_ticketID#',
    <cfqueryparam value="#hcpn#" CFSQLType = "CF_SQL_VARCHAR">,
    <cfqueryparam value="#partsreturn#" CFSQLType = "CF_SQL_VARCHAR">,
    <cfqueryparam value="#rma#" CFSQLType = "CF_SQL_VARCHAR">,
   <cfqueryparam value="#defective#" CFSQLType = "CF_SQL_BIT">
 </cfquery> 
 
 
</cfloop>
</CFLOOP>


<cfset serialList = ArrayToList(serialcheck, ",")>
<!---when inserting/updating is done delete ones not used--->
<cfloop query="countserials">
<CFSET serialnum = #pka_serialNo#>
<cfif ListFind(serialList,form.serialnum)>
<cfif not listFind(serialList,form.serialnum)>
<cfquery name="deleteserialparts" datasource="CustomerSupport">
exec usp_CS_Deleteserialparts   
<cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
'#Form.pk_ticketID#'
</cfquery>
</cfif>
</cfloop>
 </CFIF>

Thank you :),
Rach

**acoder** · Nov 17 '08, 07:21 PM

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 :)

**bonneylake** · Nov 17 '08, 07:38 PM

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

Code:

<CFIF REQUEST_METHOD EQ "POST">
 
 <cfquery name="countserials" datasource="CustomerSupport">
SELECT pka_serialNo from dbo.tbl_CS_serial where pkb_fk_ticketNo=<cfqueryparam value="#Form.pk_ticketID#">
 </cfquery>
 <cfset currSerialNo = 1>
<!--- the array gets serials that have been inserted and updated to avoid deleteing the ones that need to be inserted or updated--->
 <cfset serialcheck = ArrayNew(1)>
<!--- first loop inserts/update serials--->
 <CFLOOP list="#form.serialcount#" index="machineCount">
<CFSET serialnum       = Form["serialnum_" & machineCount]>
<CFSET modelno         = Form["modelno_" & machineCount]>
<CFSET producttype     = Form["producttype_" & machineCount]>
<CFSET softhardware    = Form["softhardware_" & machineCount]>
<CFSET resolution      = Form["resolution_" & machineCount]>
<CFSET resdate         = Form["resdate_" & machineCount]>
<CFSET resvertified    = Form["resvertified_" & machineCount]>
<CFSET vertifidate     = Form["vertifidate_" & machineCount]>
<CFSET deptvendor      = Form["deptvendor_" & machinecount]>
<CFSET hardwarefailure = Form["hardwarefailure_" & machineCount]>
<CFSET thedescription  = Form["thedescription_" & machineCount]>
<CFSET ArrayAppend(serialcheck, "#Form["serialnum_" & machineCount]#")> 


 <!--- inserts information into the serial table--->
 <cfquery name="serialinsertupdate" datasource="CustomerSupport">
    exec usp_CS_Updateinsertserial 
     <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
     "#Form.pk_ticketID#",
     <cfqueryparam value="#modelno#" CFSQLType = "CF_SQL_VARCHAR">,
      <cfqueryparam value="#producttype#" CFSQLType = "CF_SQL_VARCHAR">,
     <cfqueryparam value="#softhardware#" CFSQLType = "CF_SQL_VARCHAR">,
     <cfqueryparam value="#resolution#" CFSQLType = "CF_SQL_VARCHAR">,
     <cfqueryparam value="#resdate#">,
     <cfqueryparam value="#resvertified#" CFSQLType = "CF_SQL_VARCHAR">,
     <cfqueryparam value="#vertifidate#">,
    <cfqueryparam value="#deptvendor#" CFSQLType = "CF_SQL_VARCHAR">,
     <cfqueryparam value="#hardwarefailure#" CFSQLType = "CF_SQL_VARCHAR">   
     </cfquery>

 <!---Inserts information into notes_descr table.--->
 <cfquery name="description" datasource="CustomerSupport">
    exec usp_CS_Insertdescription
   <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
   '#Form.pk_ticketID#',
    <cfqueryparam value="#thedescription#" CFSQLType = "CF_SQL_VARCHAR">,
   '#Form.fk_addedBy#'
 </cfquery>
  
  <!---Inserts parts information into parts table.--->
  <!---because it is a bit we don't use 'ticks' around defective for parts table--->
 <cfparam name="form.partscount#machinecount#" default="">
 <cfloop list="#form['partscount' & machinecount]#" index="ps">
<cfparam name="Form.defective_#ps#_#machinecount#" default="0">
  <cfparam name="Form.partsreturn_#ps#_#machinecount#" default="0">
 <CFSET hcpn            = Form["hcpn_" & "#ps#" & "_#machinecount#"]>
 <CFSET partsreturn     = Form["partsreturn_" & "#ps#" & "_#machinecount#"]>
 <CFSET defective       = Form["defective_" & "#ps#" & "_#machinecount#"]>
 <CFSET rma             = Form["rma_" & "#ps#" & "_#machineCount#"]>
<cfquery name="parts" datasource="CustomerSupport">
   exec usp_CS_Updateinsertparts 
  <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
   '#Form.pk_ticketID#',
    <cfqueryparam value="#hcpn#" CFSQLType = "CF_SQL_VARCHAR">,
    <cfqueryparam value="#partsreturn#" CFSQLType = "CF_SQL_VARCHAR">,
    <cfqueryparam value="#rma#" CFSQLType = "CF_SQL_VARCHAR">,
   <cfqueryparam value="#defective#" CFSQLType = "CF_SQL_BIT">
 </cfquery> 
 
 
</cfloop>
</CFLOOP>


<cfset serialList = ArrayToList(serialcheck, ",")>
<!---when inserting/updating is done delete ones not used--->
<cfloop query="countserials">
<CFSET serialnum = #pka_serialNo#>
<!---<cfif ListFind(serialList,form.serialnum)>--->
<cfif not listFind(serialList,serialnum)>
<cfquery name="deleteserialparts" datasource="CustomerSupport">
exec usp_CS_Deleteserialparts   
<cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
'#Form.pk_ticketID#'
</cfquery>
</cfif>
</cfloop>
 </CFIF>

Thank you :),
Rach

**acoder** · Nov 17 '08, 10:01 PM

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?

**bonneylake** · Nov 17 '08, 10:07 PM

Originally posted by acoder

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

**acoder** · Nov 17 '08, 10:22 PM

What's the exact error message and on what line?

**bonneylake** · Nov 17 '08, 10:27 PM

Originally posted by acoder

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

Code:

 <cfloop list="#form['partscount' & machinecount]#" index="ps">
<cfparam name="Form.defective_#ps#_#machinecount#" default="0">
  <cfparam name="Form.partsreturn_#ps#_#machinecount#" default="0">
 <CFSET hcpn            = Form["hcpn_" & "#ps#" & "_#machinecount#"]>
 <CFSET partsreturn     = Form["partsreturn_" & "#ps#" & "_#machinecount#"]>
 <CFSET defective       = Form["defective_" & "#ps#" & "_#machinecount#"]>
 <CFSET rma             = Form["rma_" & "#ps#" & "_#machineCount#"]>
<cfquery name="parts" datasource="CustomerSupport">
   exec usp_CS_Updateinsertparts 
  <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
   '#Form.pk_ticketID#',
    <cfqueryparam value="#hcpn#" CFSQLType = "CF_SQL_VARCHAR">,
    <cfqueryparam value="#partsreturn#" CFSQLType = "CF_SQL_VARCHAR">,
    <cfqueryparam value="#rma#" CFSQLType = "CF_SQL_VARCHAR">,
   <cfqueryparam value="#defective#" CFSQLType = "CF_SQL_BIT">
 </cfquery> 
</cfloop>

Thank you,
Rach

**acoder** · Nov 17 '08, 10:42 PM

Add cfparams for all the fields, not just defective and partsreturn.

**bonneylake** · Nov 17 '08, 10:47 PM

Originally posted by acoder

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

Code:

<cfloop list="#form['partscount' & machinecount]#" index="ps">
<cfparam name="Form.hcpn_#ps#_#machinecount#" default="">
<cfparam name="Form.defective_#ps#_#machinecount#" default="0">
<cfparam name="Form.partsreturn_#ps#_#machinecount#" default="0">
<cfparam name="Form.rma_#ps#_#machinecount#" default="">
<CFSET hcpn            = Form["hcpn_" & "#ps#" & "_#machinecount#"]>
<CFSET partsreturn     = Form["partsreturn_" & "#ps#" & "_#machinecount#"]>
<CFSET defective       = Form["defective_" & "#ps#" & "_#machinecount#"]>
<CFSET rma             = Form["rma_" & "#ps#" & "_#machineCount#"]>
<cfquery name="parts" datasource="CustomerSupport">
  exec usp_CS_Insertparts 
 <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
    '#Form.ID#',
     <cfqueryparam value="#hcpn#" CFSQLType = "CF_SQL_VARCHAR">,
   <cfqueryparam value="#partsreturn#" CFSQLType = "CF_SQL_VARCHAR">,
   <cfqueryparam value="#defective#" CFSQLType = "CF_SQL_BIT">,
    <cfqueryparam value="#rma#" CFSQLType = "CF_SQL_VARCHAR">
</cfquery>
 </cfloop>

Thank you,
Rach

**acoder** · Nov 18 '08, 08:23 AM

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.

displaying previously entered multiple fields

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment