displaying previously entered multiple fields

[bonneylake]

I think it may be caused by the fact that the delete stored procedure doesn't delete descriptions. Also, now you mention that you can have many descriptions for one serial, it changes things. The serial query uses a join query to get the description. This assumes a one-to-one relationship, but now you've got a one-to-many relationship, i.e. many descriptions to one serial. You'll have to change the query to keep serials and descriptions separate like how you have serials and parts separate. In other words, treat descriptions like parts.

Hey Acoder,

Alrighty i made the querys on the cticketpage2edi t.cfm into this

<cfquery name="serial" datasource="CustomerSupport">
		SELECT pka_serialNo,pkb_fk_ticketNo,model_no,product_type,software_hardware,
       resolution,resolution_date,verification_date,type_hardware_failure,
        dept_responsibility,resolution_verified_by
        FROM dbo.tbl_CS_serial
        WHERE pkb_fk_ticketNo = #URL.pk_ticketID# 
</cfquery>

<!---Shows what was previously entered into parts table--->
<cfquery name="parts" datasource="CustomerSupport">
       SELECT pk_partID,fk_serialNo,fk_ticketNo,hc_partNo,ltrim(rtrim(part_returned)) as        part_returned,rma_number,defective
         FROM dbo.tbl_CS_parts
         WHERE fk_ticketNo = #URL.pk_ticketID#
</cfquery>

<!---Shows what was previously entered into description table--->
<cfquery name="description" datasource="CustomerSupport">
		SELECT fk_serialNo,fk_ticketNo,description
         FROM dbo.tbl_CS_notes_descr
         WHERE fk_ticketNo = #URL.pk_ticketID#
</cfquery>

an well want to ask since if i am only inserting descriptions and going to be displaying them a different way. Unsure if i will be displaying them on the same page or a different page.but if i am displaying them on the page (i am doubting all do this) but in case i have to how would i just display it. I tried doing just this above the textarea.

<cfloop query="description">#description#</cfloop>

but i got the error of

Expression result cannot be converted to a string

Expressions used inside tags like CFOUTPUT, CFQUERY, CFMAIL, etc. must evaluate to a value that can be converted to a string for output or dynamic text accumulation purposes. Complex objects, such as queries, arrays, and COM/DCOM objects, cannot be represented as strings.


also wanted to ask would it be ok to go back to updating/inserting previously submitted serials and parts instead of deleting them? asking since inserting into serial and parts is not the problem and would be nice to be able to just update them instead. which i am sorry for making you go through all the deleting now, i wish i would of known what i do now back a week or 2 ago. i should of tested more, i should of thought about everything i had instead of just the javascript and html so i am sorry for putting you through all the extra work.

besides that i only have 2 problems left (as far as i know). For some reason when i update if i have a serial and part. When i come back to look it will not display the part anymore an only show a serial. The second problem i have is a similar problem with parts. If i add serials 1 and 3 and submit it i get the error of it asking for serial 2 an was wondering if we could make it where if a user does not submit a serial 2 it will still work ok.

Thank you,
Rach

[acoder]

You'll have to move the description outside the serial table just as the parts is separate and have its own cfoutput loop.

The reason why the deletion was added was because of the problem where you can't insert and update at the same time. If you want to avoid deletes, you're going to have separate pages for inserts and updates.

[bonneylake]

You'll have to move the description outside the serial table just as the parts is separate and have its own cfoutput loop.

The reason why the deletion was added was because of the problem where you can't insert and update at the same time. If you want to avoid deletes, you're going to have separate pages for inserts and updates.

Hey Acoder,

Well the thing is though the description would have to appear where the description does now. An only way i can think of to do it as you described it would be to start off with cfoutput serial then stop it right where description starts, do my description an then restart serial again. An i don't know if that would work still the way it does now?

An actually i am able to update and insert at the same time using a stored procedure.I never had the problem of updating and inserting at the same time, just the problem with the duplicates. But I do it for my contacts table and i had created and tested it for my serials table and i doubled checked it just now and it works just fine with no errors and displays back on the edit page like it should.here is what i am using to update and insert at the same time.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go













-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[usp_CS_Updateinsertserial]
-- Add the parameters for the stored procedure here
    (@pka_serialNo nvarchar(100),
    @pkb_fk_ticketNo nvarchar(100),
    @model_no nvarchar(50),
    @product_type nvarchar(100),
    @software_hardware nvarchar(40),
    @resolution nvarchar(500),
    @resolution_date datetime,
    @resolution_verified_by nvarchar(50),
    @verification_date datetime,
    @dept_responsibility nvarchar(50),
    @type_hardware_failure nvarchar(100))
AS

IF EXISTS(SELECT @pka_serialNo FROM dbo.tbl_CS_serial WHERE pka_serialNo=@pka_serialNo and pkb_fk_ticketNo=@pkb_fk_ticketNo)
  BEGIN
     UPDATE    dbo.tbl_CS_serial
            SET       
                      pkb_fk_ticketNo=@pkb_fk_ticketNo,
                      model_no=@model_no,
                      product_type=@product_type,
                      software_hardware=@software_hardware,
                      resolution=@resolution,
                      resolution_date=@resolution_date,
                      resolution_verified_by=@resolution_verified_by,
                      verification_date=@verification_date,
                      dept_responsibility=@dept_responsibility,
                      type_hardware_failure=@type_hardware_failure
                      
                      
            WHERE    (pka_serialNo=@pka_serialNo and pkb_fk_ticketNo=@pkb_fk_ticketNo)
  END
ELSE
  BEGIN
insert into dbo.tbl_CS_serial
    (pka_serialNo,pkb_fk_ticketNo,model_no,product_type,software_hardware,resolution,resolution_date,
     resolution_verified_by,verification_date,dept_responsibility,type_hardware_failure)

    values  
    (@pka_serialNo,@pkb_fk_ticketNo,@model_no,@product_type,@software_hardware,@resolution,@resolution_date,
     @resolution_verified_by,@verification_date,@dept_responsibility,@type_hardware_failure)  
    END

but besides that i only got the other 2 problems i mentioned above the one with the parts not showing up after making changes to edit. an also the part about the serials where if i have serials 1, 3 it will say it needs serial 2.an i have also ran into this problem once again with the parts now with the new changes to the html.

Thank you,
Rach

[acoder]

Well the thing is though the description would have to appear where the description does now. An only way i can think of to do it as you described it would be to start off with cfoutput serial then stop it right where description starts, do my description an then restart serial again. An i don't know if that would work still the way it does now?

In that case, use cfloop to output the description. Rename the description query to something else to avoid problems with Coldfusion trying to convert it to a string when you output #description#.

An actually i am able to update and insert at the same time using a stored procedure.I never had the problem of updating and inserting at the same time, just the problem with the duplicates. But I do it for my contacts table and i had created and tested it for my serials table and i doubled checked it just now and it works just fine with no errors and displays back on the edit page like it should.here is what i am using to update and insert at the same time.

I see. I didn't know you had this stored procedure. In that case, it could work without deletion.

but besides that i only got the other 2 problems i mentioned above the one with the parts not showing up after making changes to edit. an also the part about the serials where if i have serials 1, 3 it will say it needs serial 2.an i have also ran into this problem once again with the parts now with the new changes to the html.

We'll deal with these after the above two problems.

[bonneylake]

In that case, use cfloop to output the description. Rename the description query to something else to avoid problems with Coldfusion trying to convert it to a string when you output #description#.
I see. I didn't know you had this stored procedure. In that case, it could work without deletion.
We'll deal with these after the above two problems.

Hey Acoder,

Alrighty, i used the cfloop an well i am getting no errors, but its displaying what i wrote in every description for all descriptions. If i wrote for serial 1 the description test and wrote for serial 2 the description dog. When it redisplays it says testdog for both instead of serial 1 test and serial 2 dog.

an well i am working on the parts update and insert to make sure it will work with serial and parts together. i got it written. For some reason it don't want to give me the permissions to use it, but going to keep working on that.

Thank you,
Rach

[acoder]

You need to have a separate description query for each serial just as parts has a separate query for each serial. Use the serialno field to get the right description for each serial.

[bonneylake]

You need to have a separate description query for each serial just as parts has a separate query for each serial. Use the serialno field to get the right description for each serial.

Hey Acoder,

Alrighty the update/insert for serial and parts is working perfectly and i am not having any trouble with that. I also have the description working perfectly as you said to do :). Ok so how do i go about fixing it so that if i entered serial 1,3 that it will not give me an error asking for 2? i am also having this same trouble once again with parts.

Here is the full html i have

<!--- Ticket Information 
       This display the ticket Information--->
    
<!---<input type="hidden" value="0" id="theValue" />--->
    <div id="dynamicInput"> 
     <!--- All Ticket Information Appears Here--->
 



<!---<cfif serial.recordcount is 0>--->
<cfset count = 0>
<cfoutput query="serial">
<cfset count = count + 1>
<cfset model_no = #model_no#>
<cfset product_type = #product_type#>
<cfset type_hardware_failure = #type_hardware_failure#>
<cfset software_hardware = #software_hardware#>
<cfset resolution_verified_by = #resolution_verified_by#>
<cfset dept_responsibility = #dept_responsibility#>

 <!--- Shows what was previously entered for Model No, Product Type, and Type of Hardware Failure  --->

<div id="dynamic#count#Input"> 
<table class="zpExpandedTable" id="modeltable"> 
<th class="sectiontitletick" colspan="7">
Serial Information #count# </th>
<tr>
<td id="paddingformultitop">Model No:&nbsp;&nbsp;&nbsp;&nbsp;</td>
</td>
<td>
<select name="modelno_#count#">
<option value="">Make A Selection</option>
<cfloop query="models">
<option value="#model#"<cfif #model# is #model_no#>selected</cfif>>#model#</option>
</cfloop> 
</select>
</td>
<td>
&nbsp;&nbsp;&nbsp;&nbsp;Product Type:
</td>
<td>
<select name="producttype_#count#">
<option value="" selected>No Choice</option>
<cfloop query="getProdType">
<option value="#pk_productType#"<cfif #pk_productType# is #product_type#>selected</cfif>>#pk_productType#</option> 
</cfloop>
</select>
</td>
<td class="red">
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Type Of Hardware Failure*:
</td>
<td>

<select name="hardwarefailure_#count#">
<option value="" selected>Make A Selection</option>
<cfloop query="getHardwareFail">
<option value="#pk_hardwareFailure#"<cfif #pk_hardwareFailure# is #type_hardware_failure#>selected</cfif>>#pk_hardwareFailure#</option>
</cfloop>
</select>
</td>
</tr>
<table>
 
<!--- Shows what was previously entered for Serial Number and Software/Hardware  --->
<table class="zpExpandedTable" id="modeltable">
<tr>
<td id="paddingformultitop">
Serial Number:&nbsp;&nbsp;
<input type="text" name="serialnum_#count#" value="#pka_serialNo#">
&nbsp;&nbsp;&nbsp;&nbsp;Software/Hardware:&nbsp;&nbsp;
<select name="softhardware_#count#">
<option value="" selected>No Choice</option>
<cfloop query="getSoftHard">
<option value="#pk_softwareHardware#"<cfif #pk_softwareHardware# is #software_hardware#>selected</cfif>>#pk_softwareHardware#</option>
</cfloop>
</select>
</td>
</tr>
</table>

<!--- Shows what was previously entered for Description ---> 

 <cfquery name="getnotes" dbtype="query" >
SELECT *
FROM notes
WHERE fk_serialNo=<cfqueryparam value="#pka_serialNo#" 
cfsqltype="cf_sql_char" maxLength="20">
</cfquery>
<cfloop query="getnotes">#description#</cfloop>
<table class="zpExpandedTable" id="resoltable" cellpadding="3" cellspacing="0">
<tr>
<td id="paddingformutli">
Description:&nbsp;&nbsp;
</td>
<td class="descriptionmoveinmulti">

( You may enter up to 1000 characters. )
<br>
<textarea maxlength='1000' onkeyup='return descriptionmaxlength(this)' onkeydown='return descriptionmaxlength(this)'rows='4' cols='60' name="thedescription_#count#"></textarea>

</td>
</tr>
</table>
<!---Shows what was previously entered for Resolution ---> 

<table class="zpExpandedTable" id="resoltable" cellpadding="1" cellspacing="0">
<tr>
<td id="paddingformutli">
Resolution:&nbsp;&nbsp;
</td>
<td class="resolutionmoveinmulti">
( You may enter up to 1500 characters. )
<br>
<textarea  maxlength="1500" onkeyup="return resolutionismaxlength(this)" onkeydown="return resolutionismaxlength(this)" rows="4" cols="60" name="resolution_#count#">#resolution#</textarea>
</td>
</tr>
</table>
 
<!--- Shows what was previously entered for Resolution Date, Current Date (for resolution date) and resolution vertified as effective by  --->

<table class="zpExpandedTable" id="resoldatetab" cellpadding="1" cellspacing="0">
<tr>
<td id="paddingformultitop">
Resolution Date:&nbsp;(MM/DD/YYYY)&nbsp;&nbsp;
</td>
<td>
<input type="text" name="resdate_#count#" value="#DateFormat(resolution_date,'mm/dd/yyyy')#">&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;Current Date:&nbsp;&nbsp;
<input type="checkbox" name="currentdateresol_#count#" 
onClick="resdate_#count#.value=fill_date()">
</td>
<td>
Resolution Verified as effective by:&nbsp;&nbsp;
</td>
<td>
<select name="resvertified_#count#">
<option value="" selected>Make A Selection</option>
<cfloop query="gettech">
<option value="#fname# #lname#"<cfif "#fname# #lname#" is #resolution_verified_by#>
selected</cfif>>#fname# #lname#</option>
</cfloop>
</select>
</td>
</tr>
</table>
<!--- Shows what was previously entered for Vertification Date, Current Date (for vertification date)   --->
<table class="zpExpandedTable" id="resoltable" cellpadding="1" cellspacing="0">
<tr>
<td id="paddingformultitop">
Verification Date:&nbsp;(MM/DD/YYYY)&nbsp;&nbsp;
</td>
<td class="vertificationmoveinmulti">
<input type="text" name="vertifidate_#count#" value="#DateFormat(verification_date,'mm/dd/yyyy')#">&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;Current Date:&nbsp;&nbsp;
<input type="checkbox" name="currentdatevert_#count#" 
onClick="vertifidate_#count#.value=fill_date()">
</td>
</tr>
</table>
 
<!--- Shows what was previously entered for Dept/Vendor Responsibility  --->
<table class="zpExpandedTable" id="resoltable" cellpadding="1" cellspacing="0">
<tr>
<td class="red" id="paddingformultitop">
Dept/Vendor Responsibility*:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<select name="deptvendor_#count#">
<option value="" selected>Make A Selection</option>
<cfloop query="getDeptVendor">
<option value="#pk_deptVendor#"<cfif #pk_deptVendor# is #dept_responsibility#>selected</cfif>>#pk_deptVendor#</option>
</cfloop>
</select>
</td>
</tr>
</table>


<input type="hidden" name="serialcount" id="serialcount" value="#count#">
<!--- Adds Delete to every ticket  --->
<table class="zpExpandedTable" id="resoltable" cellpadding="1" cellspacing="0">
<tr>
<td>
<input type="button" class="removeticket" value="Remove Serial #count#" onclick="removeElement('dynamic#count#Input')">
</td>
</tr>
<tr>
<td>
<input type="button" class="addticket" value="Add Parts" onclick="addpartInput('dynamic#count#Input')">
</td>
</tr>
</table>


<!---Shows what was previously entered into parts table--->
<!---dbtype="query"--->
<cfquery name="getparts" dbtype="query" >
SELECT *
FROM parts
WHERE fk_serialNo=<cfqueryparam value="#pka_serialNo#" 
cfsqltype="cf_sql_char" maxLength="20">
</cfquery>
<cfset count1 = 0>
<!---<cfif serial.recordcount is 0>--->
<cfloop query="getparts">
<cfset count1 = count1 + 1>
<div id="part#count1#Name#count#">
<!---<cfset partliststr = "">
<cfloop from="1" to="#count1#" index="pl">
<cfset partliststr = partliststr & #pl# & ",">
</cfloop>

<input type="hidden" name="part2count#count#" id="part2count#count#" value="#partliststr#">
<input type="hidden" name="partscount#count#" id="partscount#count#" value="#count1#">--->

<!---THIS IS THE PARTS SECTION--->



<table class="zpExpandedTable" id="resoltable" cellpadding="1" cellspacing="0">
<th class="sectiontitle" colspan="7">Parts Information #count1# Serial Information #count#</th>
<tr>
<td class='indent' id='formfieldpadding'>HC P/N:&nbsp;&nbsp;&nbsp;<input type='text' name="hcpn_#count1#_#count#" style="margin:0px" value="#hc_partNo#"></td>
<td>
Parts been returned*
<input type="checkbox" name="partsreturn_#count1#_#count#" value="#part_returned#"<cfif #part_returned# eq "1">checked=yes</cfif>/>
</td>
<td>
<td class="indent">Defective<input type="checkbox" name="defective_#count1#_#count#" value="#defective#"<cfif #defective# eq "1">checked=yes</cfif>/></td>
</td>
</tr>
</table>
<!---<input type="hidden" name="partscount#count#" id="partscount#count#" value="#count#">--->


<!--- Shows what was previously entered for RMA Data Only  --->
<table class="zpExpandedTable" id="resoltable" cellpadding="1" cellspacing="0">
<tr>
<td id="paddingformultitop">
RMA Data Only:&nbsp;&nbsp;&nbsp;&nbsp;
</td>
<td class="rmanmoveinmulti">
( You may enter up to 1000 characters. )
<br/>
<textarea maxlength="1000" onkeyup="return rmaismaxlength(this)" onkeydown="return rmaismaxlength(this)" rows="4" cols="60" name="rma_#count1#_#count#" >#rma_number#</textarea>
</td>
</tr>
</table>


<!--- Adds Delete to every ticket  --->
<table class="zpExpandedTable" id="resoltable" cellpadding="1" cellspacing="0">
<tr>
<td>
<input type="button" class="removeticket" value="Remove Parts #count1#" 
onclick="removetheElement('part#count1#Name#count#',#count#,#count#)"></a>
</td>
</td>
</tr>
</table>
</div>

<input type="hidden" name="partscount#count#" id="partscount#count#" value="#count1#"/>
</cfloop>

</div>
<!---this one makes it so you can add a part to a previously submitted serial--->
<input type="hidden" name="partcounter#count#" id="partcounter#count#" value="#count1#" />
</cfoutput>


<input type="hidden" value="<cfoutput>#count#</cfoutput>" name="theValue" id="theValue" />



<!---If have any blanks in serial table it will make field appear--->
<!---<cfoutput query="serial">
<cfset model_no = #model_no#>
<cfset product_type = #product_type#>
<cfset type_hardware_failure = #type_hardware_failure#>
<cfset pka_serialNo = #pka_serialNo#>
<cfset software_hardware = #software_hardware#>
<cfset description = #description#>
<cfset resolution = #resolution#>
<cfset resolution_date = #resolution_date#>
<cfset resolution_verified_by = #resolution_verified_by#>
<cfset verification_date = #verification_date#>
<cfset dept_responsibility = #dept_responsibility#>
</cfoutput>
<cfif serial.recordcount is 0>
<cfset model_no = "">
<cfset product_type = "">
<cfset type_hardware_failure = "">
<cfset pka_serialNo = "">
<cfset software_hardware = "">
<cfset description = "">
<cfset resolution = "">
<cfset resolution_date = "">
<cfset resolution_verified_by = "">
<cfset verification_date = "">
<cfset dept_responsibility = "">
<cfinclude template="serialdisplay.cfm">
</cfif>--->

<!---Parts Information, display parts if have or don't have a value--->
<!---<cfoutput query="parts">
<cfset hc_partNo = #hc_partNo#>
<cfset part_returned = #part_returned#>
<cfset defective = #defective#>
<cfset rma_number = #rma_number#>
<cfinclude template="partsdisplay.cfm">
</cfoutput>
<cfif parts.recordcount is 0>
<cfset hc_partNo = "">
<cfset part_returned = "">
<cfset defective = "">
<cfset rma_number = "">
<cfinclude template="partsdisplay.cfm">
</cfif>--->

</div>

     <input type="button" class="addticket" value="Add Serial" onClick="addInput('dynamicInput');" >

Thank you :),
Rach

[acoder]

In your submit page, you have the following two lines:

<CFSET machineListLen = listLen(Form.serialcount)>
<CFLOOP from="1" to="#machineListLen#" index="machineCount">

This counts from 1 to whatever number of serials. You need to use form.serialcoun t as a list instead, e.g.

<cfloop list="#form.serialcount#" index="machineCount">

[bonneylake]

In your submit page, you have the following two lines:

<CFSET machineListLen = listLen(Form.serialcount)>
<CFLOOP from="1" to="#machineListLen#" index="machineCount">

This counts from 1 to whatever number of serials. You need to use form.serialcoun t as a list instead, e.g.

<cfloop list="#form.serialcount#" index="machineCount">

Hey Acoder,

That seemed to work perfectly for ignoring serials but still having issues with parts.I was also thinking, do i need to make this a update/insert and delete stored procedure since there will be serials/parts removed? Here is what i have for the submit

<cftransaction>
<!---Inserts information into serial table.--->
<!---<cfquery name="deleteserialparts" datasource="CustomerSupport">
exec usp_CS_Deleteserialparts '#Form.pk_ticketID#'
</cfquery>--->

<CFIF REQUEST_METHOD EQ "POST">
<!---<CFSET machineListLen = listLen(Form.serialcount)>--->
<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]>

 
<!--- 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--->
<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>
</CFIF>

Thank you,
Rach

[acoder]

That seemed to work perfectly for ignoring serials but still having issues with parts.

Did you have this issue when you were deleting?

I was also thinking, do i need to make this a update/insert and delete stored procedure since there will be serials/parts removed?

This is what I forgot about when I looked at your insert-update stored procedure. I knew I was forgetting something - a good reason why the delete was there. You could combine all three (insert/update/delete), but it would mean some extra coding to get it to work. For example, you could store the deleted serials/parts numbers in a hidden field to use in the submitted page.

[bonneylake]

Hey Acoder,

With the parts i am not having trouble deleting them. It deletes fine. Its just when i go to insert its asking. For example

serial 1
parts 1
serial 2

if i do not add a parts 2 to the above example it asks me for it. It also happens if i do not have a parts 1 for serial 1.An if i do the exact example i have above i get the error

An error occurred while evaluating the expression:


"#form['partscount' & machinecount]#"


Error near line 91, column 20.
--------------------------------------------------------------------------------

The member "PARTSCOUNT 2" in dimension 1 of object "form" cannot be found. Please, modify the member name.

an on the stored procedure not worried about the extra work. Just worried about making sure it don't delete all or delete ones that don't need to be deleted. Looking for an example online on how to add delete to the update insert i got now.

Thank you,
Rach

[acoder]

If you don't have a part, use cfparam for defaulting to an empty string and if it's empty, don't loop.

Re. the stored procedure. You can avoid any more hidden fields by looping over the serials and when you come across a missing number, that serial has to be deleted. For example, you had 1, 2, 3, then 2 was deleted and 4 was added, so you have 1, 3, 4. When looping over the serialcount, when it comes across 3, 2 is missing, so you can delete 2. You don't necessarily need to combine it with the insert-update stored procedure because you can just call a separate stored procedure that just deletes.

[bonneylake]

Hey Acoder,

I am a bit confused about the parts part an unsure how i would do that. But i was thinking it might be a bit more simple.what we had done early with parts which was part2count and partscount worked perfectly before. An we have the same exact setup almost except that one hidden field appears in serial an the other appears in parts.is it possible that for the one that appears in parts that we could do what we had done before?which is this. but instead of using part2count use partscount?

<cfset partliststr = "">
<cfloop from="1" to="#count1#" index="pl">
<cfset partliststr = partliststr & #pl# & ",">
</cfloop>

<input type="hidden" name="part2count#count#" id="part2count#count#" value="#partliststr#">

An also i think your right on the delete. When i added a part earlier an deleted it. Although it showed the part deleted in the html, in the partcounter it still displayed it as saying there was a part. For example if i added one part an partcounter value was 1. If i delete the part the value still says 1.

as with the sql query. Ok your idea sounds wonderful an was wondering how i could go about doing that? Could i still keep the same delete query i had previously and use that an just need to put a cfloop around the serial?

Thank you,
Rach

[acoder]

For the parts, if you have no parts, the partscount fields will not be set. So just use cfparam above the loop to set it to "".

An also i think your right on the delete. When i added a part earlier an deleted it. Although it showed the part deleted in the html, in the partcounter it still displayed it as saying there was a part. For example if i added one part an partcounter value was 1. If i delete the part the value still says 1.

This is correct because you want unique values. The list length of partscount will give you the number of parts anyway.

as with the sql query. Ok your idea sounds wonderful an was wondering how i could go about doing that? Could i still keep the same delete query i had previously and use that an just need to put a cfloop around the serial?

You'll have to modify it to delete one serial at a time rather than all serials for a particular ticket. Use another variable which stores the current serial number. Match it with the serial number in the loop. If it doesn't match, then delete until it matches incrementing each time.

[bonneylake]

For the parts, if you have no parts, the partscount fields will not be set. So just use cfparam above the loop to set it to "".
This is correct because you want unique values. The list length of partscount will give you the number of parts anyway.

You'll have to modify it to delete one serial at a time rather than all serials for a particular ticket. Use another variable which stores the current serial number. Match it with the serial number in the loop. If it doesn't match, then delete until it matches incrementing each time.

Hey Acoder,

So do something like this for parts?

<cfparam name="form.partscount" 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>

an then the delete for stored procedure has me a bit off. Are you saying to match the serial number with the ticket number to delete? or are you saying that in the cfloop on my userformedit.cf m page that i need to add it somewhere there to delete? here is what i have right now for the stored procedure delete

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go





-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[usp_CS_Deleteserialparts]
	-- Add the parameters for the stored procedure here
      (
      @pka_serialNo nvarchar(100)
      @pkb_fk_ticketNo nvarchar(100)) AS

DELETE FROM dbo.tbl_CS_serial where 
(pka_serialNo = @pka_serialNo and pkb_fk_ticketNo = @pkb_fk_ticketNo)

Thank you,
Rach