displaying previously entered multiple fields

[bonneylake]

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_ser ial.pka_serialN o' is not the same length as referencing column 'tbl_CS_notes_d escr.fk_serialN o' 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

<cfset serialList = ArrayToList(serialcheck, ",")>
<!---when inserting/updating for serial's table is done it then delete serials not being updated/inserted or was to be deleted on purpose--->
<cfloop query="countserials">
<CFSET serialnum = #pka_serialNo#>
<!---if the serial is not found in the list it begins deleteing--->
<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>
<cfquery name="deletedescription" datasource="CustomerSupport">
exec usp_CS_Deletenotesdescription
<cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
'#Form.pk_ticketID#'
</cfquery>
</cfif>
</cfloop>

and used the stored procedure below but it didn't delete the record just what was written in the description field

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go




-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
 -- =============================================
ALTER PROCEDURE [dbo].[usp_CS_Deletenotesdescription]
-- Add the parameters for the stored procedure here
(@fk_serialNo nvarchar(100),
@fk_ticketNo nvarchar(100)) as

delete from dbo.tbl_CS_notes_descr where (fk_serialNo = @fk_serialNo and fk_ticketNo = @fk_ticketNo)

Thank you,
Rach

[acoder]

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?

[bonneylake]

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

[acoder]

Try the query/stored procedure in the SQL Query Analyzer. Do you get the same result?

[bonneylake]

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

<CFIF REQUEST_METHOD EQ "POST">

<!---counts how many serials are there--->
 <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]>
<!---the array checks to see what serials are there--->
<CFSET ArrayAppend(serialcheck, "#Form["serialnum_" & machineCount]#")> 
<!--- counts how many parts are available--->
 <cfquery name="countparts" datasource="CustomerSupport">
SELECT pk_partID from dbo.tbl_CS_parts where fk_serialNo=<cfqueryparam value="#serialnum#"> and fk_ticketNo=<cfqueryparam value="#Form.pk_ticketID#">
 </cfquery>
<!--- the array gets parts that have been inserted and updated to avoid deleteing the ones that need to be inserted or updated--->
<cfset partcheck = ArrayNew(1)>
 

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

[B]THIS INSERTS INTO DESCRIPTION TABLE[/B]
 <!---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  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">
 <!--- the pkpartID makes each part added unique, this allows us to delete one part instead of all parts associated with a serial--->
  <cfparam name="Form.pkpartID_#ps#_#machinecount#" default="">
 <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="">
<!--- the pkpartID makes each part added unique, this allows us to delete one part instead of all parts associated with a serial--->
 <CFSET pkpartID           = Form["pkpartID_" & "#ps#" & "_#machinecount#"]>
 <CFSET hcpn            = Form["hcpn_" & "#ps#" & "_#machinecount#"]>
 <CFSET partsreturn     = Form["partsreturn_" & "#ps#" & "_#machinecount#"]>
 <CFSET defective       = Form["defective_" & "#ps#" & "_#machinecount#"]>
 <CFSET rma             = Form["rma_" & "#ps#" & "_#machineCount#"]>
<!---the array checks to see what parts are there--->
<CFSET ArrayAppend(partcheck, "#Form["pkpartID_" & "#ps#" & "_#machinecount#"]#")>
<!---if hcpn is not equal to blank (meaning if it doesn't have a value) it will not insert a part. However if hcpn 
has a value it will insert the part into the table.--->
<cfif hcpn neq "">
<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> 
 <cfelse>
 </cfif>
</cfloop>

<!---THIS DELETES FOR THE PARTS TABLE--->
<!---setting a list for parts--->
<cfset partList = ArrayToList(partcheck, ",")>
<!---when inserting/updating for part's table is done it then delete parts not being updated/inserted or was to be deleted on purpose--->
<cfloop query="countparts">
<cfset pk_partID= #pk_partID#>
<!---if the part is not found in the list it begins deleteing--->
<cfif not listFind(partList,pk_partID)>
<cfquery name="deleteparts" datasource="CustomerSupport">
exec usp_CS_Deleteparts
<cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
'#Form.pk_ticketID#'
</cfquery>
</cfif>
</cfloop>

</CFLOOP>

<!---THIS DELETES FOR SERIAL TABLE AND ANY PARTS ASSOCIATED WITH THE SERIAL--->
<!---setting a list for serial--->
<cfset serialList = ArrayToList(serialcheck, ",")>
<!---when inserting/updating for serial's table is done it then delete serials not being updated/inserted or was to be deleted on purpose--->
<cfloop query="countserials">
<CFSET serialnum = #pka_serialNo#>
<!---if the serial is not found in the list it begins deleteing--->
<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>
[B]THIS DELETES THE DESCRIPTION[/B]
<cfquery name="deletedescription" datasource="CustomerSupport">
exec usp_CS_Deletenotesdescription
<cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
'#Form.pk_ticketID#'
</cfquery>
</cfif>
</cfloop>

</CFIF>

Thank you,
Rach

[acoder]

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.

That seems the most likely explanation. You need to change that stored procedure to an insert/update one like serials and parts.

Just one thing though: couldn't you have more than one description per serial? If so, you need to treat it like parts.

[bonneylake]

That seems the most likely explanation. You need to change that stored procedure to an insert/update one like serials and parts.

Just one thing though: couldn't you have more than one description per serial? If so, you need to treat it like parts.

Hey Acoder,

I realized i can't make the description an update. Theres a field in the description called fk_addedBy and basically whoever adds a description there name will be applied to the description. Well if i update it no matter if they mess with the description or not it puts the name of the person who updated the ticket last name instead of the person who actually submitted the description in the first place. So basically if i put the description as a update it will take the person who original added a description an take there name out an put who ever last updated the tickets name and apply it to each description.So i not sure how i can go around that unless you have an idea?

But yes the description can have more then one applied to it. So i am going to try an treat it like parts an see what happens.

Thank you :),
Rach

[acoder]

So basically if i put the description as a update it will take the person who original added a description an take there name out an put who ever last updated the tickets name and apply it to each description.So i not sure how i can go around that unless you have an idea?

Why not change the update query to only update the other fields and not the added by? In the part which checks if it should be an update or an insert, only add a check for the ther fields and not added-by.

[bonneylake]

Why not change the update query to only update the other fields and not the added by? In the part which checks if it should be an update or an insert, only add a check for the ther fields and not added-by.

Hey Acoder,

Are you saying that when i created my update/insert stored procedure for description. To just not to add fk_addedBy? Or are you saying to keep fk_addedBy out of the update, but not out of the insert part of the stored procedure? If your talking about either i tried the following but although its not updateing the fk_addedBy its still updateing the description so if i change the description it changes as well instead of adding a new one here is what i have.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go



-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[usp_CS_Updatedescription]
-- Add the parameters for the stored procedure here
         (@fk_serialNo nvarchar(50),
    @fk_ticketNo numeric(18,0),
    @description nvarchar(1000),
    @fk_addedBy nvarchar(100))
AS
BEGIN
       --- SET should be the first statement .. before everything else
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @pk_num int

    
    -- If @fk_custNum already exists, get the pk_contactID
    SELECT @pk_num = ( SELECT MAX(pk_num) FROM dbo.tbl_CS_notes_descr WHERE fk_serialNo = @fk_serialNo and  fk_ticketNo=@fk_ticketNo)

    -- If  @fk_custNum already exists in the table, update it
    IF @pk_num IS NOT NULL
    BEGIN

            UPDATE    dbo.tbl_CS_notes_descr
            SET       
                     fk_ticketNo=@fk_ticketNo,
                     description=@description
                     

                    
                    
            WHERE    (pk_num = @pk_num and fk_serialNo = @fk_serialNo and  fk_ticketNo=@fk_ticketNo)

    END
    -- the @fk_custNum does NOT exist. insert a new record
    ELSE
    BEGIN

           
        -- Insert statements for procedure here
insert into dbo.tbl_CS_notes_descr
    (pk_num,fk_serialNo,fk_ticketNo,description,fk_addedBy)

    values
    (@pk_num,@fk_serialNo,@fk_ticketNo,@description,@fk_addedBy)
    END


    SET NOCOUNT OFF
END

here is what i got for description to be treated like parts is but i am unsure of where the sections for descriptions goes.

<CFIF REQUEST_METHOD EQ "POST">

<!---counts how many serials are there--->
 <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]>
<!---the array checks to see what serials are there--->
<CFSET ArrayAppend(serialcheck, "#Form["serialnum_" & machineCount]#")> 
<!--- counts how many parts are available--->
 <cfquery name="countparts" datasource="CustomerSupport">
SELECT pk_partID from dbo.tbl_CS_parts where fk_serialNo=<cfqueryparam value="#serialnum#"> and fk_ticketNo=<cfqueryparam value="#Form.pk_ticketID#">
 </cfquery>
<!--- the array gets parts that have been inserted and updated to avoid deleteing the ones that need to be inserted or updated--->
<cfset partcheck = ArrayNew(1)>

[B]DESCRIPTION[/B]
 <!--- counts how many descriptions are available--->
 <cfquery name="countdescription" datasource="CustomerSupport">
SELECT pk_num from dbo.tbl_CS_notes_desc where fk_serialNo=<cfqueryparam value="#serialnum#"> and fk_ticketNo=<cfqueryparam value="#Form.pk_ticketID#">
 </cfquery>
<!--- the array gets description that have been inserted and updated to avoid deleteing the ones that need to be inserted or updated--->
<cfset desccheck = ArrayNew(1)>

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

[B]DESCRIPTION[/B]
 <!---Inserts information into notes_descr table.--->
 <CFSET thedescription  = Form["thedescription_" & machineCount]>
 <!--- the pk_num makes each description added unique, this allows us to delete one descriptoin instead of all description associated with a serial--->
 <CFSET pk_num = Form["pk_num_" & machinecount]>
 <cfif thedescription neq "">
  <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>
 </cfif>
  
  <!---Inserts  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">
 <!--- the pkpartID makes each part added unique, this allows us to delete one part instead of all parts associated with a serial--->
  <cfparam name="Form.pkpartID_#ps#_#machinecount#" default="">
 <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="">
<!--- the pkpartID makes each part added unique, this allows us to delete one part instead of all parts associated with a serial--->
 <CFSET pkpartID           = Form["pkpartID_" & "#ps#" & "_#machinecount#"]>
 <CFSET hcpn            = Form["hcpn_" & "#ps#" & "_#machinecount#"]>
 <CFSET partsreturn     = Form["partsreturn_" & "#ps#" & "_#machinecount#"]>
 <CFSET defective       = Form["defective_" & "#ps#" & "_#machinecount#"]>
 <CFSET rma             = Form["rma_" & "#ps#" & "_#machineCount#"]>
<!---the array checks to see what parts are there--->
<CFSET ArrayAppend(partcheck, "#Form["pkpartID_" & "#ps#" & "_#machinecount#"]#")>
<!---if hcpn is not equal to blank (meaning if it doesn't have a value) it will not insert a part. However if hcpn 
has a value it will insert the part into the table.--->
<cfif hcpn neq "">
<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> 
 <cfelse>
 </cfif>
</cfloop>

<!---THIS DELETES FOR THE PARTS TABLE--->
<!---setting a list for parts--->
<cfset partList = ArrayToList(partcheck, ",")>
<!---when inserting/updating for part's table is done it then delete parts not being updated/inserted or was to be deleted on purpose--->
<cfloop query="countparts">
<cfset pk_partID= #pk_partID#>
<!---if the part is not found in the list it begins deleteing--->
<cfif not listFind(partList,pk_partID)>
<cfquery name="deleteparts" datasource="CustomerSupport">
exec usp_CS_Deleteparts
<cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
'#Form.pk_ticketID#'
</cfquery>
</cfif>
</cfloop>

[B]<!---THIS DELETES FOR THE DESCRIPTION TABLE--->[/B]
<cfset descriptionList = ArrayToList(desccheck, ",")>
<!---when inserting/updating for part's table is done it then delete parts not being updated/inserted or was to be deleted on purpose--->
<cfloop query="countdescription">
<cfset pk_num= #pk_num#>
<!---if the part is not found in the list it begins deleteing--->
<cfif not listFind(descriptionList,pk_num)>
<cfquery name="deletedescription" datasource="CustomerSupport">
exec usp_CS_Deletenotesdescription
<cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
'#Form.pk_ticketID#'
</cfquery
</cfif>

</CFLOOP>

<!---THIS DELETES FOR SERIAL TABLE AND ANY PARTS ASSOCIATED WITH THE SERIAL--->
<!---setting a list for serial--->
<cfset serialList = ArrayToList(serialcheck, ",")>
<!---when inserting/updating for serial's table is done it then delete serials not being updated/inserted or was to be deleted on purpose--->
<cfloop query="countserials">
<CFSET serialnum = #pka_serialNo#>
<!---if the serial is not found in the list it begins deleteing--->
<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>
<!---<cfquery name="deletedescription" datasource="CustomerSupport">
exec usp_CS_Deletenotesdescription
<cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
'#Form.pk_ticketID#'
</cfquery>--->
</cfif>
</cfloop>

Thank you,
Rach

[acoder]

Are you saying that when i created my update/insert stored procedure for description. To just not to add fk_addedBy? Or are you saying to keep fk_addedBy out of the update, but not out of the insert part of the stored procedure? If your talking about either i tried the following but although its not updateing the fk_addedBy its still updateing the description so if i change the description it changes as well instead of adding a new one here is what i have.

Is that what you wanted?

The description should be very similar to parts, so you will need something to uniquely identify a single description.

[bonneylake]

Is that what you wanted?

The description should be very similar to parts, so you will need something to uniquely identify a single description.

Hey Acoder,

actually yes it does, i realized i forgot a few things an now its the same problem but it makes more sense just not sure on the solution. Well now the problem i have is. The way we do the other fields is we check if it already has a value and it updates it. The thing is though i don't display the description like i do all serial or parts. on the cticketpage2edi t.cfm page i display it like so.

<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">
<input type="hidden" name="pk_num_#count#" id="pk_num" value="#pk_num#" />
#description#</cfloop>

an basically unless the value is in the following below. It will a)delete the value i already had and b) will not add another value into the textarea below if i add another

<textarea maxlength='1000' onkeyup='return descriptionmaxlength(this)' onkeydown='return descriptionmaxlength(this)'rows='4' cols='60']name="thedescription_#count#"></textarea>

any ideas to go around this? i tried just doing a hidden field with the value of #description# but it didn't work to well.

Thank you,
Rach

[acoder]

Is the table name tbl_CS_notes_de scr or just notes?

So, does that mean that you can't edit a description already entered? If there are, for example, 5 descriptions already for that serial, you can't edit or delete any of them- you can only add. Is that correct?

[bonneylake]

Hey Acoder,

Yes the table name for description is tbl_CS_notes_de scr.An yes basically can't edit or delete a description already entered. Basically the description keeps up with what is going on with whatever they are working on so say for example

nov/11/08 jane
customer called complaining hard drive is not working. working on getting parts for it
nov/15/08 stefan
got the part for it working on sending it

its basically to know who to blame if something goes wrong or something does not get done. atleast that is how i think of it lol. But is there a way to just put a hidden field on there an it knows what the value is based on the hidden field?

Thank you :),
Rach

[acoder]

Now I notice that notes is the name of a Coldfusion query, not a table name.

If no description can be deleted or edited, then you only need to worry about deletions when a serial is deleted (though ideally that shouldn't even be a problem because the foreign key should be taking care of that). Use the serial numbers to delete all descriptions. Since you can't edit, there's no need for updates - just inserts will do. Is there an option to insert more than one description at any one time?

[bonneylake]

Now I notice that notes is the name of a Coldfusion query, not a table name.

If no description can be deleted or edited, then you only need to worry about deletions when a serial is deleted (though ideally that shouldn't even be a problem because the foreign key should be taking care of that). Use the serial numbers to delete all descriptions. Since you can't edit, there's no need for updates - just inserts will do. Is there an option to insert more than one description at any one time?

Hey Acoder,

The only way a person can add another description is when they go to edit. For example lets say they inserted a serial and added a description to that serial. They then go back to edit the description. They will see the description that was previously added (by clicking on another link or by me displaying it on the page). They will not be able to change the description previously enter. However, they will still see the textarea for description under the pacific serial an will be able to add another description to that particular serial. So basically they will be able to add a description that way. But besides that they can not do like we do with serial where they can add multiple serials or parts.

but basically for my description i should be deleteing like this an then does the description delete need to be in the part loop, serial loop, or on its own like we do serial delete?

<cfset descriptionList = ArrayToList(desccheck, ",")>
<!---when inserting/updating for part's table is done it then delete parts not being updated/inserted or was to be deleted on purpose--->
<cfloop query="countdescription">
<cfset pk_num= #pk_num#>
<!---if the part is not found in the list it begins deleteing--->
<cfif not listFind(descriptionList,pk_num)>
<cfquery name="deletedescription" datasource="CustomerSupport">
exec usp_CS_Deletenotesdescription
<cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
</cfquery>
</cfif>
</cfloop>

Thank you :),
Rach