RE: stored procedure - Ambiguous column name
Try renaming your stored proc input parameter CategoryJobDescrID to something else so it doesn't have the same name as a database column. --- Greg Jordan <[EMAIL PROTECTED]> wrote: = I-Lin Kuo Macromedia CF5 Advanced Developer Sun Certified Java 2 Programmer __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/ __ Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: stored procedure - Ambiguous column name
Greg,
Only thing I can see that isn't going to help is : dbvarname="@orderyby" in
one of your tags
Are you sure you're sure that attributes.orderby isn't being changed before
you get to the SP call? Mind you ORDER BY .. isn't going to help your
SELECT statement.
Regards
Stephen
- Original Message -
From: "Greg Jordan" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Thursday, April 18, 2002 4:29 PM
Subject: RE: stored procedure - Ambiguous column name
> the default orderby is dateTimeEntered desc. here is cfml. thanks again!
>
>
>
>
>
>
>
>
>
>
> value="#attributes.keywords#" null="No">
>
> null="Yes">
>
>
> value="#attributes.locationID#" null="No">
>
> null="Yes">
>
>
> dbvarname="@categoryJobDescrID" value="#attributes.categoryJobDescrID#"
> null="No">
>
> dbvarname="@categoryJobDescrID" null="Yes">
>
> value="#attributes.page#" null="No">
> value="#attributes.size#" null="No">
> value="#URLDecode(attributes.orderby)#" null="No">
> dbvarname="@count" null="Yes">
>
>
>
> -Original Message-
> From: Stephen Moretti [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, April 18, 2002 10:18 AM
> To: CF-Talk
> Subject: Re: stored procedure - Ambiguous column name
>
>
> Greg,
>
> categoryJobDescrID is in both you categoryJobDescr and categoryJobListing
> table.
>
> The procedure looks ok, so I can only assume that you are passing through
> categoryJobDescrID as one of the fields in @orderby without a tablename
> prefix.
>
> Regards
>
> Stephen
> - Original Message -
> From: "Greg Jordan" <[EMAIL PROTECTED]>
> To: "CF-Talk" <[EMAIL PROTECTED]>
> Sent: Thursday, April 18, 2002 3:41 PM
> Subject: stored procedure - Ambiguous column name
>
>
> > i've been trying to work this out for about a week and i am stumped. i
> keep
> > getting an error related to an ambiguous column name. I've checked the
sp
> > over and over (with my admittedly sub-par sql skills) and cannot find
> > anything wrong. i've included the error and the sp below. any help is
> > appreciated very much. thanks
> >
> > ***error message***
> >
> > Error occured at: 04/17/02 16:05:24
> >
> > Browser: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; .NET CLR
> > 1.0.3705)
> >
> > ODBC Error Code = 37000 (Syntax error or access violation)
> > [Microsoft][ODBC SQL Server Driver][SQL Server]Ambiguous column name
> > 'categoryJobDescrID'.
> >
> >
> > SQL = "sp_jobs_select"
> >
> > Data Source = " ** "
> >
> > ***error message***
> >
> >
> >
> > ***stored procedure***
> >
> >
> >
> > CREATE PROCEDURE [yourconsultant].[sp_jobs_select] @keyword
varchar(100),
> > @locationID varchar(500), @categoryJobDescrID varchar(500), @page int,
> @size
> > int, @orderby varchar(100), @count int output
> >
> > AS
> >
> > DECLARE @sql nvarchar(2000), @where nvarchar(1000)
> >
> > SET @WHERE='0=0'
> >
> > IF @keyword IS NOT NULL
> >
> > BEGIN
> > SET @WHERE = @WHERE + ' AND jobs.jobName LIKE ' +'''' + @keyword + '%''
OR
> > jobs.jobDescription LIKE ' +'''' + @keyword + '%'''
> > END
> >
> > IF @locationID IS NOT NULL
> >
> > BEGIN
> > SET @WHERE = @WHERE + ' AND locationJob.locationID IN (' + @locationID +
> ')'
> > END
> >
> > IF @categoryJobDescrID IS NOT NULL
> >
> > BEGIN
> > SET @WHERE = @WHERE + ' AND categoryJobListing.categoryJobDescrID IN ('
+
> > @categoryJobDescrID + ') '
> > END
> >
> > SET @sql =(' SELECT jobs.jobid, jobs. empid, jobs.jobname,
> > jobs.datetimeentered, location.location,
> > categoryJobDescr.categoryjobdescrname, employers.empName
> > FROM jobs
> > JOIN locationJob ON jobs.jobID = locationJob.jobID
> > JOIN categoryJobListing ON jobs.jobID = categoryJobListing.jobID
> > JOIN location ON location.locationID = locationJob.locationID
> > JOIN categoryJobDescr ON categoryJobDe
RE: stored procedure - Ambiguous column name
the default orderby is dateTimeEntered desc. here is cfml. thanks again!
-Original Message-
From: Stephen Moretti [mailto:[EMAIL PROTECTED]]
Sent: Thursday, April 18, 2002 10:18 AM
To: CF-Talk
Subject: Re: stored procedure - Ambiguous column name
Greg,
categoryJobDescrID is in both you categoryJobDescr and categoryJobListing
table.
The procedure looks ok, so I can only assume that you are passing through
categoryJobDescrID as one of the fields in @orderby without a tablename
prefix.
Regards
Stephen
- Original Message -
From: "Greg Jordan" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Thursday, April 18, 2002 3:41 PM
Subject: stored procedure - Ambiguous column name
> i've been trying to work this out for about a week and i am stumped. i
keep
> getting an error related to an ambiguous column name. I've checked the sp
> over and over (with my admittedly sub-par sql skills) and cannot find
> anything wrong. i've included the error and the sp below. any help is
> appreciated very much. thanks
>
> ***error message***
>
> Error occured at: 04/17/02 16:05:24
>
> Browser: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; .NET CLR
> 1.0.3705)
>
> ODBC Error Code = 37000 (Syntax error or access violation)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Ambiguous column name
> 'categoryJobDescrID'.
>
>
> SQL = "sp_jobs_select"
>
> Data Source = " ** "
>
> ***error message***
>
>
>
> ***stored procedure***
>
>
>
> CREATE PROCEDURE [yourconsultant].[sp_jobs_select] @keyword varchar(100),
> @locationID varchar(500), @categoryJobDescrID varchar(500), @page int,
@size
> int, @orderby varchar(100), @count int output
>
> AS
>
> DECLARE @sql nvarchar(2000), @where nvarchar(1000)
>
> SET @WHERE='0=0'
>
> IF @keyword IS NOT NULL
>
> BEGIN
> SET @WHERE = @WHERE + ' AND jobs.jobName LIKE ' +'''' + @keyword + '%'' OR
> jobs.jobDescription LIKE ' +'''' + @keyword + '%'''
> END
>
> IF @locationID IS NOT NULL
>
> BEGIN
> SET @WHERE = @WHERE + ' AND locationJob.locationID IN (' + @locationID +
')'
> END
>
> IF @categoryJobDescrID IS NOT NULL
>
> BEGIN
> SET @WHERE = @WHERE + ' AND categoryJobListing.categoryJobDescrID IN (' +
> @categoryJobDescrID + ') '
> END
>
> SET @sql =(' SELECT jobs.jobid, jobs. empid, jobs.jobname,
> jobs.datetimeentered, location.location,
> categoryJobDescr.categoryjobdescrname, employers.empName
> FROM jobs
> JOIN locationJob ON jobs.jobID = locationJob.jobID
> JOIN categoryJobListing ON jobs.jobID = categoryJobListing.jobID
> JOIN location ON location.locationID = locationJob.locationID
> JOIN categoryJobDescr ON categoryJobDescr.categoryJobDescrID
> =categoryJobListing.categoryJobDescrID
> JOIN employers ON employers.empID = jobs.empID
> WHERE ' +@WHERE+'
> ORDER BY '+@orderby)
>
> DECLARE @Start int, @End int
> SET @Start = (((@Page - 1) * @Size) + 1)
> SET @End = (@Start + @Size - 1)
>
> CREATE TABLE #temp
>
> (Row int IDENTITY(1,1) PRIMARY KEY,
> JOBID int, EMPID int,
> JOBNAME varchar(100),
> DATETIMEENTERED datetime,
> LOCATION varchar(150),
> CATEGORYJOBDESCRNAME varchar(75),
> EMPNAME varchar(100)
> )
>
> INSERT INTO #temp
> EXEC(@sql)
>
> BEGIN
> SELECT categoryjobdescrname, datetimeentered, empid, empname,
jobid,jobname,
> location
> FROM #temp
> WHERE (Row >= @Start) AND (Row <= @End)
>
> SET @count = (select count(*) from #temp)
> END
>
> DROP TABLE #temp
>
> GO
> ***stored procedure***
>
>
> -
> Greg Jordan
> Certified ColdFusion Developer
> Web Project Manager
> [EMAIL PROTECTED]
>
> ---
> [This E-mail scanned for viruses by Declude Virus]
>
>
__
Your ad could be here. Monies from ads go to support these lists and provide more
resources for the community. http://www.fusionauthority.com/ads.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: stored procedure - Ambiguous column name
no -Original Message- From: Dave Watts [mailto:[EMAIL PROTECTED]] Sent: Thursday, April 18, 2002 10:21 AM To: CF-Talk Subject: RE: stored procedure - Ambiguous column name > i've been trying to work this out for about a week and i > am stumped. i keep getting an error related to an ambiguous > column name. I've checked the sp over and over (with my > admittedly sub-par sql skills) and cannot find anything > wrong. i've included the error and the sp below. any help > is appreciated very much. > > ***error message*** > > Error occured at: 04/17/02 16:05:24 > > Browser: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; > .NET CLR 1.0.3705) > > ODBC Error Code = 37000 (Syntax error or access violation) > [Microsoft][ODBC SQL Server Driver][SQL Server]Ambiguous > column name 'categoryJobDescrID'. I don't see an obvious error, but are you referencing that field in your @orderby string? Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 __ Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: stored procedure - Ambiguous column name
Greg,
This error usually indicates a failure to properly alias during a join -
where the driver can't figure out which table to pull "jobdescId" from.
Mark
-Original Message-
From: Greg Jordan [mailto:[EMAIL PROTECTED]]
Sent: Thursday, April 18, 2002 9:42 AM
To: CF-Talk
Subject: stored procedure - Ambiguous column name
i've been trying to work this out for about a week and i am stumped. i keep
getting an error related to an ambiguous column name. I've checked the sp
over and over (with my admittedly sub-par sql skills) and cannot find
anything wrong. i've included the error and the sp below. any help is
appreciated very much. thanks
***error message***
Error occured at: 04/17/02 16:05:24
Browser: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; .NET CLR
1.0.3705)
ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC SQL Server Driver][SQL Server]Ambiguous column name
'categoryJobDescrID'.
SQL = "sp_jobs_select"
Data Source = " ** "
***error message***
***stored procedure***
CREATE PROCEDURE [yourconsultant].[sp_jobs_select] @keyword varchar(100),
@locationID varchar(500), @categoryJobDescrID varchar(500), @page int, @size
int, @orderby varchar(100), @count int output
AS
DECLARE @sql nvarchar(2000), @where nvarchar(1000)
SET @WHERE='0=0'
IF @keyword IS NOT NULL
BEGIN
SET @WHERE = @WHERE + ' AND jobs.jobName LIKE ' + + @keyword + '%'' OR
jobs.jobDescription LIKE ' + + @keyword + '%'''
END
IF @locationID IS NOT NULL
BEGIN
SET @WHERE = @WHERE + ' AND locationJob.locationID IN (' + @locationID + ')'
END
IF @categoryJobDescrID IS NOT NULL
BEGIN
SET @WHERE = @WHERE + ' AND categoryJobListing.categoryJobDescrID IN (' +
@categoryJobDescrID + ') '
END
SET @sql =(' SELECT jobs.jobid, jobs. empid, jobs.jobname,
jobs.datetimeentered, location.location,
categoryJobDescr.categoryjobdescrname, employers.empName
FROM jobs
JOIN locationJob ON jobs.jobID = locationJob.jobID
JOIN categoryJobListing ON jobs.jobID = categoryJobListing.jobID
JOIN location ON location.locationID = locationJob.locationID
JOIN categoryJobDescr ON categoryJobDescr.categoryJobDescrID
=categoryJobListing.categoryJobDescrID
JOIN employers ON employers.empID = jobs.empID
WHERE ' +@WHERE+'
ORDER BY '+@orderby)
DECLARE @Start int, @End int
SET @Start = (((@Page - 1) * @Size) + 1)
SET @End = (@Start + @Size - 1)
CREATE TABLE #temp
(Row int IDENTITY(1,1) PRIMARY KEY,
JOBID int, EMPID int,
JOBNAME varchar(100),
DATETIMEENTERED datetime,
LOCATION varchar(150),
CATEGORYJOBDESCRNAME varchar(75),
EMPNAME varchar(100)
)
INSERT INTO #temp
EXEC(@sql)
BEGIN
SELECT categoryjobdescrname, datetimeentered, empid, empname, jobid,jobname,
location
FROM #temp
WHERE (Row >= @Start) AND (Row <= @End)
SET @count = (select count(*) from #temp)
END
DROP TABLE #temp
GO
***stored procedure***
-
Greg Jordan
Certified ColdFusion Developer
Web Project Manager
[EMAIL PROTECTED]
---
[This E-mail scanned for viruses by Declude Virus]
__
Get the mailserver that powers this list at http://www.coolfusion.com
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: stored procedure - Ambiguous column name
> i've been trying to work this out for about a week and i > am stumped. i keep getting an error related to an ambiguous > column name. I've checked the sp over and over (with my > admittedly sub-par sql skills) and cannot find anything > wrong. i've included the error and the sp below. any help > is appreciated very much. > > ***error message*** > > Error occured at: 04/17/02 16:05:24 > > Browser: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; > .NET CLR 1.0.3705) > > ODBC Error Code = 37000 (Syntax error or access violation) > [Microsoft][ODBC SQL Server Driver][SQL Server]Ambiguous > column name 'categoryJobDescrID'. I don't see an obvious error, but are you referencing that field in your @orderby string? Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 __ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: stored procedure - Ambiguous column name
Greg,
categoryJobDescrID is in both you categoryJobDescr and categoryJobListing
table.
The procedure looks ok, so I can only assume that you are passing through
categoryJobDescrID as one of the fields in @orderby without a tablename
prefix.
Regards
Stephen
- Original Message -
From: "Greg Jordan" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Thursday, April 18, 2002 3:41 PM
Subject: stored procedure - Ambiguous column name
> i've been trying to work this out for about a week and i am stumped. i
keep
> getting an error related to an ambiguous column name. I've checked the sp
> over and over (with my admittedly sub-par sql skills) and cannot find
> anything wrong. i've included the error and the sp below. any help is
> appreciated very much. thanks
>
> ***error message***
>
> Error occured at: 04/17/02 16:05:24
>
> Browser: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; .NET CLR
> 1.0.3705)
>
> ODBC Error Code = 37000 (Syntax error or access violation)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Ambiguous column name
> 'categoryJobDescrID'.
>
>
> SQL = "sp_jobs_select"
>
> Data Source = " ** "
>
> ***error message***
>
>
>
> ***stored procedure***
>
>
>
> CREATE PROCEDURE [yourconsultant].[sp_jobs_select] @keyword varchar(100),
> @locationID varchar(500), @categoryJobDescrID varchar(500), @page int,
@size
> int, @orderby varchar(100), @count int output
>
> AS
>
> DECLARE @sql nvarchar(2000), @where nvarchar(1000)
>
> SET @WHERE='0=0'
>
> IF @keyword IS NOT NULL
>
> BEGIN
> SET @WHERE = @WHERE + ' AND jobs.jobName LIKE ' + + @keyword + '%'' OR
> jobs.jobDescription LIKE ' + + @keyword + '%'''
> END
>
> IF @locationID IS NOT NULL
>
> BEGIN
> SET @WHERE = @WHERE + ' AND locationJob.locationID IN (' + @locationID +
')'
> END
>
> IF @categoryJobDescrID IS NOT NULL
>
> BEGIN
> SET @WHERE = @WHERE + ' AND categoryJobListing.categoryJobDescrID IN (' +
> @categoryJobDescrID + ') '
> END
>
> SET @sql =(' SELECT jobs.jobid, jobs. empid, jobs.jobname,
> jobs.datetimeentered, location.location,
> categoryJobDescr.categoryjobdescrname, employers.empName
> FROM jobs
> JOIN locationJob ON jobs.jobID = locationJob.jobID
> JOIN categoryJobListing ON jobs.jobID = categoryJobListing.jobID
> JOIN location ON location.locationID = locationJob.locationID
> JOIN categoryJobDescr ON categoryJobDescr.categoryJobDescrID
> =categoryJobListing.categoryJobDescrID
> JOIN employers ON employers.empID = jobs.empID
> WHERE ' +@WHERE+'
> ORDER BY '+@orderby)
>
> DECLARE @Start int, @End int
> SET @Start = (((@Page - 1) * @Size) + 1)
> SET @End = (@Start + @Size - 1)
>
> CREATE TABLE #temp
>
> (Row int IDENTITY(1,1) PRIMARY KEY,
> JOBID int, EMPID int,
> JOBNAME varchar(100),
> DATETIMEENTERED datetime,
> LOCATION varchar(150),
> CATEGORYJOBDESCRNAME varchar(75),
> EMPNAME varchar(100)
> )
>
> INSERT INTO #temp
> EXEC(@sql)
>
> BEGIN
> SELECT categoryjobdescrname, datetimeentered, empid, empname,
jobid,jobname,
> location
> FROM #temp
> WHERE (Row >= @Start) AND (Row <= @End)
>
> SET @count = (select count(*) from #temp)
> END
>
> DROP TABLE #temp
>
> GO
> ***stored procedure***
>
>
> -
> Greg Jordan
> Certified ColdFusion Developer
> Web Project Manager
> [EMAIL PROTECTED]
>
> ---
> [This E-mail scanned for viruses by Declude Virus]
>
>
__
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
