displaying previously entered multiple fields

[acoder]

pkb_fk_ticketNo shouldn't have been replaced:

 <cfquery name="countserials" datasource="CustomerSupport">
SELECT COUNT(*) as serialcount from dbo.tbl_CS_serial where pkb_fk_ticketNo=<cfqueryparam value="Form.pk_ticketID#">
 </cfquery>

I notice that you have serialnum in both queries, but they're not set correctly. Set serialnum to the correct value in both loops.

[bonneylake]

pkb_fk_ticketNo shouldn't have been replaced:

 <cfquery name="countserials" datasource="CustomerSupport">
SELECT COUNT(*) as serialcount from dbo.tbl_CS_serial where pkb_fk_ticketNo=<cfqueryparam value="Form.pk_ticketID#">
 </cfquery>

I notice that you have serialnum in both queries, but they're not set correctly. Set serialnum to the correct value in both loops.

Hey Acoder,

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

<input type="text" name="serialnum_#count#">

Thank you,
Rach

[acoder]

I meant in the submitted page, but now I see that the serial numbers to delete are not available anywhere. So you'll have to change the countserials query to list the serial numbers instead of counting - you'll get the count from the recordcount anyway. then use these serial numbers to delete.

[bonneylake]

I meant in the submitted page, but now I see that the serial numbers to delete are not available anywhere. So you'll have to change the countserials query to list the serial numbers instead of counting - you'll get the count from the recordcount anyway. then use these serial numbers to delete.

Hey Acoder,

Are you talking about something like this

<cfquery name="countserials" datasource="CustomerSupport">
SELECT COUNT(*) as pka_serialNo from dbo.tbl_CS_serial where pkb_fk_ticketNo=<cfqueryparam value="#Form.pk_ticketID#">
 </cfquery>

an i am still confused about changing the value in the cfloop part

<cfloop condition="currSerialNo neq machinecount">
 <cfquery name="deleteserialparts" datasource="CustomerSupport">
 exec usp_CS_Deleteserialparts   
 <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
 '#Form.pk_ticketID#'
 </cfquery>

Thank you,
Rach

[acoder]

Not quite - you need the serials:

<cfquery name="countserials" datasource="CustomerSupport">
SELECT pka_serialNo from dbo.tbl_CS_serial where pkb_fk_ticketNo=<cfqueryparam value="#Form.pk_ticketID#">
 </cfquery>

The #serialnum# variable has to be set to the correct serial number, so you need to get the serial number from this query. Are the serial numbers in the correct order - you may need to add an ORDER BY clause.

[bonneylake]

Not quite - you need the serials:

<cfquery name="countserials" datasource="CustomerSupport">
SELECT pka_serialNo from dbo.tbl_CS_serial where pkb_fk_ticketNo=<cfqueryparam value="#Form.pk_ticketID#">
 </cfquery>

The #serialnum# variable has to be set to the correct serial number, so you need to get the serial number from this query. Are the serial numbers in the correct order - you may need to add an ORDER BY clause.

Hey Acoder,

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

 <cfquery name="countserials" datasource="CustomerSupport">
SELECT pka_serialNo from dbo.tbl_CS_serial where pka_serialNo=<cfqueryparam value="#serialnum#"> and pkb_fk_ticketNo=<cfqueryparam value="#Form.pk_ticketID#">
 </cfquery>

an i am not sure if the serial numbers are in the correct order. In my sql table they are just scattered everywhere so theres no real order. An afraid if i put them in order that it will mess it up because the first serial could be serial 2 and the second serial could be 1 so kinda nervous about doing that.

Thank you,
Rach

[acoder]

The query was OK, I meant setting serialnum in the Coldfusion code in the loops.

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

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

[bonneylake]

The query was OK, I meant setting serialnum in the Coldfusion code in the loops.

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

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

Hey Acoder,

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

 <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,

to use

#Form.serialnum#

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

Thank you :),
Rach

[acoder]

If that's the only reason, then that's what we have cftransaction for. It's either all or nothing. If it hasn't inserted that last record and something happens, then nothing is deleted or affected. Even with the current approach, you'd need cftransaction if you were fearing connection problems.

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

[bonneylake]

If that's the only reason, then that's what we have cftransaction for. It's either all or nothing. If it hasn't inserted that last record and something happens, then nothing is deleted or affected. Even with the current approach, you'd need cftransaction if you were fearing connection problems.

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

Hey Acoder,

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

<CFIF REQUEST_METHOD EQ "POST">
 <!---<CFSET machineListLen = listLen(Form.serialcount)>--->
 <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>
 <CFLOOP list="#form.serialcount#" index="machineCount">
<!--- <cfif machineCount eq currSerialNo>--->
  <cfloop condition="currSerialNo neq machinecount">
  <CFSET serialnum       = Form["serialnum_" & machineCount]>
 <cfquery name="deleteserialparts" datasource="CustomerSupport">
 exec usp_CS_Deleteserialparts   
 <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
 '#Form.pk_ticketID#'
 </cfquery>
 <cfset currSerialNo = currSerialNo + 1>
 </cfloop>

  <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]>
 

 <!--- 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>
 <cfset currSerialNo = currSerialNo + 1>
</CFLOOP>
 <cfloop condition="currSerialNo neq countserials.pka_serialNo">
 <CFSET serialnum       = Form["serialnum_" & machineCount]>
<cfquery name="deleteserialparts" datasource="CustomerSupport">
exec usp_CS_Deleteserialparts   
<cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
'#Form.pk_ticketID#'
</cfquery>
 <cfset currSerialNo = currSerialNo + 1>
</cfloop>
 </CFIF>

an i tried what i have above an i got an error with the last loop saying
Error resolving parameter SERIALNUM


ColdFusion was unable to determine the value of the parameter.



Thank you,
Rach

[acoder]

Yes, it's not going to work because it doesn't exist.

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

[bonneylake]

Hey Acoder,

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

<CFIF REQUEST_METHOD EQ "POST">
 <!---<CFSET machineListLen = listLen(Form.serialcount)>--->
 <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>
<cfloop query="#countserials#">
<!--- <cfif machineCount eq currSerialNo>--->
  <cfloop condition="currSerialNo neq machinecount">
  <CFSET serialnum       = Form["serialnum_" & machineCount]>
 <cfquery name="deleteserialparts" datasource="CustomerSupport">
 exec usp_CS_Deleteserialparts   
 <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
 '#Form.pk_ticketID#'
 </cfquery>
 <cfset currSerialNo = currSerialNo + 1>
 </cfloop>

  <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]>
 

 <!--- 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>
 <cfset currSerialNo = currSerialNo + 1>
 

</CFLOOP>
<cfloop condition="currSerialNo neq countserials.pka_serialNo">
<CFSET serialnum       = Form["serialnum_" & machineCount]>
<cfquery name="deleteserialparts" datasource="CustomerSupport">
exec usp_CS_Deleteserialparts   
<cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
'#Form.pk_ticketID#'
</cfquery>
 <cfset currSerialNo = currSerialNo + 1>
</cfloop>
 </CFIF>

Thank you,
Rach

[acoder]

I'm now thinking that since you want to avoid problems if something happens in between, it would be better to insert/update, then delete.

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

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

[bonneylake]

Hey Acoder,

Alright i went back to cflooping but i was wondering where would the delete loop go? would it be the one i have at the way bottom already like so?

</cfloop>
 <cfset currSerialNo = currSerialNo + 1>
</CFLOOP>
<cfloop condition="currSerialNo neq countserials.pka_serialNo">
<CFSET serialnum       = Form["serialnum_" & machineCount]>
<cfquery name="deleteserialparts" datasource="CustomerSupport">
exec usp_CS_Deleteserialparts   
<cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
'#Form.pk_ticketID#'
</cfquery>
 <cfset currSerialNo = currSerialNo + 1>
</cfloop>
 </CFIF>

an then how would i in the delete loop check that the serial number is not in the insert-update array? but i was messing around with arrays, was looking an example online an i was wondering if you were meaning to do something like the following?

<CFIF REQUEST_METHOD EQ "POST">
 <!---<CFSET machineListLen = listLen(Form.serialcount)>--->
 <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>
 <CFLOOP list="#form.serialcount#" index="machineCount">
<!--- <cfif machineCount eq currSerialNo>--->

  <cfloop condition="currSerialNo neq machinecount">
  <CFSET serialnum       = Form["serialnum_" & machineCount]>
 <cfquery name="deleteserialparts" datasource="CustomerSupport">
 exec usp_CS_Deleteserialparts   
 <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
 '#Form.pk_ticketID#'
 </cfquery>
 <cfset currSerialNo = currSerialNo + 1>
 </cfloop>
<cfset test = ArrayNew(1)>
  <CFSET ArrayAppend(test, "serialnum       = Form["serialnum_" & machineCount]")>
  <CFSET ArrayAppend(test,"modelno         = Form["modelno_" & machineCount]")>
  <CFSET producttype     = Form["producttype_" & machineCount]>
  <CFSET softhardware    = Form["softhardware_" & machineCount]>
  <CFSET resolution      = Form["resolution_" & machineCount]>

Thank you,
Rach

[acoder]

Yes, the delete would go at the bottom.

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

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