displaying previously entered multiple fields

**acoder** · Oct 17 '08, 09:00 AM

Does the query for parts only get the parts for the current serial? Show the code for the cfquery.

**bonneylake** · Oct 17 '08, 01:01 PM

Originally posted by acoder

Does the query for parts only get the parts for the current serial? Show the code for the cfquery.

Hey Acoder,

well the parts is getting the correct information for that ticket an so is the serial. Its just repeating the results instead of the correct part going under the correct serial. But here is the cfquery

for serial

Code:

<!---Shows what was previously entered into serial and description table--->
<cfquery name="serial" datasource="CustomerSupport">
		SELECT pka_serialNo,pkb_fk_ticketNo,model_no,product_type,software_hardware,
       resolution,resolution_date,verification_date,rma_data,type_hardware_failure,
        dept_responsibility,resolution_verified_by,fk_serialNo,fk_ticketNo,description
        FROM dbo.tbl_CS_serial
        LEFT JOIN dbo.tbl_CS_notes_descr
        ON dbo.tbl_CS_serial.pkb_fk_ticketNo=dbo.tbl_CS_notes_descr.fk_ticketNo and
        dbo.tbl_CS_serial.pka_serialNo=dbo.tbl_CS_notes_descr.fk_serialNo
        WHERE pkb_fk_ticketNo = #URL.pk_ticketID# and fk_ticketNo = #URL.pk_ticketID#
        ORDER BY pkb_fk_ticketNo
</cfquery>

for parts

Code:

<!---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,defective,submission
         FROM dbo.tbl_CS_parts
         WHERE fk_ticketNo = #URL.pk_ticketID#
</cfquery>

Thank you,
Rach

**acoder** · Oct 17 '08, 01:40 PM

You don't want all the parts at once. You want parts for each serial separately. To avoid creating a separate query for each, get all the parts and then use a query of queries to get parts for each serial separately.

**bonneylake** · Oct 17 '08, 01:41 PM

Originally posted by acoder

You don't want all the parts at once. You want parts for each serial separately. To avoid creating a separate query for each, get all the parts and then use a query of queries to get parts for each serial separately.

Hey Acoder,

Well how would create a query of queries to get the parts?

Thank you,
Rach

**acoder** · Oct 17 '08, 01:54 PM

Use the current query and then instead of querying the database, you query the query. To do that, set the dbtype attribute of cfquery to "query". See Query of Queries

**bonneylake** · Oct 17 '08, 02:01 PM

Originally posted by acoder

Use the current query and then instead of querying the database, you query the query. To do that, set the dbtype attribute of cfquery to "query". See Query of Queries

Hey Acoder,

Is this all i need to do to it. I read over your link but i am still a bit confused.

Code:

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

Thank you,
Rach

**acoder** · Oct 17 '08, 02:41 PM

Well, to be honest, you don't necessarily need a query of queries. However, if you're looking at performance benefits and efficiency, it makes sense. We can carry on down this route, but if you feel that you just want to get it working without looking at some new type of coding, then we can look at the inefficient solution.

Your query of queries should query the parts query, not the database and you can just select * because you already have only the fields that you need.

**bonneylake** · Oct 17 '08, 02:47 PM

Originally posted by acoder

Well, to be honest, you don't necessarily need a query of queries. However, if you're looking at performance benefits and efficiency, it makes sense. We can carry on down this route, but if you feel that you just want to get it working without looking at some new type of coding, then we can look at the inefficient solution.

Your query of queries should query the parts query, not the database and you can just select * because you already have only the fields that you need.

Hey Acoder,

Well the faster the better so i guess we can stick with what we are doing. But here is what i have now. I looked at the example better an think i understand a bit more. The one part i am confused on is what would i put for cfqueryparam. Would i put what i had before which is the pk_ticketID?

Code:

<cfquery name="parts" dbtype="query" >
		SELECT *
         FROM dbo.tbl_CS_parts
         WHERE fk_ticketNo <cfqueryparam value="#LastNameSearch#" 
cfsqltype="cf_sql_char" maxLength="20">
</cfquery>

Thank you,
Rach

**acoder** · Oct 17 '08, 03:35 PM

Replace lastnamesearch with serial number (the field name from the serial query).

**bonneylake** · Oct 17 '08, 03:46 PM

Originally posted by acoder

Replace lastnamesearch with serial number (the field name from the serial query).

Hey Acoder,

Do i replace the lastnamesearch with the serial number field from the serial table or the serial number field from the parts table? both tables have a serial field and both contain the same serial number. Right now i am using the serial field from parts table.

Code:

<cfquery name="parts" dbtype="query" >
SELECT *
FROM dbo.tbl_CS_parts
WHERE fk_ticketNo <cfqueryparam value="#fk_serialNo#" 
cfsqltype="cf_sql_char" maxLength="20">
</cfquery>

Thank you,
Rach

**acoder** · Oct 17 '08, 04:56 PM

It should be the field from the serial query. Also change the FROM clause to the name of the query that selects all parts. If it's parts, don't use the same name to name this particular query.

**bonneylake** · Oct 17 '08, 05:36 PM

Originally posted by acoder

It should be the field from the serial query. Also change the FROM clause to the name of the query that selects all parts. If it's parts, don't use the same name to name this particular query.

Hey Acoder,

Here is what i did, not sure if i did it right.

Code:

<cfquery name="getparts" dbtype="query" >
SELECT *
FROM dbo.tbl_CS_parts
WHERE fk_ticketNo <cfqueryparam value="#pka_serialNo#" 
cfsqltype="cf_sql_char" maxLength="20">
</cfquery>

Thank you,
Rach

**acoder** · Oct 17 '08, 05:46 PM

It should query "parts" (name of the first query), not dbo... and shouldn't the field being checked in the WHERE clause be fk_serialno? You also forgot an =.

**bonneylake** · Oct 17 '08, 06:02 PM

Originally posted by acoder

It should query "parts" (name of the first query), not dbo... and shouldn't the field being checked in the WHERE clause be fk_serialno? You also forgot an =.

Hey Acoder,

Yes you are right it should be fk_serialNo. An i been thinking the whole we only needed the one parts my bad. Ok so the whole thing should be the following

Code:

<!---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,defective,submission
         FROM dbo.tbl_CS_parts
         WHERE fk_ticketNo = #URL.pk_ticketID#
</cfquery>

<cfquery name="getparts" dbtype="query" >
SELECT *
FROM dbo.tbl_CS_parts
WHERE fk_serialNo=<cfqueryparam value="#pka_serialNo#" 
cfsqltype="cf_sql_char" maxLength="20">
</cfquery>

Thank you,
Rach

displaying previously entered multiple fields

displaying previously entered multiple fields

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment