EasyCFM.COM ColdFusion Forums / MS SQL Server Related Issues / Help With Loop Code in CF/SQL

   Reply to Discussion | New Discussion << previous || next >> 
Posted By Discussion Topic: Help With Loop Code in CF/SQL

book mark this topic Printer-friendly Version  send this discussion to a friend  new posts last

slamothe
06-04-2010 @ 1:16 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
New Member
Posts: 6
Joined: Jun 2010

I have very little expierience with coding CF and calling queries from SQL. I own a website that a friend of mine built and I am trying to revise (add) a few features. I'm having trouble getting the data I want to "order by" correctly in a cfloop.
Here is the code I'm working on:
<cfloop query="qTracks">

     <cfquery name="qTotalTime" datasource="#application.dsn#">
          SELECT TOP 1 *
          FROM tRaces
          WHERE sTrackName = '#sTrackName#' AND bDNF = 0  and iLapsCompleted = #id# and iCarCount >= 1
          ORDER BY dTotalTime, doDateTime
     </cfquery>
<cfquery name="qTrackLength" datasource="#application.dsn#">
     SELECT iTrackLength
     FROM tTracks
     ORDER BY sTrackName
     </cfquery>
     
     <tr <cfif currentrow mod 2 eq 1>bgcolor="000000"</cfif>>
          <td><font face="Verdana" size="2" color="ooccff"><!---a href="../tracks/index.cfm?trkid=#qTracks.sTrackName#"--->#qTracks.sTrackName#</a></font></td>
          <!---td><font face="Verdana" size="2" color="ooccff">#NumberFormat(iTrackLength, '(.___')#</font></td--->
          <td><font face="Verdana" size="2" color="ooccff">#qTotalTime.sDriverName#</font></td>
          <td><font face="Verdana" size="2" color="ooccff">#NumberFormat(qTotalTime.dTotalTime, '__.______')#</font></td>
          <td><font face="Verdana" size="2" color="ooccff">#NumberFormat(qTotalTime.dAvgLap, '__.______')#</font></td>
          <td><font face="Verdana" size="2" color="ooccff">#NumberFormat((3600/ qTotalTime.dAvgLap)  * iTrackLength, '(.___')#</font></td>
          <td width="300"><font face="Verdana" size="2" color="ooccff">#DateFormat(qTotalTime.doDateTime, 'mm/dd/yyyy')# @ #TimeFormat(qTotalTime.doDateTime, 'h:mm tt')#</font></td>
     </tr>
</cfloop>
</table>

I'm doing a calculation to calculate Avg. MPH on this line from above snippet.
<td><font face="Verdana" size="2" color="ooccff">#NumberFormat((3600/ qTotalTime.dAvgLap)  * iTrackLength, '(.___')#</font></td>

It works except I can't figure out how to make the loop look at the correct "iTrackLength" for each track as there are several different lengths.
In my table "tTracks" I have built a column iTrackLength" and each row has the correct data.
The loop see's the first row in iTrackLength then uses that same data for ALL TRACKS.
I have tried #sTrackName.iTrackLength# but errors as iTrackLength is UNDIFINED in sTrackName even though I "OREDER BY sTrackName"

Any and all input greatly appreciated.
Steve

To see the live code:
http://www.woostatserver.com/wooscrB/stats/stats.cfm

This message was edited by slamothe on 6-4-10 @ 2:46 PM

slamothe
06-04-2010 @ 4:26 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
New Member
Posts: 6
Joined: Jun 2010

Have I stated the question Clearly?
32 looks and no suggestions?
I'll take your hunch guess!!
:)
Steve

Jimbo7207
06-09-2010 @ 1:02 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
New Member
Posts: 2
Joined: Jun 2010

Can you post the 'qTracks' query you are looping?  I
think that's most likely your issue.

Jim

maquiladoras
08-23-2010 @ 5:08 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 2073
Joined: Dec 2002

well your issue is here

<cfquery name="qTrackLength" datasource="#application.dsn#">
     SELECT iTrackLength
     FROM tTracks
     ORDER BY sTrackName
     </cfquery>


the query qTrackLength simply doesn't include a column called sTrackName (all you are calling is iTrackLength), to do what you want you will need to do a query of queries, or set them up in to Vars and then perform your calcs that way

http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=using_recordsets_7.html

-
Any code printed thus far is likely to be stolen and used by Ray Horn, well done...ray
-


Website Designed and Developed by Pablo Varando.