Try eliminating one of the colorID fields from the results (it's in there twice). Also, the MM technotes say that this can occur if a column name doesn't exist in the table (or is spelled wrong).
:) -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Les Mizzell Sent: Saturday, April 16, 2005 9:47 AM To: [email protected] Subject: Re: [CFCDev] One more query question Roland Collins wrote: > Can you post the full stack trace? Sure - I'm still monking with it. Query at end of message... Un-nesting the queries is working fine using: WHERE papers.ITEM_NUM NOT IN (#quotedValueList(srch.ITEM_NUM)#) This will never return more than 50 or 60 records at a time, so I'm not too worried about effeciency and could use two separate queries if I had too, but I'm one a mission to get it to work with the one now... Error Executing Database Query. [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1. The error occurred in D:\Inetpub\wwwroot\papersellers\search_result_BETA.cfm: line 59 57 : ITEM_DESC LIKE '#FORM.SRCH# %' )) 58 : AND (color.colorCAT = '#FORM.SRCH#' 59 : OR color.colorNAME = '#FORM.SRCH#' ) 60 : </cfquery> 61 : ---------------------------------------------------------------------------- ---- SQL SELECT color.colorCAT, color.colorNAME, color.colorID, colorLOOKUP.itemNUM, colorLOOKUP.colorID, papers.ITEM_NUM, papers.ID, papers.PA_TYPE, papers.PA_CATEGORY FROM ( color INNER JOIN colorLOOKUP ON color.colorID = colorLOOKUP.colorID) INNER JOIN papers ON colorLOOKUP.itemNUM = papers.ITEM_NUM WHERE papers.ITEM_NUM NOT IN (SELECT ITEM_NUM FROM papers WHERE (ITEM_NAME LIKE '%pink%' or PA_TYPE LIKE '%pink%' or PA_CATEGORY LIKE '%pink%' or PA_COUNTRY LIKE '%pink%' or ITEM_DESC LIKE 'pink %' )) AND (color.colorCAT = 'pink' OR color.colorNAME = 'pink' ) DATASOURCE phoenixart VENDORERRORCODE -3010 SQLSTATE 07002 Please try the following: Check the ColdFusion documentation to verify that you are using the correct syntax. Search the Knowledge Base to find a solution to your problem. Browser Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; FunWebProducts-MyWay) Remote Address 127.0.0.1 Referrer http://localhost:8500/papersellers/search_result_BETA.cfm Date/Time 16-Apr-05 09:41 AM Stack Trace (click to expand) at cfsearch_result_BETA2ecfm370778497.runPage(D:\Inetpub\wwwroot\papersellers\s earch_result_BETA.cfm:59) at cfsearch_result_BETA2ecfm370778497.runPage(D:\Inetpub\wwwroot\papersellers\s earch_result_BETA.cfm:59) java.sql.SQLException: [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1. at macromedia.sequelink.ssp.Diagnostic.toSQLException(Unknown Source) at macromedia.sequelink.ssp.Chain.cnvDiagnostics(Unknown Source) at macromedia.sequelink.ssp.Chain.decodeDiagnostic(Unknown Source) at macromedia.sequelink.ssp.Chain.decodeBody(Unknown Source) at macromedia.sequelink.ssp.Chain.decode(Unknown Source) at macromedia.sequelink.ssp.Chain.send(Unknown Source) at macromedia.sequelink.ctxt.stmt.StatementContext.execDirect(Unknown Source) at macromedia.jdbc.sequelink.SequeLinkImplStatement.execute(Unknown Source) at macromedia.jdbc.slbase.BaseStatement.commonExecute(Unknown Source) at macromedia.jdbc.slbase.BaseStatement.executeInternal(Unknown Source) at macromedia.jdbc.slbase.BaseStatement.execute(Unknown Source) at coldfusion.server.j2ee.sql.JRunStatement.execute(JRunStatement.java:212) at coldfusion.sql.Executive.executeQuery(Executive.java:705) at coldfusion.sql.Executive.executeQuery(Executive.java:638) at coldfusion.sql.Executive.executeQuery(Executive.java:599) at coldfusion.sql.SqlImpl.execute(SqlImpl.java:236) at coldfusion.tagext.sql.QueryTag.doEndTag(QueryTag.java:498) at cfsearch_result_BETA2ecfm370778497.runPage(D:\Inetpub\wwwroot\papersellers\s earch_result_BETA.cfm:59) at coldfusion.runtime.CfJspPage.invoke(CfJspPage.java:152) at coldfusion.tagext.lang.IncludeTag.doStartTag(IncludeTag.java:343) at coldfusion.filter.CfincludeFilter.invoke(CfincludeFilter.java:65) at coldfusion.filter.ApplicationFilter.invoke(ApplicationFilter.java:210) at coldfusion.filter.PathFilter.invoke(PathFilter.java:86) at coldfusion.filter.LicenseFilter.invoke(LicenseFilter.java:27) at coldfusion.filter.ExceptionFilter.invoke(ExceptionFilter.java:50) at coldfusion.filter.ClientScopePersistenceFilter.invoke(ClientScopePersistence Filter.java:28) at coldfusion.filter.BrowserFilter.invoke(BrowserFilter.java:38) at coldfusion.filter.GlobalsFilter.invoke(GlobalsFilter.java:38) at coldfusion.filter.DatasourceFilter.invoke(DatasourceFilter.java:22) at coldfusion.CfmServlet.service(CfmServlet.java:105) at coldfusion.bootstrap.BootstrapServlet.service(BootstrapServlet.java:78) at jrun.servlet.ServletInvoker.invoke(ServletInvoker.java:91) at jrun.servlet.JRunInvokerChain.invokeNext(JRunInvokerChain.java:42) at jrun.servlet.JRunRequestDispatcher.invoke(JRunRequestDispatcher.java:257) at jrun.servlet.ServletEngineService.dispatch(ServletEngineService.java:527) at jrun.servlet.http.WebService.invokeRunnable(WebService.java:172) at jrunx.scheduler.ThreadPool$DownstreamMetrics.invokeRunnable(ThreadPool.java: 349) at jrunx.scheduler.ThreadPool$ThreadThrottle.invokeRunnable(ThreadPool.java:457 ) at jrunx.scheduler.ThreadPool$UpstreamMetrics.invokeRunnable(ThreadPool.java:29 5) at jrunx.scheduler.WorkerThread.run(WorkerThread.java:66) > <cfquery name="srch2" datasource="myDATA"> > SELECT > color.colorCAT, > color.colorNAME, > color.colorID, > colorLOOKUP.itemNUM, > colorLOOKUP.colorID, > papers.ITEM_NUM, > papers.ID, > papers.PA_TYPE, > papers.PA_CATEGORY > FROM ( color > INNER JOIN colorLOOKUP ON color.colorID = colorLOOKUP.colorID) > INNER JOIN papers ON colorLOOKUP.itemNUM = papers.ITEM_NUM > WHERE papers.ITEM_NUM NOT IN > (SELECT ITEM_NUM FROM papers > WHERE > (ITEM_NAME LIKE '%#FORM.SRCH#%' or > PA_TYPE LIKE '%#FORM.SRCH#%' or > PA_CATEGORY LIKE '%#FORM.SRCH#%' or > PA_COUNTRY LIKE '%#FORM.SRCH#%' or > ITEM_DESC LIKE '#FORM.SRCH# %' )) > AND (color.colorCAT = '#FORM.SRCH#' > OR color.colorNAME = '#FORM.SRCH#' ) > </cfquery> > > > I *will* add cfqueryparam to it once it's working for me properly... > -- ----------- Les Mizzell ---------------------------------------------------------- You are subscribed to cfcdev. To unsubscribe, send an email to [email protected] with the words 'unsubscribe cfcdev' as the subject of the email. CFCDev is run by CFCZone (www.cfczone.org) and supported by CFXHosting (www.cfxhosting.com). An archive of the CFCDev list is available at www.mail-archive.com/[email protected] ---------------------------------------------------------- You are subscribed to cfcdev. To unsubscribe, send an email to [email protected] with the words 'unsubscribe cfcdev' as the subject of the email. CFCDev is run by CFCZone (www.cfczone.org) and supported by CFXHosting (www.cfxhosting.com). An archive of the CFCDev list is available at www.mail-archive.com/[email protected]
