displaying previously entered multiple fields

[acoder]

It depends on what version of SQL Server you're using. If it's 2000 and later it's very easy - just add a constraint. This article may help. If you need more help on this, I suggest you ask in the SQL Server forum. Once you have it working, you can come back to this thread.

[bonneylake]

It depends on what version of SQL Server you're using. If it's 2000 and later it's very easy - just add a constraint. This article may help. If you need more help on this, I suggest you ask in the SQL Server forum. Once you have it working, you can come back to this thread.

Hey Acoder,

I am using SQL server 2005. But i am still baffled by the example. The reason i am confused is my tables are not related, they are 2 different separate things so i am not sure how to do it if both tables are not related? but i posted a question on it here http://bytes.com/forum/showthread.ph...89#post3405289

Thank you,
Rach

[bonneylake]

Hey Acoder,

I wanted to ask if i went back to just looping the coldfusion what sort of performance issues could i run into? reason i am asking is because i am starting
to debate if the delete cascade is going to work on my 2 tables. Also, i thought about it over the weekend and a concern came up i wanted to ask you on. Although are internet is fast where i work most of the time, we do have issues that come up at least once a month where the internet will just die.No warning or anything. An i am wondering what could/would happen if someone was submitting the serials but as they went to submit an they where suppose to be took to the next page and instead of being took to the next page the internet cut out, would all the fields delete an no new ones be added, or would the delete never happen? been thinking about it over the weekend an wanted to ask because if there is the chance of loosing it i must admit that concerns me. Its ok if it remains old information an doesn't update (they can go back an update again later). But if theres no information to update then that could be a serious problem.

Thank you,
Rach

[acoder]

It depends on when it exactly happens, but there's an easy solution. Use cftransaction to make sure it all happens as one transaction.

[bonneylake]

It depends on when it exactly happens, but there's an easy solution. Use cftransaction to make sure it all happens as one transaction.

Hey Acoder,

Well everything gets inserted into the table after everything has been filled out in the entire form an the user clicks submit. So basically instead of <cfquery name = "" datasource="" i would do something like <cftransactio n name="" datasource"" to prevent the problem ?

Thank you,
Rach

[acoder]

No, the cftransaction tag would encapsulate all the cfquerys. Read the docs on the tag.

[bonneylake]

No, the cftransaction tag would encapsulate all the cfquerys. Read the docs on the tag.

Hey Acoder,

I see what your saying so something like

<cftransaction>
<cfquery name="serialinsertupdate" datasource="CustomerSupport">
   exec usp_CS_Updateserial 
the fields
     </cfquery>
</cftransaction>

an wanted to ask they had an option where you could add an action to the
cf transaction an was wondering if i needed to add one an if i do which one? an also if i have more the one cfquery on the page where it submits, should i wrap the cftransaction around all the cfquerys present or only the cfquerys i am worried about?

Thank you,
Rach

[acoder]

I think the default options should be OK. It should go around the queries you're worried about. In other words, if it were to crash/lose connection/break down during the running of the queries within cftransaction, there's nothing to worry about. It's either all of them or none.

[bonneylake]

I think the default options should be OK. It should go around the queries you're worried about. In other words, if it were to crash/lose connection/break down during the running of the queries within cftransaction, there's nothing to worry about. It's either all of them or none.

Hey Acoder,

Well i am glade my concern was only a small fix :). But alrighty so we got the stored procedure for the delete, so were do we go next?

this is the last thing i had on the user form page (before working on the stored procedure). where everything gets sent to the database after editing.

<cftransaction>
<!---Inserts information into serial table.--->

<CFIF REQUEST_METHOD EQ "POST">
<CFSET machineListLen = listLen(Form.serialcount)>
<CFLOOP from="1" to="#machineListLen#" index="machineCount">
<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>
</cftransaction>

Thank you,
Rach

[acoder]

You need to add a call to the delete stored procedure at the top within the cftransaction. I'm thinking that if this is the only place this stored procedure is used, you could modify it to delete by ticket, so you wouldn't have to call it repeatedly for all serials.

[bonneylake]

You need to add a call to the delete stored procedure at the top within the cftransaction. I'm thinking that if this is the only place this stored procedure is used, you could modify it to delete by ticket, so you wouldn't have to call it repeatedly for all serials.

Hey Acoder,

Are you saying call it by using a cfquery like so? or is there another way to call it for a delete?

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

<CFIF REQUEST_METHOD EQ "POST">

Thank you,
Rach

[acoder]

The stored proc. requires two parameters, serial no and ticket no. I was suggesting that you modify the stored procedure to delete by ticket, so it gets all serials matching that ticket no. and deletes them (and the cascade would delete all parts too). Of course, you could do this via Coldfusion by using a select query first and then looping over the results to delete each serial one by one, but doing it all in one go via a single stored procedure call would be more efficient.

[bonneylake]

The stored proc. requires two parameters, serial no and ticket no. I was suggesting that you modify the stored procedure to delete by ticket, so it gets all serials matching that ticket no. and deletes them (and the cascade would delete all parts too). Of course, you could do this via Coldfusion by using a select query first and then looping over the results to delete each serial one by one, but doing it all in one go via a single stored procedure call would be more efficient.

Hey Acoder,

So your basically saying to do something like this in the stored procedure to delete just based on ticket number?

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
      (@pkb_fk_ticketNo nvarchar(100)) AS

DELETE FROM dbo.tbl_CS_serial where pkb_fk_ticketNo = @pkb_fk_ticketNo

Thank you,
Rach

[acoder]

Well, if that works, then yes. Test it out with a few serials, i.e. add 3 serials for a particular ticket, say no. 3, with some parts too for those serials, then try this stored procedure with '3' supplied to it.

[bonneylake]

Hey Acoder,

Yep that worked perfectly :), So whats next?

Thank you,
Rach