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\search_result_BETA.cfm:59) at cfsearch_result_BETA2ecfm370778497.runPage(D:\Inetpub\wwwroot\papersellers\search_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\search_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(ClientScopePersistenceFilter.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:295)
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]
