Greg, Only thing I can see that isn't going to help is : dbvarname="@orderyby" in one of your <cfprocparam> 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! > > <cfparam name="attributes.keywords" default=""> > <cfparam name="attributes.locationID" default=""> > <cfparam name="attributes.categoryJobDescrID" default=""> > <cfparam name="attributes.page" default="1"> > <cfparam name="attributes.size" default="10"> > <cfparam name="attributes.orderby" default="dateTimeEntered desc"> > > <cfstoredproc procedure="sp_jobs_select" datasource="#request.Datasource#" > > <cfif attributes.keywords IS NOT ""> > <cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="@keyword" > value="#attributes.keywords#" null="No"> > <cfelse> > <cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="@keyword" > null="Yes"> > </cfif> > <cfif attributes.locationID IS NOT ""> > <cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="@locationID" > value="#attributes.locationID#" null="No"> > <cfelse> > <cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="@locationID" > null="Yes"> > </cfif> > <cfif attributes.categoryJobDescrID IS NOT ""> > <cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" > dbvarname="@categoryJobDescrID" value="#attributes.categoryJobDescrID#" > null="No"> > <cfelse> > <cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" > dbvarname="@categoryJobDescrID" null="Yes"> > </cfif> > <cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="@page" > value="#attributes.page#" null="No"> > <cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="@size" > value="#attributes.size#" null="No"> > <cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="@orderyby" > value="#URLDecode(attributes.orderby)#" null="No"> > <cfprocparam type="Out" cfsqltype="CF_SQL_INTEGER" variable="rowcount" > dbvarname="@count" null="Yes"> > <cfprocresult name="jobs_select"> > </cfstoredproc> > > -----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] > > > > > > ______________________________________________________________________ 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
