Solved

RecordCount counts too many records; duplicate records appear in CFOUTPUT

Posted on 2011-02-16
21
556 Views
Last Modified: 2012-05-11
My current problem: RecordCount counts too many business records. There are 40 businesses and each should have its own record, but RecordCount counts 114 records. The erroneous test page is here:

http://ascassociation.org/businessdirectory/index_test3.cfm?BusinessCategoryID=20

I think I have a grouping problem.

If I take away all of the "« previous 20 records | next 20 records »" code, then the page displays correctly. Example:

http://ascassociation.org/businessdirectory/index_test4.cfm?BusinessCategoryID=20

That page correctly displays the 40 businesses and their respective business categories. Only 40 records display.

When I introduce the "« previous 20 records | next 20 records »" code, then 114 records display, many of them duplicates.

So I need to modify my query and/or my HTML. I attach the full code, below.
Thank you as always for any advice.

Eric
<!----- 
Name:        index_test2.cfm 
Author:      Eric B, _agx_, gdemaria 
Description: Display ASCA businesses by category; display 20 businesses per page; use StartRow and EndRow to display next 20 and previous 20 records  
Created:     February 2011 
ColdFusion Version 8 
MS SQL Server 2005 
-----> 
 
<cfparam name="url.Letter" default=""> 
<cfparam name="url.BusinessCategoryID" default=""> 
 
<cfquery name="getBusinesses" datasource="#application.datasource#">   
  SELECT *   
   FROM Businesses biz
    LEFT OUTER JOIN BizHasCat bhc ON biz.BusinessID = bhc.BusinessID   
    LEFT OUTER JOIN Categories cat ON cat.BusinessCategoryID = bhc.BusinessCategoryID   
   <cfif val(url.BusinessCategoryID)>   
    INNER JOIN BizHasCat sel ON sel.BusinessID = biz.BusinessID   
    AND sel.BusinessCategoryID = <cfqueryparam cfsqltype="cf_sql_integer" value="#url.BusinessCategoryID#">   
   </cfif>   
  ORDER BY BusinessTitle ASC, BusinessCategory ASC   
</cfquery>  
 
<cfset activeCategoryName = ""> 
<cfif val(url.BusinessCategoryID)> 
  <cfquery name="getCat" datasource="#application.datasource#"> 
   select BusinessCategory 
    from Categories 
   where BusinessCategoryID = <cfqueryparam cfsqltype="cf_sql_integer" value="#url.BusinessCategoryID#">   
  </cfquery> 
  <cfset activeCategoryName = getCat.BusinessCategory> 
</cfif> 
  
<!--- site header ---> 
<cfinclude template="/SiteHeader.cfm">


 
<!--- configure for next N records --->
<!--- Number of rows to display per Next/Back page --->
<cfset RowsPerPage = 20> <!--- this can be modified to any value --->
 
<!--- At which row shall we start? Assume first row by default --->
<cfparam name="URL.StartRow" default="1" type="numeric">
 
<!--- Allow for Show All parameter in the URL --->
<cfparam name="URL.ShowAll" type="boolean" default="No">
 
<!--- We know the total number of rows from query getBusinesses --->
<cfset TotalRows = getBusinesses.RecordCount>
 
 
<!--- Last row is 10 rows past the starting row, or --->
<!--- total number of query rows, whichever is less --->
 
<cfset EndRow = Min(URL.StartRow + RowsPerPage - 1, TotalRows)>
<!--- Next button goes to 1 past current end row  --->
 
<cfset StartRowNext = EndRow + 1>
<!--- Back button goes back N rows from start row --->
 
<cfset StartRowBack = URL.StartRow - RowsPerPage>  
   
<p>[ <a href="/businessdirectory/">Return to ASC Supporter Business Directory</a> ]</p> 
  
  
<h1>ASC Supporter Business Directory</h1> 
  
<cfif len(activeCategoryName)> 
<h2><cfoutput>#activeCategoryName#</cfoutput></h2> 
</cfif> 

    <!-- include Next N records -->
        <!---provides next/back arrows for navigation through records--->
         
        <div class="align-center">
        <cfoutput>
            <p><em>Displaying records #URL.StartRow# to #EndRow# of #TotalRows# total.</em>
            
            <br />   
         
            <!---show links for Previous if necessary--->
           <cfif StartRowBack GT 0>
              <a href="#CGI.SCRIPT_NAME#?StartRow=#StartRowBack#&BusinessCategoryID=#url.BusinessCategoryID#">&laquo; previous #RowsPerPage# records</a>
           </cfif>
            
            &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
            
            <!---show links for Next if necessary--->
            <cfif StartRowNext LTE TotalRows>
             <a href="#CGI.SCRIPT_NAME#?StartRow=#StartRowNext#&BusinessCategoryID=#url.BusinessCategoryID#">next #RowsPerPage# records &raquo;</a>
             </cfif>
             
             </p>
            
        </cfoutput>
        </div>
    
    
<cfoutput query="getBusinesses" group="BusinessTitle" groupcasesensitive="no" startRow="#URL.StartRow#">   
    
<div class="boxf9edd0 float-right-img font11 width250px"><strong>Business Categories:</strong><br />   
<cfoutput group="BusinessCategory" groupcasesensitive="no">#getBusinesses.businesscategory#<br /></cfoutput>   
</div>   
   
   
<p><span class="bold blue00467f">#BusinessTitle#</span><br />   
<cfif BusinessContactname IS NOT "">#BusinessContactname#<br /></cfif>   
<cfif BusinessAddress1 IS NOT "">#BusinessAddress1#<br /></cfif>   
<cfif BusinessAddress2 IS NOT "">#BusinessAddress2#<br /></cfif>   
<cfif BusinessAddress3 IS NOT "">#BusinessAddress3#<br /></cfif>   
#BusinessCity# #BusinessState# #BusinessPostalCode#<br />   
<strong>Voice:</strong> #BusinessTelephone#<br />   
<cfif BusinessFax IS NOT ""><strong>Fax:</strong> #BusinessFax#<br /></cfif>   
<cfif BusinessEmail IS NOT ""><strong>E-mail:</strong> <a href="mailto:#BusinessEmail#">#BusinessEmail#</a><br /></cfif>   
<cfif BusinessURL IS NOT ""><strong>Web Address:</strong> <a href="http://#BusinessURL#">#BusinessURL#</a><br /><br /></cfif>    
   
<cfif BusinessDescription IS NOT ""><strong>About #BusinessTitle#:</strong><br /> #BusinessDescription#</cfif></p>   
   
   
<hr class="clear-both" />   
   
</cfoutput>  
 
  
  
 
  
<!--- site footer ---> 
<cfinclude template="/SiteFooter.cfm">

Open in new window

0
Comment
Question by:Eric Bourland
  • 10
  • 8
  • 2
  • +1
21 Comments
 
LVL 15

Expert Comment

by:myselfrandhawa
Comment Utility
Hi Eric,
Just dump your query and see the results to see if ur query is returning proper results or not
0
 
LVL 3

Expert Comment

by:dagaz_de
Comment Utility
Propably there is a Problem with double cfoutput

<cfoutput query="">
     <cfoutput group="
    </cfoutput>
</cfoutput>

try to use cfloop instead:

<cfloop query="">
     <cfoutput group="
    </cfoutput>
</cfloop>


0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
myselfrandhawa -- I will try that. More in a little while.

dagaz_de -- I was using a CFLOOP before -- I will go back to the code and try CFLOOP again.

I'll come back here in a little while and report my progress. Thank you for your help.

Eric
0
 
LVL 39

Accepted Solution

by:
gdemaria earned 500 total points
Comment Utility

Eric, don't change to the cfloop... remember from the previous post.

You need ONE group by in the outer cfoutput

<cfoutput query="getBusinesses" group="BusinessTitle">

then you need one addition cfoutput around your categories...

like this...

<div class="boxf9edd0 float-right-img font11 width250px"><strong>Business    Categories:</strong><br />  
   <cfoutput>#getBusinesses.businesscategory#<br /></cfoutput>  
</div>  


The problem with your code is a bit more involved.  You can't use record numbers to do paging because the record number does not relate directly to one business name (the thing you want to count).  You may have 1,2 or more records for one business because of the categories.   That means all the counts will be off when you try to do paging.

Do you have access to your database to create a stored procedure?  Is it SQL Server?   I have a possible solution for you..
0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
myselfrandhawa -- I did a CFDUMP and I see that the query is returning multiple results for each business -- each business is counted a number of times equal to the number of categories with which it is associated. It was a good idea to use CFDUMP to confirm this. Thank you. =)

dagaz_de -- CFLOOP was indeed working before, when my application was more static. Now the application is fully dynamic. I experimented a lot with CFLOOP; the nested CFOUTPUTs seem to be the way to go; per gdemaria's notes. Thank you for this idea.

gdemaria,

>>>Eric, don't change to the cfloop... remember from the previous post.

I remember. I see that it works with the nested CFOUTPUTs, but not with the CFLOOP.

>>>Do you have access to your database to create a stored procedure?
Actually, I do not. It is a shared SQL server and I do not have administrative access.

>>>Is it SQL Server?
SQL Server 2005. ColdFusion 8.

If the fact that I do not have rights to create a stored procedure will veto this project, that is not a terrible loss. The client is very pleased with what I have showed her. I have set up a working test page:

http://ascassociation.org/bd/

It works really well.

.... OK, while composing this reply, I wrote to my ISP, hosting.com, and asked them if they would set up a stored procedure for me. They wrote back and asked me to describe what I want them to set up.

What should I reply to Keith at hosting.com? How would you describe the stored procedure that you have in mind?

Thank you again. Hope your day is going well.

Eric
Hello Eric,

Since the database is on the shared database (VPS) server, we would need to set that up for you. If you let us know what you are wanting to do we can set it up for you.

Thank you



Regards,
Keith Mitchell
Cloud & Virtual Server Support

Open in new window

0
 
LVL 39

Expert Comment

by:gdemaria
Comment Utility
we don't need the procedure, it's just a neat way to do it.

Here's the query, this would replace your main query.

the result is this; a business title followed by a delimited list of categories such as:

ID       Title                  Categories
123    My Business      Books|Novels|Paperbacks

Every business will have only ONE record, which is what you need to do the paging.

Then we just need to parse the list of categories and show them one at a time


<cfquery name="getBusinesses" datasource="#application.datasource#">   
    SELECT 
      biz.*,
      STUFF(
        (
          SELECT '|' + c.businesscategory
          FROM BizHasCat bhc
            left join Categories c  ON  cat.BusinessCategoryID = bhc.BusinessCategoryID   
          WHERE  bhc.BusinessID = biz.BusinessID 
          FOR XML PATH('')
        ), 1, 1, '') as businesscategory
    FROM Businesses biz
       <cfif val(url.BusinessCategoryID)>   
        INNER JOIN BizHasCat sel ON sel.BusinessID = biz.BusinessID   
        AND sel.BusinessCategoryID = <cfqueryparam cfsqltype="cf_sql_integer" value="#url.BusinessCategoryID#">   
       </cfif>   
    ORDER BY BusinessTitle ASC, BusinessCategory ASC   
</cfquery>

Open in new window

0
 
LVL 39

Expert Comment

by:gdemaria
Comment Utility
i have to run for a while, will be back on later this evening..
0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
I'll try this out.
0
 
LVL 15

Expert Comment

by:myselfrandhawa
Comment Utility
what @gd has suggested try out and cfdump that values, if you get the relevant results, you might not need to change the cfoutput or cfloop
0
 
LVL 39

Assisted Solution

by:gdemaria
gdemaria earned 500 total points
Comment Utility
You actually need remove the GROUP of the query and the next loop.   The new query which only returns each business title once no longer needs to be grouped, so remove the group.

Instead, the category is a delimted list which we will parse and shown one per line.




<!----- 
Name:        index_test2.cfm 
Author:      Eric B, _agx_, gdemaria 
Description: Display ASCA businesses by category; display 20 businesses per page; use StartRow and EndRow to display next 20 and previous 20 records  
Created:     February 2011 
ColdFusion Version 8 
MS SQL Server 2005 
-----> 
 
<cfparam name="url.Letter" default=""> 
<cfparam name="url.BusinessCategoryID" default=""> 
 
<cfquery name="getBusinesses" datasource="#application.datasource#">   
    SELECT 
      biz.*,
      STUFF(
        (
          SELECT ',' + c.businesscategory
          FROM BizHasCat bhc
            left join Categories c  ON  cat.BusinessCategoryID = bhc.BusinessCategoryID   
          WHERE  bhc.BusinessID = biz.BusinessID 
          FOR XML PATH('')
        ), 1, 1, '') as businesscategory
    FROM Businesses biz
       <cfif val(url.BusinessCategoryID)>   
        INNER JOIN BizHasCat sel ON sel.BusinessID = biz.BusinessID   
          AND sel.BusinessCategoryID = <cfqueryparam cfsqltype="cf_sql_integer" value="#url.BusinessCategoryID#">   
       </cfif>   
    ORDER BY BusinessTitle ASC, BusinessCategory ASC   
</cfquery>  
 
<cfset activeCategoryName = ""> 
<cfif val(url.BusinessCategoryID)> 
  <cfquery name="getCat" datasource="#application.datasource#"> 
   select BusinessCategory 
    from Categories 
   where BusinessCategoryID = <cfqueryparam cfsqltype="cf_sql_integer" value="#url.BusinessCategoryID#">   
  </cfquery> 
  <cfset activeCategoryName = getCat.BusinessCategory> 
</cfif> 
  
<!--- site header ---> 
<cfinclude template="/SiteHeader.cfm">

<!--- configure for next N records --->
<!--- Number of rows to display per Next/Back page --->
<cfset RowsPerPage = 20> <!--- this can be modified to any value --->
 
<!--- At which row shall we start? Assume first row by default --->
<cfparam name="URL.StartRow" default="1" type="numeric">
 
<!--- Allow for Show All parameter in the URL --->
<cfparam name="URL.ShowAll" type="boolean" default="No">
 
<!--- We know the total number of rows from query getBusinesses --->
<cfset TotalRows = getBusinesses.RecordCount>
 
 
<!--- Last row is 10 rows past the starting row, or --->
<!--- total number of query rows, whichever is less --->
 
<cfset EndRow = Min(URL.StartRow + RowsPerPage - 1, TotalRows)>
<!--- Next button goes to 1 past current end row  --->
 
<cfset StartRowNext = EndRow + 1>
<!--- Back button goes back N rows from start row --->
 
<cfset StartRowBack = URL.StartRow - RowsPerPage>  
   
<p>[ <a href="/businessdirectory/">Return to ASC Supporter Business Directory</a> ]</p> 
  
  
<h1>ASC Supporter Business Directory</h1> 
  
<cfif len(activeCategoryName)> 
<h2><cfoutput>#activeCategoryName#</cfoutput></h2> 
</cfif> 

    <!-- include Next N records -->
        <!---provides next/back arrows for navigation through records--->
         
        <div class="align-center">
        <cfoutput>
            <p><em>Displaying records #URL.StartRow# to #EndRow# of #TotalRows# total.</em>
            
            <br />   
         
            <!---show links for Previous if necessary--->
           <cfif StartRowBack GT 0>
              <a href="#CGI.SCRIPT_NAME#?StartRow=#StartRowBack#&BusinessCategoryID=#url.BusinessCategoryID#">&laquo; previous #RowsPerPage# records</a>
           </cfif>
            
            &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
            
            <!---show links for Next if necessary--->
            <cfif StartRowNext LTE TotalRows>
             <a href="#CGI.SCRIPT_NAME#?StartRow=#StartRowNext#&BusinessCategoryID=#url.BusinessCategoryID#">next #RowsPerPage# records &raquo;</a>
             </cfif>
             
             </p>
            
        </cfoutput>
        </div>
    
    
<cfoutput query="getBusinesses"  startRow="#URL.StartRow#">   

 <div class="boxf9edd0 float-right-img font11 width250px"><strong>Business Categories:</strong><br />   
   <cfloop index="aCat" collection="#getBusinesses.businesscategory#" delimiters="|">
    #aCat#<br>
   </cfloop>
 </div>   
   
<p><span class="bold blue00467f">#BusinessTitle#</span><br />   
<cfif BusinessContactname IS NOT "">#BusinessContactname#<br /></cfif>   
<cfif BusinessAddress1 IS NOT "">#BusinessAddress1#<br /></cfif>   
<cfif BusinessAddress2 IS NOT "">#BusinessAddress2#<br /></cfif>   
<cfif BusinessAddress3 IS NOT "">#BusinessAddress3#<br /></cfif>   
#BusinessCity# #BusinessState# #BusinessPostalCode#<br />   
<strong>Voice:</strong> #BusinessTelephone#<br />   
<cfif BusinessFax IS NOT ""><strong>Fax:</strong> #BusinessFax#<br /></cfif>   
<cfif BusinessEmail IS NOT ""><strong>E-mail:</strong> <a href="mailto:#BusinessEmail#">#BusinessEmail#</a><br /></cfif>   
<cfif BusinessURL IS NOT ""><strong>Web Address:</strong> <a href="http://#BusinessURL#">#BusinessURL#</a><br /><br /></cfif>    
   
<cfif BusinessDescription IS NOT ""><strong>About #BusinessTitle#:</strong><br /> #BusinessDescription#</cfif></p>   

<hr class="clear-both" />   
</cfoutput>  
 
  
  
 
  
<!--- site footer ---> 
<cfinclude template="/SiteFooter.cfm">

Open in new window

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
gdemaria, I ran out of time today. I look forward to trying this out tomorrow. Thanks again and have a good evening. E
0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
gdemaria,

I made progress on this task. I used your code, above, but it looks like ColdFusion does not like the order of the attributes in the CFLOOP tag. I attach error message.

I've been trying different combinations of attributes. Are we allowed to use the delimiters attribute without the "list" attribute?

E
Attribute validation error for the CFLOOP tag.  
The tag has an invalid attribute combination: collection,delimiters,index. Possible combinations are:
Required attributes: 'file,index'. Optional attributes: 'charset,from,to'. 
Required attributes: 'index,list'. Optional attributes: 'delimiters'. 
Required attributes: 'query'. Optional attributes: 'endrow,startrow'. 
Required attributes: 'array,index'. Optional attributes: None. 
Required attributes: 'characters,file,index'. Optional attributes: 'charset'. 
Required attributes: 'collection,item'. Optional attributes: None. 
Required attributes: 'condition'. Optional attributes: None. 
Required attributes: 'from,index,to'. Optional attributes: 'step'. 
 
  
The error occurred in D:\websites\ascassociation.org\businessdirectory_test\index.cfm: line 109
 
107 :  
108 :  <div class="boxf9edd0 float-right-img font11 width250px"><strong>Business Categories:</strong><br />    
109 :    <cfloop index="aCat" collection="#getBusinesses.businesscategory#" delimiters="|"> 
110 :     #aCat#<br> 
111 :    </cfloop>

Open in new window

0
 
LVL 39

Assisted Solution

by:gdemaria
gdemaria earned 500 total points
Comment Utility
it should be "list" not collection



 <cfloop index="aCat" list="#getBusinesses.businesscategory#" delimiters="|">
                                ^^^^^^
     #aCat#<br>
 </cfloop>
0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
Now getting:

[Macromedia][SQLServer JDBC Driver][SQLServer]The multi-part identifier "cat.BusinessCategoryID" could not be bound.  
  
The error occurred in D:\websites\ascassociation.org\businessdirectory_test\index.cfm: line 27
 
25 :        <cfif val(url.BusinessCategoryID)>    
26 :         INNER JOIN BizHasCat sel ON sel.BusinessID = biz.BusinessID    
27 :           AND cat.BusinessCategoryID = <cfqueryparam cfsqltype="cf_sql_integer" value="#url.BusinessCategoryID#">    
28 :        </cfif>    
29 :     ORDER BY BusinessTitle ASC, BusinessCategory ASC    

Open in new window


Should cat.BusinessCategoryID in line 27 be c.BusinessCategoryID ?
<!-----  
Name:        index.cfm  
Author:      Eric B, _agx_, gdemaria  
Description: Display ASCA businesses by category; display 20 businesses per page; use StartRow and EndRow to display next 20 and previous 20 records   
Created:     February 2011  
ColdFusion Version 8  
MS SQL Server 2005  
----->  
  
<cfparam name="url.Letter" default="">  
<cfparam name="url.BusinessCategoryID" default="">  
  
<cfquery name="getBusinesses" datasource="#application.datasource#">    
    SELECT  
      biz.*, 
      STUFF( 
        ( 
          SELECT ',' + c.businesscategory 
          FROM BizHasCat bhc 
            left join Categories c  ON  cat.BusinessCategoryID = bhc.BusinessCategoryID    
          WHERE  bhc.BusinessID = biz.BusinessID  
          FOR XML PATH('') 
        ), 1, 1, '') as businesscategory 
    FROM Businesses biz 
       <cfif val(url.BusinessCategoryID)>    
        INNER JOIN BizHasCat sel ON sel.BusinessID = biz.BusinessID    
          AND sel.BusinessCategoryID = <cfqueryparam cfsqltype="cf_sql_integer" value="#url.BusinessCategoryID#">    
       </cfif>    
    ORDER BY BusinessTitle ASC, BusinessCategory ASC    
</cfquery>   
  
<cfset activeCategoryName = "">  
<cfif val(url.BusinessCategoryID)>  
  <cfquery name="getCat" datasource="#application.datasource#">  
   select BusinessCategory  
    from Categories  
   where BusinessCategoryID = <cfqueryparam cfsqltype="cf_sql_integer" value="#url.BusinessCategoryID#">    
  </cfquery>  
  <cfset activeCategoryName = getCat.BusinessCategory>  
</cfif>  
   
<!--- site header --->  
<cfinclude template="/SiteHeader.cfm"> 
 
<!--- configure for next N records ---> 
<!--- Number of rows to display per Next/Back page ---> 
<cfset RowsPerPage = 20> <!--- this can be modified to any value ---> 
  
<!--- At which row shall we start? Assume first row by default ---> 
<cfparam name="URL.StartRow" default="1" type="numeric"> 
  
<!--- Allow for Show All parameter in the URL ---> 
<cfparam name="URL.ShowAll" type="boolean" default="No"> 
  
<!--- We know the total number of rows from query getBusinesses ---> 
<cfset TotalRows = getBusinesses.RecordCount> 
  
  
<!--- Last row is 10 rows past the starting row, or ---> 
<!--- total number of query rows, whichever is less ---> 
  
<cfset EndRow = Min(URL.StartRow + RowsPerPage - 1, TotalRows)> 
<!--- Next button goes to 1 past current end row  ---> 
  
<cfset StartRowNext = EndRow + 1> 
<!--- Back button goes back N rows from start row ---> 
  
<cfset StartRowBack = URL.StartRow - RowsPerPage>   
    
<p>[ <a href="/businessdirectory/">Return to ASC Supporter Business Directory</a> ]</p>  
   
   
<h1>ASC Supporter Business Directory</h1>  
   
<cfif len(activeCategoryName)>  
<h2><cfoutput>#activeCategoryName#</cfoutput></h2>  
</cfif>  
 
    <!-- include Next N records --> 
        <!---provides next/back arrows for navigation through records---> 
          
        <div class="align-center"> 
        <cfoutput> 
            <p><em>Displaying records #URL.StartRow# to #EndRow# of #TotalRows# total.</em> 
             
            <br />    
          
            <!---show links for Previous if necessary---> 
           <cfif StartRowBack GT 0> 
              <a href="#CGI.SCRIPT_NAME#?StartRow=#StartRowBack#&BusinessCategoryID=#url.BusinessCategoryID#">&laquo; previous #RowsPerPage# records</a> 
           </cfif> 
             
            &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
             
            <!---show links for Next if necessary---> 
            <cfif StartRowNext LTE TotalRows> 
             <a href="#CGI.SCRIPT_NAME#?StartRow=#StartRowNext#&BusinessCategoryID=#url.BusinessCategoryID#">next #RowsPerPage# records &raquo;</a> 
             </cfif> 
              
             </p> 
             
        </cfoutput> 
        </div> 
     
     
<cfoutput query="getBusinesses"  startRow="#URL.StartRow#">    
 
 <div class="boxf9edd0 float-right-img font11 width250px"><strong>Business Categories:</strong><br />    
   <cfloop index="aCat" list="#getBusinesses.businesscategory#" delimiters="|"> 
    #aCat#<br> 
   </cfloop> 
 </div>    
    
<p><span class="bold blue00467f">#BusinessTitle#</span><br />    
<cfif BusinessContactname IS NOT "">#BusinessContactname#<br /></cfif>    
<cfif BusinessAddress1 IS NOT "">#BusinessAddress1#<br /></cfif>    
<cfif BusinessAddress2 IS NOT "">#BusinessAddress2#<br /></cfif>    
<cfif BusinessAddress3 IS NOT "">#BusinessAddress3#<br /></cfif>    
#BusinessCity# #BusinessState# #BusinessPostalCode#<br />    
<strong>Voice:</strong> #BusinessTelephone#<br />    
<cfif BusinessFax IS NOT ""><strong>Fax:</strong> #BusinessFax#<br /></cfif>    
<cfif BusinessEmail IS NOT ""><strong>E-mail:</strong> <a href="mailto:#BusinessEmail#">#BusinessEmail#</a><br /></cfif>    
<cfif BusinessURL IS NOT ""><strong>Web Address:</strong> <a href="http://#BusinessURL#">#BusinessURL#</a><br /><br /></cfif>     
    
<cfif BusinessDescription IS NOT ""><strong>About #BusinessTitle#:</strong><br /> #BusinessDescription#</cfif></p>    
 
<hr class="clear-both" />    
</cfoutput>   
  
   
   
  
   
<!--- site footer --->  
<cfinclude template="/SiteFooter.cfm">

Open in new window

0
 
LVL 39

Expert Comment

by:gdemaria
Comment Utility
Your 2nd post looks correct

      <cfif val(url.BusinessCategoryID)>    
        INNER JOIN BizHasCat sel ON sel.BusinessID = biz.BusinessID    
          AND sel.BusinessCategoryID = <cfqueryparam cfsqltype="cf_sql_integer" value="#url.BusinessCategoryID#">    
       </cfif>

using sel.   because the BizHasCat has the alias "sel"

0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
gdemaria, thank you for this. I will work on this further late this evening and / or tomorrow morning. I appreciate your time, and hope you have a ColdFusion-free weekend in store. Eric
0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
gdemaria,

Hope your weekend is going well. There's no urgency about replying to this.

I moved the test page to here: http://www.ascassociation.org/businessdirectory_test/

I'm still getting an error, and I've been looking at the query and how it does what you say it should do:

>>>Here's the query, this would replace your main query.

>>>the result is this; a business title followed by a delimited list of categories such as:
>>>ID       Title                  Categories
>>>123    My Business      Books|Novels|Paperbacks

I think I understand most of what you are doing. You are creating another list, as you did with the CEP task, when we needed to list all of the files associated with a document. I am not quite sure about this beginning part:

 SELECT  
      biz.*, 
      STUFF( 

Open in new window


... apply alias "biz" to all tables? And I am not sure what STUFF is doing.

And I am puzzled by the error that says The multi-part identifier "cat.BusinessCategoryID" could not be bound. It seems to me that we identify cat.BusinessCategoryID on line 20.

I'm continuing to look at this, while working on my third cup of coffee.

Eric
error:
Error Executing Database Query.  
[Macromedia][SQLServer JDBC Driver][SQLServer]The multi-part identifier "cat.BusinessCategoryID" could not be bound.  
  
The error occurred in D:\websites\ascassociation.org\businessdirectory_test\index.cfm: line 27
 
25 :        <cfif val(url.BusinessCategoryID)>    
26 :         INNER JOIN BizHasCat sel ON sel.BusinessID = biz.BusinessID    
27 :           AND sel.BusinessCategoryID = <cfqueryparam cfsqltype="cf_sql_integer" value="#url.BusinessCategoryID#">    
28 :        </cfif>    
29 :     ORDER BY BusinessTitle ASC, BusinessCategory ASC    


query:
<cfquery name="getBusinesses" datasource="#application.datasource#">    
    SELECT  
      biz.*, 
      STUFF( 
        ( 
          SELECT ',' + c.BusinessCategory
          FROM BizHasCat bhc 
            LEFT JOIN Categories c ON cat.BusinessCategoryID = bhc.BusinessCategoryID    
          WHERE bhc.BusinessID = biz.BusinessID  
          FOR XML PATH('')
        ), 1, 1, '') AS BusinessCategory
    FROM Businesses biz 
       <cfif val(url.BusinessCategoryID)>    
        INNER JOIN BizHasCat sel ON sel.BusinessID = biz.BusinessID    
          AND sel.BusinessCategoryID = <cfqueryparam cfsqltype="cf_sql_integer" value="#url.BusinessCategoryID#">    
       </cfif>    
    ORDER BY BusinessTitle ASC, BusinessCategory ASC    
</cfquery>   
  
<cfset activeCategoryName = "">  
<cfif val(url.BusinessCategoryID)>  
  <cfquery name="getCat" datasource="#application.datasource#">  
   SELECT BusinessCategory  
   FROM Categories  
   WHERE BusinessCategoryID = <cfqueryparam cfsqltype="cf_sql_integer" value="#url.BusinessCategoryID#">
  </cfquery>  
  <cfset activeCategoryName = getCat.BusinessCategory>  
</cfif>

Open in new window

0
 
LVL 39

Expert Comment

by:gdemaria
Comment Utility
Hey Eric,

To solve this problem, all you need to do is look for the thing the error says doesn't exist.   It doesn't know what cat.BusinessCategoryID is, right?   So there are two parts to that (1) the alias cat.  and (2) the column name BusinessCategoryID - one of these (or both) must be wrong.

So, we will start by looking for the table which has the alias  "cat"

          FROM BizHasCat bhc
            LEFT JOIN Categories c ON cat.BusinessCategoryID = bhc.BusinessCategoryID  

Oh wait, neither table BizHasCat (alias bhc)  or  table  Categories (alias is "C")  has the alias "Cat"

So that is the problem.. "Cat" cannot be used as a table alias because it is not associated with the table.

So what should CAT be if not CAT?     Since the other side of the = is bhc, we know that CAT should have been just C.


          FROM BizHasCat bhc
            LEFT JOIN Categories c ON c.BusinessCategoryID = bhc.BusinessCategoryID  


Now the alias matches with one of the tables
0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
I see that we needed alias c, not alias cat.

I was looking for a way to include the unneeded alias cat, when in fact cat was not needed.

It's working: http://ascassociation.org/businessdirectory_test/

Next I need to figure out a way to use the attribute EndRow to delimit the number of displayed pages to 20.

Apparently I cannot use attribute EndRow in tag CFOUTPUT. I have been doing some reading on StartRow / EndRow attributes: http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-71a8.html

Do I need to make my <cfoutput query="getBusinesses" startRow="#URL.StartRow#"> into a CFLOOP?

I wil try this.
0
 
LVL 39

Assisted Solution

by:gdemaria
gdemaria earned 500 total points
Comment Utility

Just use MaxRows="20" instead.   Maxrows limits the number of rows displayed so you can just set maxRow to your page size

<cfoutput query="getBusinesses" startRow="#URL.StartRow#" maxRows=""#RowsPerPage#>
0
 
LVL 3

Author Closing Comment

by:Eric Bourland
Comment Utility
It's working really well and it makes sense to me. Thank as always to gdemaria. Thank you also to  myselfrandhawa and dagaz_de.
Eric B
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

This article  is about submitting  form through  ColdFusion.Ajax.submitForm to the action page and send a response back in JSON format which later can be decoded using ColdFusion.JSON.decode. By this way you can avoid the usual page refresh for subm…
Hi, I will be creating today a basic tutorial on how we can create a Mail Custom Function and use it where ever we want. The main advantage about creating a custom function is that we can accommodate a range of arguments to pass to the Function and …
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now