[TheTVDuchess]

I'm having issues connect the datasource to the query. I have a beginner/basic understanding/knowlegde of ColdFusion, so my company has asked me to fix some old files for a client that were apparently built by some one who didn't know ColdFusion at all.

Here is the error message I receive:

CFML Runtime Error
Variable DISTRICTZONELIST does not exist.

Here is the code I used:

<cfloop query="districtList"
   datasource= "MetricsFile">
SELECT * FROM districtList
<tr bgcolor="##cccccc">
   <td align="right">#numberFormat(sum_count)#</td>
   <td align="right">#numberFormat(total_count)#</td>
   <td align="right">#decimalFormat(net_food_cost)#</td>
   <td align="right">#decimalFormat(net_labor_cost)#</td>
   <td align="right">#timeSpanStr#</td>						    
</tr>
</cfloop>

Now the "MetricsFile" was a file buried deep in the dev folder. I put a copy of it in the root directory, but it doesn't seem to notice there either. I also tried to put .cfc after the file name, but that didn't work either.

Any help would greatly be appreciated!

[Craig328]

Hi Duchess. Welcome to DIC!

Um...yeah, that's not gonna work for a whole lot of reasons. Good news is: it's an easy fix.

So, for starters, what you need to understand is that displaying the contents of a database table in a browser via CF is a two step process. Step 1 is retrieving the data you need from the database while Step 2 is the actual outputting of that retrieval to the browser.

Step 1 is accomplished by using the CFQUERY tag. In your case it would look like this:

<cfquery name="getDistrictData" datasource="MetricsFile">
   SELECT * FROM districtList
</cfquery>

So, what you have there is the query that you'd use to get the data you want to display. The name attribute of the CFQUERY tag is how you'll refer to the dataset your query just retrieved. CF creates a structure that contains the resultset from your query and this is how you name that structure so you can work with it thereafter.

For Step 2, it's pretty similar to what you already have:

<cfoutput>
<cfloop query="getDistrictData">
   <tr bgcolor="##cccccc">
      <td align="right">#numberFormat(sum_count)#</td>
      <td align="right">#numberFormat(total_count)#</td>
      <td align="right">#decimalFormat(net_food_cost)#</td>
      <td align="right">#decimalFormat(net_labor_cost)#</td>
      <td align="right">#timeSpanStr#</td>						    
   </tr>
</cfloop>
</cfoutput>

This second block makes a couple of changes to what you had. Notice the opening and closing CFOUTPUT tags. This is needed to render the referenced output to the user's browser. The CFLOOP contained inside that is looping over the retrieved query dataset (that I named getDistrictData to differentiate it from the database table districtList) and outputting each piece data inside part of a table structure. Now, understand that the query you posted that said "SELECT *" means basically "select everything" and yet your output is referencing specific database table column names (sum_count, total_count, net_food_cost, etc.). As long as you have columns named those things in the districtList table in your database, you're fine. However, the SQL (the database code contained inside the CFQUERY tags) can be made to do much more than simply SELECT data. It can also do math functions for you, get an average, find the highest/lowest/midrange value of a column...all kinds of things. I mention this because the titling of some of your output suggests some kind of SQL function was performed (sum, count).

Anyway, that should fix what you posted. If you're going to do more CF, please feel free to post any issues you encounter here. Also, have a look at our pinned forum topic Coldfusion Resources as it has a great many handy things in there to help you out.

Good luck!

[TheTVDuchess]

Thank you! I think this will help a lot! And I am definitely impressed with this forum, and the speed in which people respond! I am a DBA, not a programmer, so web development is not really in my wheel house. I really am trying to figure out this stuff on my own as much as possible, but you know how that goes...

I've tried the changes that you suggested, but I am now experiencing a new problem. I get the following message:

CFML Runtime Error
Variable GETSUBMISSIONSTATUSDATA does not exist.

I found this as the function name,GETSUBMISSIONSTATUSDATA, in the MetricsFile.cfc file and I've tried to use just the query name, "districtList".

I must be missing a crucial part... Any ideas?

[Craig328]

Hm. That variable doesn't show up in any of the code you posted earlier. If the page that is throwing the error isn't too big, can you post it here?

It may be that getsubmissionstatusdata is a function or a component method or other such thing but without seeing the code it's nearly impossible to say for sure.

[TheTVDuchess]

I've sent you a PM in regards to this matter.