Hey Everyone,
i been working on trying to understand the URL method of retrieving information for the last week, but i am stuck. I been able to get one table of information, but now i need to get 3 tables of information at the same time an i am running into trouble. Each table has a number in common with the other, each has a field that holds the number (but fields have different name). For example ticketMaster table has the field pk_ticketID which holds the number 1, serial has the field pkb_fk_ticketNo which holds the number 1, and parts has the field fk_ticketNo which holds the number 1. Right now i can get the correct table information for ticketMaster but for the serial an parts i get all the records that are in those tables instead of just the one record needed, which in this case is the one record that holds the number 1.
Right now here is what i have -
<!---Shows what was previously entered into table ticketmaster--->
-
<cfquery name="ticket" datasource="CustomerSupport">
-
SELECT pk_ticketID,title,priority,status,
-
cost_center,fk_customer_number,
-
customer_company,customer_Fname,customer_Lname,
-
customer_add1,customer_city,customer_state,
-
customer_zip,customer_email,customer_pri_phone,
-
customer_sec_phone,customer_notes,htpp FROM dbo.tbl_CS_ticketMaster
-
WHERE pk_ticketID = #URL.pk_ticketID#
-
</cfquery>
-
-
<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,resoluti on_verified_by FROM dbo.tbl_CS_serial
-
</cfquery>
-
-
<cfquery name="parts" datasource="CustomerSupport">
-
SELECT pk_partID,fk_serialNo,fk_ticketNo,hc_partNo,
-
part_returned,defective,submission
-
FROM dbo.tbl_CS_parts
-
</cfquery>
-
-
<form name="page1" id="page1" action="saveticket1edit.cfm?<cfoutput query="ticket">pk_ticketID=#pk_ticketID#</cfoutput><cfoutput query="serial">&pkb_fk_ticketNo=#pkb_fk_ticketNo#</cfoutput><cfoutput query="parts">&fk_ticketNo=#fk_ticketNo#</cfoutput>"
-
method="POST" onSubmit="return validate_form();">
-
</form>
if anyone could explain to me how to get the url method to work with 3 fields in 3 different tables i would really appreciate it.
Thank you in advance,
Rach
107 8578 acoder 16,027
Recognized Expert Moderator MVP
You could combine all three into one query and perform a join operation. The best way would depend on the database and the dialect of SQL used, but a very simple way would be something like: - select ...
-
from ticket, serial, parts
-
where serial.fk_id = ticket.id and parts.fk_id = ticket.id
-
and ticket.id = #url...#
You match the foreign keys with the primary key of the main table to avoid duplicates.
You could combine all three into one query and perform a join operation. The best way would depend on the database and the dialect of SQL used, but a very simple way would be something like: - select ...
-
from ticket, serial, parts
-
where serial.fk_id = ticket.id and parts.fk_id = ticket.id
-
and ticket.id = #url...#
You match the foreign keys with the primary key of the main table to avoid duplicates.
Hey Acoder,
Well here is what i have, but its still not getting the right information, it still getting all the rows in the tables for serial and parts - <!---Shows what was previously entered into table ticketmaster--->
-
<cfquery name="ticket" datasource="CustomerSupport">
-
SELECT pk_ticketID,title,priority,status,cost_center,fk_customer_number,
-
customer_company,customer_Fname,customer_Lname,customer_add1
-
,customer_city,customer_state,
-
customer_zip,customer_email,customer_pri_phone,
-
customer_sec_phone,customer_notes,htpp FROM dbo.tbl_CS_ticketMaster
-
WHERE pk_ticketID = #URL.pk_ticketID#
-
</cfquery>
-
-
<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 FROM dbo.tbl_CS_serial
-
-
</cfquery>
-
-
<cfquery name="parts" datasource="CustomerSupport">
-
SELECT pk_partID,fk_serialNo,fk_ticketNo,hc_partNo,part_returned,defective,submission
-
FROM dbo.tbl_CS_parts
-
</cfquery>
-
-
-
<cfquery name="all" datasource="CustomerSupport">
-
SELECT *
-
FROM dbo.tbl_CS_ticketMaster,dbo.tbl_CS_serial,dbo.tbl_CS_parts
-
WHERE pkb_fk_ticketNo = pk_ticketID and pk_partID = pk_ticketID
-
and pk_ticketID = #URL.pk_ticketID#
-
</cfquery>
-
-
<cfoutput query="all">
-
<form name="page1" id="page1" action="saveticket1edit.cfm?pk_ticketID=#pk_ticketID#"
-
method="POST" onSubmit="return validate_form();">
-
</cfoutput>
-
</form>
-
-
-
Thank you,
Rach
acoder 16,027
Recognized Expert Moderator MVP
What's the relationship between serial/parts and ticket. Is it many-to-one, i.e. many serials and parts to one ticket?
What's the relationship between serial/parts and ticket. Is it many-to-one, i.e. many serials and parts to one ticket?
Ok everytime someone inserts a ticket. ticket has one record (always), parts has one record (always) and serial can have multiple records. An there all suppose to share the same ticket like pkb_fk_ticketNo (for serial) has the same number as fk_serialNo (for parts) and pk_ticketID(for ticket). Hope that makes since, kinda hard to explain.
Thank you,
Rach
Hey Acoder,
Yes its many serials and parts to one ticket. They all share the same number in each table like if pk_ticketID (from ticket table) has the number 1, then pkb_fk_ticketNo (from serial table) has the number 1, and fk_ticketNo(fro m parts table) has the number 1. I figured out i had the wrong field for parts. But even changing it didn't seem to help. But here is the statement again -
<cfquery name="all" datasource="CustomerSupport">
-
SELECT *
-
FROM dbo.tbl_CS_ticketMaster,dbo.tbl_CS_serial,dbo.tbl_CS_parts
-
-
WHERE pkb_fk_ticketNo = pk_ticketID and fk_ticketNo = pk_ticketID
-
and pk_ticketID = #URL.pk_ticketID#
-
</cfquery>
-
Thank you again for all your help,
Rach
acoder 16,027
Recognized Expert Moderator MVP
To make things easier, you could use 3 queries as you had earlier.
As an example: - <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,resoluti on_verified_by FROM dbo.tbl_CS_serial
-
WHERE pkb_fk_ticketno = #url.pk_ticketID#
-
</cfquery>
Then you can get the serial and parts data separately.
To make things easier, you could use 3 queries as you had earlier.
As an example: - <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,resoluti on_verified_by FROM dbo.tbl_CS_serial
-
WHERE pkb_fk_ticketno = #url.pk_ticketID#
-
</cfquery>
Then you can get the serial and parts data separately.
Hey Acoder,
Thought i understood what you meant, but i am still having trouble.It still wont only get the serial and part associated with the ticket Here is what i have. - <cfquery name="ticket" datasource="CustomerSupport">
-
SELECT pk_ticketID,title,priority,status,cost_center,fk_customer_number,
-
customer_company,customer_Fname,customer_Lname,customer_add1,
-
customer_city,customer_state,
-
customer_zip,customer_email,customer_pri_phone,customer_sec_phone,
-
customer_notes,htpp FROM dbo.tbl_CS_ticketMaster
-
WHERE pk_ticketID = #URL.pk_ticketID#
-
</cfquery>
-
-
<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 FROM dbo.tbl_CS_serial
-
WHERE pkb_fk_ticketNo = #URL.pk_ticketID#
-
</cfquery>
-
-
<cfquery name="parts" datasource="CustomerSupport">
-
SELECT pk_partID,fk_serialNo,fk_ticketNo,hc_partNo,part_returned,defective
-
,submission
-
FROM dbo.tbl_CS_parts
-
WHERE fk_ticketNo = #URL.pk_ticketID#
-
</cfquery>
-
-
<cfoutput>
-
<form name="page1" id="page1" action="saveticket1edit.cfm?pk_ticketID=#pk_ticketID#"
-
method="POST" onSubmit="return validate_form();">
-
</cfoutput>
Any suggestions?
Thank you for all the help :),
Rach
acoder 16,027
Recognized Expert Moderator MVP
Where's the code for outputting the data?
Where's the code for outputting the data?
Hey Acoder,
Not sure what you mean, bit confused. i don't know if this is what you mean but here is all i have
page 1 - <!---Shows what was previously entered into table ticketmaster--->
-
<cfquery name="ticket" datasource="CustomerSupport">
-
SELECT pk_ticketID,title,priority,status,cost_center,fk_customer_number,
-
customer_company,customer_Fname,customer_Lname,customer_add1,customer_city,customer_state,
-
customer_zip,customer_email,customer_pri_phone,customer_sec_phone,customer_notes,htpp FROM dbo.tbl_CS_ticketMaster
-
WHERE pk_ticketID = #URL.pk_ticketID#
-
</cfquery>
-
-
<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 FROM dbo.tbl_CS_serial
-
WHERE pkb_fk_ticketNo = #URL.pk_ticketID#
-
</cfquery>
-
-
<cfquery name="parts" datasource="CustomerSupport">
-
SELECT pk_partID,fk_serialNo,fk_ticketNo,hc_partNo,part_returned,defective,submission
-
FROM dbo.tbl_CS_parts
-
WHERE fk_ticketNo = #URL.pk_ticketID#
-
</cfquery>
-
-
<cfoutput>
-
<form name="page1" id="page1" action="saveticket1edit.cfm?pk_ticketID=#pk_ticketID#"
-
method="POST" onSubmit="return validate_form();">
-
</cfoutput>
an heres whats on page 2 - <cfquery name="ticket" datasource="CustomerSupport">
-
SELECT pk_ticketID,title,priority,status,cost_center,followup_date,fk_customer_number,
-
customer_company,customer_Fname,customer_Lname,customer_add1,customer_city,customer_state,
-
customer_zip,customer_email,customer_pri_phone,customer_sec_phone,customer_notes,onsite_flag,number_onsite,htpp FROM dbo.tbl_CS_ticketMaster
-
WHERE pk_ticketID = #URL.pk_ticketID#
-
</cfquery>
-
-
<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 FROM dbo.tbl_CS_serial
-
</cfquery>
-
-
<cfquery name="parts" datasource="CustomerSupport">
-
SELECT pk_partID,fk_serialNo,fk_ticketNo,hc_partNo,part_returned,defective,submission
-
FROM dbo.tbl_CS_parts
-
-
-
</cfquery>
Thank you,
Rach
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: _link98 |
last post by:
Running DB2 ESE V8.1.8 on WinXP.
This is Fixpak 8.
Have a structured-type and some methods for that type.
One of my methods needs to do insert / update on tables.
The type specification includes "LANGUAGE SQL...CONTAINS SQL".
But I get SQL0374N "The MODIFIES SQL DATA clause has not been
specified for the CREATE FUNCTION statement for LANGUAGE function..."
|
by: PJ |
last post by:
Hello,
I am trying to use late binding to call a COM object.
I am trying to call a 'GetTables' method on the object.
It's essentially a 'MetadataService' which is used to
return the names of tables which are in a specific
database.
the first parameter to GetTables is "in only" (it's a
|
by: Michael |
last post by:
I'm using windows forms!
>-----Original Message-----
>Are you using ASP.NET or Windows Form? Only Windows
Forms
>DataGrid support that method.
>
>Tu-Thach
>
>>-----Original Message-----
|
by: mivey4 |
last post by:
Okay,
The problem is that I have an Access Database that has the following
fields:
EmpID - Autonumber (PrimaryKey)
AccountID - Text
IssueReported - Memo
DateOpened - Date/Time
TimeOpened - Date/Time
|
by: Fred Flintstone |
last post by:
What's the difference between these two methods?
1 - Parameterrized SQL queries:
Dim CommandObject As New Data.SqlClient.SqlCommand
With CommandObject
.Connection = myConnection
.Parameters.Clear()
.Parameters.Add("@TextField", SqlDbType.NVarChar,
50).Value = TextField
| |
by: tony |
last post by:
Hello!!
Hello Victor!
I use a product called flygrid to create grid tables.
In many of my forms I create such grid tables.
Some columns in these grid tables is of type drop down list where I can
select a value from the list.
Below I have some code which exist in a file named StringClass.cs
|
by: MurdockSE |
last post by:
Greetings.
My Situation:
// I have an .xml file that I am reading into a dataset in the
following code -
DataSet ds = new DataSet("MyDataset");
ds.ReadXml(@"c:\data\"+cmyxmlfilename);
|
by: rn5a |
last post by:
The .NET 2.0 documentation states the following:
When using a DataSet or DataTable in conjunction with a DataAdapter & a
relational data source, use the Delete method of the DataRow to remove
the row. The Delete method marks the row as Deleted in the DataSet or
DataTable but does not remove it. Instead when the DataAdapter
encounters a row marked as Deleted, it executes its DeleteCommand
method to delete the row at the data source. The...
|
by: leviwatts |
last post by:
Exception Details: System.ArgumentException: DataTable already belongs to another DataSet.
Googling for this error shows several post of people trying to manipulate a table within a dataset. Others suggest using .clone() and .copy(). Neither results in a different error.
Within another class, I've gathered two tables of data for a nested repeater. To return a table, int, string or anything else in C#, one sets their local variable equal...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |