Skip to main content
N
Neldarin
Participant
September 22, 2009
Question

CF8 ListAppend and ListGetAt problem

  • September 22, 2009
  • 2 replies
  • 1176 views

Hello everyone,

I'm using ColdFusion 8 and I encounter an issue usint the ListAppend and ListGetAt.

My code is the following:

<cfquery name="myProject" datasource="login">
                            SELECT usersreports.USERID, usersreports.REPORTID, reports.ID
                            FROM usersreports, reports
                            WHERE usersreports.USERID = #client.uid# AND reports.ID = usersreports.REPORTID
                        </cfquery>
                       
                        <cfset projectNames = "">
                        <cfset projectLinks = "">
                        <cfloop query="myProject">
                            <cfquery name="projects" datasource="login">
                                SELECT usersreports.USERID, usersreports.REPORTID, reports.ID, reports.NAME AS NAMES, reports.URL AS URL
                                FROM usersreports, reports
                                WHERE usersreports.USERID = #client.uid# AND reports.ID = usersreports.REPORTID
                            </cfquery>
                            <cfset projectNames = ListAppend(projectNames, projects.NAMES)>
                            <cfset projectLinks = ListAppend(projectLinks, projects.URL)>
                        </cfloop>
                       
                        <cfoutput>
                            <cfset i = 0>
                            <cfloop index="i" from="1" to="#projects.RecordCount#">
                                <cfset projectNames = #ListGetAt(projectNames, i)#>
                                #projectNames#
                            </cfloop>
                        </cfoutput>

With that code I get the following error and I try to fix it in some different ways but I didn't get yet.

Error Executing Database Query.

[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] You tried to execute a query that does not include the specified expression 'USERID' as part of an aggregate function.
The error occurred in C:\inetpub\wwwroot\Gaither Suite\gsproject.cfm: line 179
177 :                                 SELECT usersreports.USERID, usersreports.REPORTID, reports.ID, COUNT(reports.NAME) AS NAMES, reports.URL AS URL
178 :                                 FROM usersreports, reports
179 :                                 WHERE usersreports.USERID = #client.uid# AND reports.ID = usersreports.REPORTID
180 :                             </cfquery>
181 :                             <cfset projectNames = ListAppend(projectNames, projects.NAMES)>
SQLSTATE  42000
SQL    SELECT usersreports.USERID, usersreports.REPORTID, reports.ID, COUNT(reports.NAME) AS NAMES, reports.URL AS URL FROM usersreports, reports WHERE usersreports.USERID = 174 AND reports.ID = usersreports.REPORTID
VENDORERRORCODE  -3011
DATASOURCE  login
Resources:

Browser  Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US; rv:1.9.1.3) Gecko/20090824 Firefox/3.5.3
Remote Address  ::1
Referrer  http://localhost/Gaither%20Suite/gsdbank.cfm
Date/Time  21-Sep-09 11:07 PM
Stack Trace
at cfgsproject2ecfm122092148.runPage(C:\inetpub\wwwroot\Gaither Suite\gsproject.cfm:179)                                                                                  at cfgsproject2ecfm122092148.runPage(C:\inetpub\wwwroot\Gaither Suite\gsproject.cfm:179)                                  

java.sql.SQLException: [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] You tried to execute a query that does not include the specified expression 'USERID' as part of an aggregate function.
     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:254)
     at coldfusion.sql.Executive.executeQuery(Executive.java:1243)
     at coldfusion.sql.Executive.executeQuery(Executive.java:1008)
     at coldfusion.sql.Executive.executeQuery(Executive.java:939)
     at coldfusion.sql.SqlImpl.execute(SqlImpl.java:325)
     at coldfusion.tagext.sql.QueryTag.executeQuery(QueryTag.java:831)
     at coldfusion.tagext.sql.QueryTag.doEndTag(QueryTag.java:521)
     at cfgsproject2ecfm122092148.runPage(C:\inetpub\wwwroot\Gaither Suite\gsproject.cfm:179)
     at coldfusion.runtime.CfJspPage.invoke(CfJspPage.java:196)
     at coldfusion.tagext.lang.IncludeTag.doStartTag(IncludeTag.java:370)
     at coldfusion.filter.CfincludeFilter.invoke(CfincludeFilter.java:65)
     at coldfusion.filter.ApplicationFilter.invoke(ApplicationFilter.java:279)
     at coldfusion.filter.RequestMonitorFilter.invoke(RequestMonitorFilter.java:48)
     at coldfusion.filter.MonitoringFilter.invoke(MonitoringFilter.java:40)
     at coldfusion.filter.PathFilter.invoke(PathFilter.java:86)
     at coldfusion.filter.ExceptionFilter.invoke(ExceptionFilter.java:70)
     at coldfusion.filter.ClientScopePersistenceFilter.invoke(ClientScopePersistenceFilter.java:28)
     at coldfusion.filter.BrowserFilter.invoke(BrowserFilter.java:38)
     at coldfusion.filter.NoCacheFilter.invoke(NoCacheFilter.java:46)
     at coldfusion.filter.GlobalsFilter.invoke(GlobalsFilter.java:38)
     at coldfusion.filter.DatasourceFilter.invoke(DatasourceFilter.java:22)
     at coldfusion.CfmServlet.service(CfmServlet.java:175)
     at coldfusion.bootstrap.BootstrapServlet.service(BootstrapServlet.java:89)
     at jrun.servlet.FilterChain.doFilter(FilterChain.java:86)
     at coldfusion.monitor.event.MonitoringServletFilter.doFilter(MonitoringServletFilter.java:42)
     at coldfusion.bootstrap.BootstrapFilter.doFilter(BootstrapFilter.java:46)
     at jrun.servlet.FilterChain.doFilter(FilterChain.java:94)
     at jrun.servlet.FilterChain.service(FilterChain.java:101)
     at jrun.servlet.ServletInvoker.invoke(ServletInvoker.java:106)
     at jrun.servlet.JRunInvokerChain.invokeNext(JRunInvokerChain.java:42)
     at jrun.servlet.JRunRequestDispatcher.invoke(JRunRequestDispatcher.java:286)
     at jrun.servlet.ServletEngineService.dispatch(ServletEngineService.java:543)
     at jrun.servlet.jrpp.JRunProxyService.invokeRunnable(JRunProxyService.java:203)
     at jrunx.scheduler.ThreadPool$DownstreamMetrics.invokeRunnable(ThreadPool.java:320)
     at jrunx.scheduler.ThreadPool$ThreadThrottle.invokeRunnable(ThreadPool.java:428)
     at jrunx.scheduler.ThreadPool$UpstreamMetrics.invokeRunnable(ThreadPool.java:266)
     at jrunx.scheduler.WorkerThread.run(WorkerThread.java:66)

Any recomendation to fix it?

Thanks for your help.
This topic has been closed for replies.

2 replies

A
Adam Cameron.
Inspiring
September 22, 2009

That's a DB error, so it's got nothing to do with listAppend() or listGetAt().  Also the query that is reported as erroring is not one that you show in the code snippet you provide.

SELECT usersreports.USERID, usersreports.REPORTID, reports.ID, COUNT(reports.NAME) AS NAMES, reports.URL AS URL FROM usersreports, reports WHERE usersreports.USERID = 174 AND reports.ID = usersreports.REPORTID

The error itself is fairly self-explanatory:

You tried to execute a query that does not include the specified expression 'USERID' as part of an aggregate function.

If you're using aggregate functions, then all the columns that aren't being aggregated need to be grouped.  Look this up in your SQL book ("group by" or "count").

As for the code you posted, I can't help but think that you should be able to combine the two queries into one single one, to avoid the querying in a loop, which is a real performance killer.  One should minimise the number of times one hits the DB in a given operation.

Also: ditch MS Access if at all possible.  It is not fit for purpose as being the backend for a web site.  It's a desktop application, not a server application.

--

Adam

N
NeldarinAuthor
Participant
September 22, 2009

<cfquery name="myProject" datasource="login">
                            SELECT usersreports.USERID, usersreports.REPORTID, reports.ID
                            FROM usersreports, reports
                            WHERE usersreports.USERID = #client.uid# AND reports.ID = usersreports.REPORTID
                        </cfquery>
                       
                        <cfset projectNames = "">
                        <cfset projectLinks = "">
                        <cfloop index="i" from="1" to="#myProject.recordCount#">
                            <cfquery name="projects" datasource="login">
                                SELECT usersreports.USERID, usersreports.REPORTID, reports.ID, reports.NAME AS NAMES, reports.URL AS URL
                                FROM usersreports, reports
                                WHERE usersreports.USERID = #client.uid# AND reports.ID = usersreports.REPORTID
                            </cfquery>
                            <cfset projectNames = ListAppend(projectNames, #projects.NAMES#)>
                            <cfset projectLinks = ListAppend(projectLinks, #projects.URL#)>
                        </cfloop>
                       
                        <cfoutput>
                            <cfloop index="i" from="1" to="#ListLen(projectNames)#">
                                <a href="#ListGetAt(projectLinks, i)#" target="_blank">#ListGetAt(projectNames, i)#</a><br />
                            </cfloop>
                        </cfoutput>

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices