Ambiguous Error

**helios76** · 10-27-2003, 03:49 PM

I am receiving an error:

[Macromedia][SQLServer JDBC Driver][SQLServer]Ambiguous column name 'Employee_Id'.

This is my SQL/CF code:

<cfquery name="GetEmployee" datasource="CF_CSTS_DSN" >
SELECT *
FROM CST_EmployeeTraining INNER JOIN CST_Employee ON CST_EmployeeTraining.Employee_ID=CST_Employee.Empl oyee_ID
WHERE 1=1
<cfif isdefined("Form.Employee_ID")> AND Employee_ID='#FORM.Employee_ID#'</cfif>
<html>
<head> <title>Data Request</title> </head>
<body>
<div align="center">
<img src="CSTshorthead.jpg" width="580" height="72" border="0">
</div>
<cftable query="GetEmployee" startrow="1" colspacing="2" htmltable colheaders>
<cfcol header="<b>Employee ID</b>"
width=10
text="#Form.Employee_ID#">
<---!more code follows--->

Any ideas?
Thanks!
H

**MAK** · 10-27-2003, 04:45 PM

Instead of using "Select * from " try to use dbname.columnnames with alias.

example

select db1.col1 as "Employee",
db1.col5 as "Salary",
db3.col1 as "amount"
from db1, db3 where db1.colx=db3.coly

**helios76** · 10-27-2003, 04:51 PM

Well, the reason I did the SELECT * was because I wanted any and all the information available to the user from the search page. I have 2 tables in the DB that have the information a user will need. I am open to suggestions...

H

**MAK** · 10-27-2003, 04:55 PM

if both table has same column names then you will be in trouble. You have to hardcode it. or else get column names from syscolumns and give alias name when you are retrieving it.

create table x (id int, name char(10))
create table y (id int, name char(10))

select R.name,T.name from syscolumns R,sysobjects T
where T.id=R.id and T.name in ('x','y')

**KingSexy182** · 10-27-2003, 10:36 PM

SELECT
CST_EmployeeTrainging.*,
CST_Employee.*
FROM CST_EmployeeTraining
INNER JOIN CST_Employee
ON CST_EmployeeTraining.Employee_ID=CST_Employee.Empl oyee_ID
WHERE 1=1
<cfif isdefined("Form.Employee_ID")> AND Employee_ID ='#FORM.Employee_ID#'</cfif>

***** OK, Your error would be coming from the AND Clause, you need to specify the specific table name as Employee_ID exists in both table, it doesnt know which one to use.

Solution====> You need to specify in the AND Clause either:
AND CST_Employee_ID.employee_id = ???
OR
AND CST_EmployeeTraining.employee_id = ???

**helios76** · 10-28-2003, 04:39 PM

Hey Guys,

I have successfully inserted data into and retrieved from the database. However, for some reason, after my first insertion was perfect, it now wants to put Department data into the District data _and visa versa. I'm not sure why this is happening. I did switch around the tab order of the form fields - would that have anything to do with the problem?

Also, my data retrieval works, but it calls up EVERYTHING, not just what I want to search for. Help?

Thanks!

Insert CFM ---
_____________
<html>

<head> <title>Input form</title> </head>

<body>



<cfquery name="AddEmployee" datasource="CF_CSTS_DSN" >

INSERT INTO CST_Employee

VALUES ('#Form.Employee_ID#','#Form.First_Name#', '#Form.Last_Name#', '#Form.Department#','#Form.District#')

INSERT INTO CST_EmployeeTraining

VALUES ('#Form.Training_Desc#', '#Form.Training_Instructor#', '#Form.Training_Dt#', '#Form.Training_Type#', '#Form.Training_Location#', '#Form.Employee_ID#')

</cfquery>

<div align="center">

<h1><img src="CSTshorthead.jpg" width="580" height="72" border="0"></h1>

</div>

<h1>Employee Added</h1>

<cfoutput>You have added #Form.First_Name# #Form.Last_Name# to the employee database.

</cfoutput>

</body>

</html>
____________________
DB Schema

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_CST_EmployeeTrn_CST_Employee]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[CST_EmployeeTrn] DROP CONSTRAINT FK_CST_EmployeeTrn_CST_Employee
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CST_Employee]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[CST_Employee]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CST_EmployeeTrn]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[CST_EmployeeTrn]
GO

CREATE TABLE [dbo].[CST_Employee] (
[Employee_Id] [int] NOT NULL ,
[First_Name] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Last_Name] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[District] [smallint] NOT NULL ,
[Department] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[CST_EmployeeTraining] (
[EmployeeTraining_Id] [int] IDENTITY (1, 1) NOT NULL ,
[Training_Desc] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Training_Instructor] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Training_Dt] [datetime] NOT NULL,
[Training_Type] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Training_Location] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Employee_Id] [int] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[CST_Employee] WITH NOCHECK ADD
CONSTRAINT [PK_CST_Employee] PRIMARY KEY NONCLUSTERED
(
[Employee_Id]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[CST_EmployeeTraining] WITH NOCHECK ADD
CONSTRAINT [PK_CST_EmployeeTraining] PRIMARY KEY NONCLUSTERED
(
[EmployeeTraining_Id]
) ON [PRIMARY]
GO

GRANT REFERENCES , SELECT , UPDATE , INSERT , DELETE ON [dbo].[CST_Employee] TO [FRB_Dev]
GO

GRANT SELECT ON [dbo].[CST_Employee] TO [FRB_User]
GO

GRANT REFERENCES , SELECT , UPDATE , INSERT , DELETE ON [dbo].[CST_EmployeeTraining] TO [FRB_Dev]
GO

GRANT SELECT ON [dbo].[CST_EmployeeTraining] TO [FRB_User]
GO

ALTER TABLE [dbo].[CST_EmployeeTraining] ADD
CONSTRAINT [FK_CST_EmployeeTrn_CST_Employee] FOREIGN KEY
(
[Employee_Id]
) REFERENCES [dbo].[CST_Employee] (
[Employee_Id]
)
GO

**helios76** · 10-28-2003, 04:43 PM

Request CFM


<cfquery name="GetEmployee" datasource="CF_CSTS_DSN" >
SELECT *
FROM CST_EmployeeTraining INNER JOIN CST_Employee ON CST_EmployeeTraining.Employee_ID=CST_Employee.Empl oyee_ID
WHERE 1=1
<cfif isdefined("Form.Employee_ID")> AND CST_Employee.Employee_ID='#FORM.Employee_ID#'</cfif>
<cfif isdefined("Form.First_Name")> AND CST_Employee.First_Name='#FORM.First_Name#'</cfif>
<cfif isdefined("Form.Last_Name")> AND CST_Employee. Last_Name='#FORM.Last_Name#'</cfif>
<cfif isdefined("Form.Department")> AND CST_Employee.Department='#FORM.Department#'</cfif>
<cfif isdefined("Form.District")> AND CST_Employee. District='#FORM.District#'</cfif>
<cfif isdefined("Form.Training_Desc")> AND CST_EmployeeTraining.Training_Desc='#FORM.Training _Desc#'</cfif>
<cfif isdefined("Form.Training_Instructor")> AND CST_EmployeeTraining.Training_Instructor='#FORM.Tr aining_Instructor#'</cfif>
<cfif isdefined("Form.Training_Dt")> AND CST_EmployeeTraining.Training_Dt='#FORM.Training_D t#'</cfif>
<cfif isdefined("Form.Training_Type")> AND CST_EmployeeTraining.Training_Type='#FORM.Training _Type#'</cfif>
<cfif isdefined("Form.Training_Location")> AND CST_EmployeeTraining.Training_Location='#FORM.Trai ning_Location#'</cfif>
</cfquery>
<html>
<head> <title>Data Request</title> </head>
<body>
<div align="center">
<img src="CSTshorthead.jpg" width="580" height="72" border="0">
</div>
<cftable query="GetEmployee" startrow="1" colspacing="2" htmltable colheaders>
<cfcol header="<b>Employee ID</b>"
width=10
text="#Employee_ID#">
<cfcol header="<b>First Name</b>"
width=10
text="#First_Name#">
<cfcol header="<b>Last Name</b>"
width=10
text="#Last_Name#">
<cfcol header="<b>Department</b>"
width=10
text="#Department#">
<cfcol header="<b>District</b>"
width=10
text="#District#">
<cfcol header="<b>Course Name</b>"
width=2
text="#Training_Desc#">
<cfcol header="<b>Instructor</b>"
width=10
text="#Training_Instructor#">
<cfcol header="<b>Date</b>"
width=10
text="#Training_Dt#">
<cfcol header="<b>Course Type</b>"
width=10
text="#Training_Type#">
<cfcol header="<b>Location</b>"
width=10
text="#Training_Location#"></cftable>
<div align="center">
<p><font face="Arial,Helvetica,Geneva,Swiss,SunSans-Regular">Note: Districts are labeled by number, i.e., Boston = 01, New York = 02, etc.</font></p>
</div>
</body>
</html>

**MAK** · 10-28-2003, 06:19 PM

look for the column order in syscolumns.

you have to hardcode the column names. its not the best practice to insert into tablename without column names.

In future if you add a column to the table then your page will throw error.

**helios76** · 10-28-2003, 06:59 PM

Thanks! I fixed the column order issue. And I am hard/hand coding all of this because I am working on a Mac and CF (or MS SQL 2000 AFAIK) doesn't have software for my platform.

Any ideas for my other issue about data retrieval?

H

**KingSexy182** · 10-28-2003, 11:46 PM

You are retieving every column from both tables by using 'Select *'.
To output the columns you want, i suggest u distinguish the table name and column name
e.g
--obtain single column called 'FirstName':
SELECT CST_Employee.FirstName

--obtain more than one column :
SELECT CST_Employee.FirstName, CST_Employee.Age

--obtain more than one column but from both tables:

SELECT CST_Employeed.FirstName,
CST_EmployeeTraining.TrainingDate


<cfquery name="GetEmployee" datasource="CF_CSTS_DSN" >
SELECT *
FROM CST_EmployeeTraining INNER JOIN CST_Employee ON CST_EmployeeTraining.Employee_ID=CST_Employee.Empl oyee_ID
WHERE 1=1
<cfif isdefined("Form.Employee_ID")> AND CST_Employee.Employee_ID='#FORM.Employee_ID#'</cfif>
<cfif isdefined("Form.First_Name")> AND CST_Employee.First_Name='#FORM.First_Name#'</cfif>
<cfif isdefined("Form.Last_Name")> AND CST_Employee. Last_Name='#FORM.Last_Name#'</cfif>
<cfif isdefined("Form.Department")> AND CST_Employee.Department='#FORM.Department#'</cfif>
<cfif isdefined("Form.District")> AND CST_Employee. District='#FORM.District#'</cfif>
<cfif isdefined("Form.Training_Desc")> AND CST_EmployeeTraining.Training_Desc='#FORM.Training _Desc#'</cfif>
<cfif isdefined("Form.Training_Instructor")> AND CST_EmployeeTraining.Training_Instructor='#FORM.Tr aining_Instructor#'</cfif>
<cfif isdefined("Form.Training_Dt")> AND CST_EmployeeTraining.Training_Dt='#FORM.Training_D t#'</cfif>
<cfif isdefined("Form.Training_Type")> AND CST_EmployeeTraining.Training_Type='#FORM.Training _Type#'</cfif>
<cfif isdefined("Form.Training_Location")> AND CST_EmployeeTraining.Training_Location='#FORM.Trai ning_Location#'</cfif>

**helios76** · 10-30-2003, 04:56 PM

OK - How about this? How would I create a variable to receive the form information and then send that to the SELECT so that the user gets only what they are looking for?

<cfquery name="GetEmployee" datasource="CF_CSTS_DSN" >
DECLARE @search varchar(50)
SELECT @search = '#Form.search#'
FROM CST_EmployeeTraining, CST_Employee
SELECT @search
WHERE 1=1
<cfif isdefined("Form.Employee_ID")> AND CST_Employee.Employee_ID='#FORM.Employee_ID#'</cfif>
<cfif isdefined("Form.First_Name")> AND CST_Employee.First_Name='#FORM.First_Name#'</cfif>
<---etc., etc.--->

**helios76** · 11-03-2003, 09:30 AM

One last note - I got my search to work!



<cfquery name="GetEmployee" datasource="CF_CSTS_DSN" >
SELECT *
FROM CST_EmployeeTraining INNER JOIN CST_Employee ON CST_EmployeeTraining.Employee_Id=CST_Employee.Empl oyee_Id
WHERE CST_Employee.Employee_ID LIKE '%#form.search#%'
OR CST_Employee.First_Name LIKE '%#form.search#%'
OR CST_Employee.Last_Name LIKE '%#form.search#%'
OR CST_Employee.District LIKE '%#form.search#%'
OR CST_Employee.Department LIKE '%#form.search#%'
OR CST_EmployeeTraining.Training_Desc LIKE '%#form.search#%'
OR CST_EmployeeTraining.Training_Dt LIKE '%#form.search#%'
OR CST_EmployeeTraining.Training_Type LIKE '%#form.search#%'
OR CST_EmployeeTraining.Training_Location LIKE '%#form.search#%'
</cfquery>

**KingSexy182** · 11-05-2003, 11:14 PM

It would HELP ALOT if you would WRITE THE QUERY AT FIRST AND TESTING IT WITHOUT ALL THE COLDFUSION JUNK! = P

Once you have your result set, u could start substituting the some of the field names in ur test query with actual CF code.

Most of the time when there is more than 1 coding language used, it could get real messy and UGLY that PEOPLE DO ACTUALLY MAKE FUNDAMENTAL MISTAKES AND FORGET THE BASICS.

**helios76** · 11-06-2003, 07:03 AM

Since this was my VERY FIRST TIME doing anything with SQL/CF I had NO CLUE what I was doing. I will keep your suggestion in mind for my next problem.

**KingSexy182** · 11-06-2003, 05:35 PM

Np.

I find it really helpful (especially when multiple joins are needed) to write up a query then once having the correct output that i want, then i would think about adding variables etc. to create a stored proc.

Thread: Ambiguous Error

Thread Tools

Display

Ambiguous Error

Posting Permissions