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] > > ______________________________________________________________________ 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