displaying previously entered multiple fields

[bonneylake]

No, the stored procedures should be separate.

As for the separation, though it makes sense, it would mean that all the code for adding/deleting using JavaScript would no longer be required because the adding and deleting would take place in stages - one by one. Do pages take a long time to load? Would it be a problem?

Hey Acoder,

I don't think it would be a problem. I haven't had any loading problems at all.But if we remove the javascript then we won't be able to add multiple serials and parts now right? they still need to be able to add serials and parts and update ones that previously where submitted.

Thank you,
Rach

[acoder]

You wouldn't, but it wouldn't be required. The simplest solution from where you're at is to do it all in one go. In that case, you wouldn't even need an update because everything would be deleted and then added again.

[bonneylake]

You wouldn't, but it wouldn't be required. The simplest solution from where you're at is to do it all in one go. In that case, you wouldn't even need an update because everything would be deleted and then added again.

Hey Acoder,

I understand on the deleting previously submitted and just reinsert it. But what i am confused on, is if we go in this direction. Basically the user won't be able to add more serial and more parts to the ones they have previously correct?Because the biggest thing besides for being able to put in new changes is to be able to add more parts and more serials.

Thank you,
Rach

[acoder]

They would be able to add more serials and parts because it's assumed that everything will change, so everything is deleted first to be added with the changes. The big obvious disadvantage to this is that if you even change one single field in one single part and you have, say, 3 serials with 2 parts each, everything would be deleted and added all over again (and a side effect would be that the update takes place). If you want to avoid that, then you would need to make a separate page specially for updates and no additions/deletions would be allowed on that page.

[bonneylake]

They would be able to add more serials and parts because it's assumed that everything will change, so everything is deleted first to be added with the changes. The big obvious disadvantage to this is that if you even change one single field in one single part and you have, say, 3 serials with 2 parts each, everything would be deleted and added all over again (and a side effect would be that the update takes place). If you want to avoid that, then you would need to make a separate page specially for updates and no additions/deletions would be allowed on that page.

Hey Acoder,

Well the deleting is not a big deal. To be honest i have doubts that many changes will be made to it, except to say that this problem has been closed (but you never know). But as long as they can still add multiple serials and parts then i am ready to go. Where do we begin?

Thank you,
Rach

[acoder]

OK, the first thing you need to do is add a delete query which deletes parts and serials, so you could use two stored procedures, or you could use one cascading delete where if you delete serials, the parts are also deleted.

[bonneylake]

OK, the first thing you need to do is add a delete query which deletes parts and serials, so you could use two stored procedures, or you could use one cascading delete where if you delete serials, the parts are also deleted.

Hey Acoder,

I have already created 2 delete querys. One for parts and serials. I am not sure if this is the correct way to do a delete stored procedure. Here is what i have.

delete for serial

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


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

DELETE FROM tbl_CS_serial

WHERE (pka_serialNo=@pka_serialNo and pkb_fk_ticketNo=@pkb_fk_ticketNo)

delete for parts

 set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


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

DELETE FROM tbl_CS_parts


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

Thank you,
Rach

[acoder]

Do you use these delete queries anywhere else? If not, it might be an idea to write one which removes all parts and serials for a particular ticket in one (or possibly two) queries. Either way, this is OK too, the only difference being that you'll have to have a nested loop deleting all serials and parts one by one.

[bonneylake]

Do you use these delete queries anywhere else? If not, it might be an idea to write one which removes all parts and serials for a particular ticket in one (or possibly two) queries. Either way, this is OK too, the only difference being that you'll have to have a nested loop deleting all serials and parts one by one.

Hey Acoder,

Nope the 2 delete queries are not used anywhere else. So how would i combine the 2 into one query? it was hard to find example online of just trying to delete.

Thank you,
Rach

[acoder]

Well, it's not strictly necessary, just a performance issue should you need to go back to it.

In your action page, add a loop that loops over the serials and within that loop, loops over the parts and deletes them one by one, followed by a deletion of the serial for that loop iteration. The cfloop tags would be similar to what you have for addition.

[bonneylake]

Well, it's not strictly necessary, just a performance issue should you need to go back to it.

In your action page, add a loop that loops over the serials and within that loop, loops over the parts and deletes them one by one, followed by a deletion of the serial for that loop iteration. The cfloop tags would be similar to what you have for addition.

Hey Acoder,

Well do you have a suggestion on where i could find an example on how to delete records from 2 tables? having a hard time finding an example online.

An are you meaning something like this for the cfloop, was not sure what type of cfloop they should be so i just left them <cfloop>. but is where i place the 2 cfloop's correct?

<!---Inserts information into serial table.--->
<CFIF REQUEST_METHOD EQ "POST">
<CFSET machineListLen = listLen(Form.serialcount)>
<cfloop>
<CFLOOP from="1" to="#machineListLen#" 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_Updateserial 
     <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['part2count' & machinecount]#" index="ps">
<cfloop>
<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_Updateparts 
    <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="#defective#" CFSQLType = "CF_SQL_BIT">,
    <cfqueryparam value="#rma#" CFSQLType = "CF_SQL_VARCHAR">
</cfquery>
</cfloop>
</cfloop>
</cfloop>
</CFLOOP>
</CFIF>

Thank you,
Rach

[acoder]

You can search for "delete cascade". If you have problems, ask in the SQL Server forum.

On the other hand, if you want to stick with what you've got and work with Coldfusion, you can use a loop to delete serials and parts one by one instead. I'll correct myself and say that you only need one loop. Copy lines 3 and 5 and put them above line 1. Now you have the beginning of the loop. First you would delete the parts that have the serial id (as foreign key) and then the serial.

[bonneylake]

You can search for "delete cascade". If you have problems, ask in the SQL Server forum.

On the other hand, if you want to stick with what you've got and work with Coldfusion, you can use a loop to delete serials and parts one by one instead. I'll correct myself and say that you only need one loop. Copy lines 3 and 5 and put them above line 1. Now you have the beginning of the loop. First you would delete the parts that have the serial id (as foreign key) and then the serial.

Hey Acoder,

do i need to delete the 2 <cfloop> i had created before. here is what i have.

<!---Inserts information into serial table.--->
<CFSET machineListLen = listLen(Form.serialcount)>
<CFLOOP from="1" to="#machineListLen#" index="machineCount">
<CFIF REQUEST_METHOD EQ "POST">
<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_Updateserial 
     <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['part2count' & machinecount]#" index="ps">
<cfloop>
<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_Updateparts 
    <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="#defective#" CFSQLType = "CF_SQL_BIT">,
    <cfqueryparam value="#rma#" CFSQLType = "CF_SQL_VARCHAR">
</cfquery>
</cfloop>
</cfloop>
</cfloop>
</CFLOOP>
</CFIF>

an will this work for the delete for both serial and parts?

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


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

DELETE FROM tbl_CS_serial
WHERE (pka_serialNo=@pka_serialNo and pkb_fk_ticketNo=@pkb_fk_ticketNo)

DELETE FROM tbl_CS_parts
WHERE (fk_serialNo = @fk_serialNo and  fk_ticketNo=@fk_ticketNo)

Thank you,
Rach

[acoder]

If you're going to use one stored procedure to delete all serials and parts, then you won't need the loop code in Coldfusion.

If you're going the SQL Server route, the best solution would probably be a delete cascade, but you may want to make sure firstly that your version of SQL Server supports it and secondly ask the experts in the SQL Server forum if they have any suggestions. If that's not possible, the solution would be to delete from parts first and then from serials. You only need the ticket number.

[bonneylake]

If you're going to use one stored procedure to delete all serials and parts, then you won't need the loop code in Coldfusion.

If you're going the SQL Server route, the best solution would probably be a delete cascade, but you may want to make sure firstly that your version of SQL Server supports it and secondly ask the experts in the SQL Server forum if they have any suggestions. If that's not possible, the solution would be to delete from parts first and then from serials. You only need the ticket number.

Hey Acoder,

Well the reason i am doing the SQL server route is because you said i could run into problems later doing it the other way, which trying to avoid as many problems as possible. But i thought that was a delete cascade? it was the only example i could find online that deleted from 2 tables. Only ones i could find where ones that had delete and update or would delete from one table.Do you know of a better way to search it? i tried delete cascade, i tried sql server stored procedure delete cascade. Really hard to find an example.

Thank you,
Rach