variable DTRAN is undefined

[emaleroland]

I was asked to look at some code written in ColdFusion which I've had very little exposure to and haven't seen since the 90s and some of it is not clear and I'm sure it's basic.

I got past one error because of a typo in a variable name. Now, another part of the code is erroring out as I put in the title:
variable DTRAN is undefined.

One common page uses a switch, based up on the menu to call for that item, so the error returns referencing that page: sshAdmin.cfm, but the pages in question are export.cfm and export_do.cfm

export.cfm

            <cfcase value="xpr">
                <tr><td class="darkRow" colspan="2" align="center">Back to <b><a href="sshadmin.cfm?adm=xpr" class="darkLinks">Main Export Menu</a></b></td></tr>
                <cfoutput><tr><td class="textRow"><a href="sshadmin.cfm?adm=xpr&xpr=xpr&xp=all&tID=#tID#" class="textRow">Export <b>ALL</b></a></td><td class="textRow"><a href="sshadmin.cfm?adm=xpr&xpr=xpr&xp=new&tID=#tID#" class="textRow">Export <b>NEW</b></a></td></tr></cfoutput>
                <tr><td class="ltGroupRow">Date Ran</td><td class="ltGroupRow"># Of Records</td></tr>
                <cfoutput query="cfspExport">
                    <tr><td class="textRow">#DateFormat(dtRan, "mm/dd/yyyy")# #TimeFormat(dtRan, "hh:mm tt")#</td><td class="textRow">#intRecords#</td></tr>
                </cfoutput>
            </cfcase>

export_do.cfm

                    <cfmail from="#REQUEST.mailFrom#" server="#REQUEST.mailServer#" username="#REQUEST.mailUN#" password="#REQUEST.mailPW#" subject="New SSH Export" to="#sAdminEmail#" query="cfspExportInfo" mimeattach="#sDir##sFileName#">Hello!

Export Type: #vcExportType#
Run Time : #DateFormat(dtRan, "mm/dd/yyyy")# #TimeFormat(dtRan, "hh:mm tt")#
Records : #intRecords#

Text file is attached. Images and text file available via FTP
</cfmail>

From what I'm told, all this worked at some point but when I found a variable was spelled incorrectly, with the first error I was getting, I'm concerned. This site was moved and this part has not been looked at in years and I was told by the original developer, who has abandoned it, that he did make some changes to make it work with the new host.

From what I' ve looked at, I don't know if I need a CFPARAM to define it of it I'm missing a file or there is a path issue where the page I need resides. I can run the stored procedure in SQL adding the values passed, and that completes successfully so it appears it's something basic. I don't know if I've given enough information and any help is appreciated.

[arrgh]

On what line does the error occur?

I don't know the app, so I'm guessing based on what I see. "dtRan" appears to be the name of a column in a query. There are 2 queries in the code (cfspExportInfo and cfspExport). The error suggests one of them does not contain the "dtRan" column. On which line does the error occur? The one doing the output:

<cfoutput query="cfspExport">
  <tr><td class="textRow">#DateFormat(dtRan, "mm/dd/yyyy")# .... 
</cfoutput>

Or the one generating the email:

<cfmail query="cfspExportInfo" ...>
    ...
    Run Time : #DateFormat(dtRan, "mm/dd/yyyy")# 

Tip, you can view the contents of any variable by using cfdump. Do not use it on a query w/100K rows because it'll take a really long time to display.

   <cfdump var="#cfspExport#"> 
   <cfdump var="#cfspExportInfo#">

[emaleroland]

dtRan is a column in the Exports table. If I select to export New records, I saw the stored procedure, that he's only using dtRan to sort the records. Here is the SP. It doesn't appear as if it's part of his result set.

USE [SSHO_OLHP]
GO
/****** Object: StoredProcedure [dbo].[xpr_Export_New] Script Date: 04/04/2012 04:30:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER PROCEDURE [dbo].[xpr_Export_New]

@tID integer

AS

DECLARE @eID AS integer
SELECT TOP 1 @eID = ExportID
FROM Exports
WHERE intExportTypeID = @tID
ORDER BY dtRan DESC

SELECT p.PlanID, p.vcSSHID, p.vcName, p.intLivingSqF, p.intCoveredSqF, p.tiFloors, p.intFirstSqF, p.intSecondSqF, p.intThirdSqF,
p.intFourthSqF, p.intFifthSqF, p.tiBedrooms, p.tiBedroomsUp, p.tiBedroomsDown, p.tiBaths, p.tiHalfBaths, p.tiLivingAreas,
p.tiDiningAreas, p.siWidth, p.siDepth, p.siHeight, p.siFirstCeilingHeight,
p.siOtherCeilingHeight,
s.vcStyle,
em.vcExteriorMaterial,
gt.vcGarageType,
go.vcGarageOrientation,
g.rlGarageCars
FROM Plans p JOIN Styles s ON p.intStyleID = s.StyleID
JOIN ExteriorMaterials em ON p.intExteriorMaterialID = em.ExteriorMaterialID
LEFT OUTER JOIN (
Garages g JOIN GarageTypes gt ON g.intGarageTypeID = gt.GarageTypeID
JOIN GarageOrientations go ON g.intGarageOrientationID = go.GarageOrientationID
) ON p.planID = g.intPlanID
WHERE p.bActive =1 AND p.bForSale = 1 AND p.PlanID NOT IN
(SELECT intPlanID
FROM ExportRecords
WHERE intExportID = @eID)

[arrgh]

I don't know which query variable that proc relates to, but yeah, it doesn't include DTRAN in the results.

Since there're 2 query variables in the CF code, the likely possibilities are a) the codes using the wrong query in the wrong place b) the code's using the wrong column name or c) the procedure is missing a column.

Knowing nothing about the app, I couldn't say which is most likely.