Get to make sure the query: GetReport
is actually returning a record.
According to the query error:
SQL = "SELECT ProjectID, ProjectName FROM ProjectManager WHERE ProjectID ="
There is No ProjectID in the Where line.
Thus a syntax error in the SQL.
-----Original Message-----
From: Jay Patton [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, March 20, 2001 7:42 PM
To: CF-Talk
Subject: cfmail Questions
The following is code from a template that i am planing on running with a
schedual task (nightly) im not haveing any problems whith the accually task
its self but i am getting page errors durring testing. here is the error
followed by the code:
ERROR:
Error Diagnostic Information
ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near
'='.
SQL = "SELECT ProjectID, ProjectName FROM ProjectManager WHERE ProjectID ="
MY CODE: (the full page)
<!-- ************************************************************* -->
<!-- * Nightly Report to run and show Report from Past days Work * -->
<!-- ************************************************************* -->
<!-- Get Mail List of Employees -->
<cfquery name="MailList"
datasource="#datasource#"
username="#username#"
password="#password#"
dbtype="ODBC">
SELECT EmployeeEmail
FROM EmployeeManager
</cfquery>
<cfset DateNow = #Now()#>
<!-- Get Report Information -->
<cfquery name="GetReport"
datasource="#datasource#"
username="#username#"
password="#password#"
dbtype="ODBC">
SELECT *
FROM WorkOrderManager
WHERE DateEntered = #DateFormat(DateNow,"MM/DD/YYYY")#
ORDER BY DateEntered
</cfquery>
<!-- Mails Report out -->
<cfmail to="#MailList.EmployeeEmail#"
from="[EMAIL PROTECTED]"
subject="Nightly Report For #DateFormat(DateNow,"MM/DD/YYYY")#"
server="mail.webpro-usa.com"
type="HTML">
<HTML>
<HEAD>
<TITLE></TITLE>
</HEAD>
<BODY BGCOLOR="##FFFFFF" leftmargin="0" topmargin="0" marginwidth="0"
marginheight="0" background="images/background.gif">
<TABLE BORDER="0" CELLPADDING="0" CELLSPACING="0" WIDTH="755"
HEIGHT="500">
<TR>
<TD ROWSPAN="1" COLSPAN="2" WIDTH="755" HEIGHT="69"><IMG NAME="JS0"
SRC="images/template-1x1.jpg" WIDTH="755" HEIGHT="69" BORDER="0"></TD>
</TR>
<TR>
<TD ROWSPAN="1" COLSPAN="1" WIDTH="62" HEIGHT="28"><IMG NAME="JS1"
SRC="images/template-2x1.jpg" WIDTH="62" HEIGHT="28" BORDER="0"></TD>
<TD ROWSPAN="1" COLSPAN="1" WIDTH="693" HEIGHT="28"><IMG NAME="JS2"
SRC="images/template-2x2.jpg" WIDTH="693" HEIGHT="28" BORDER="0"></TD>
</TR>
<TR>
<TD ROWSPAN="1" COLSPAN="1" WIDTH="62" HEIGHT="403"><IMG NAME="JS3"
SRC="images/template-3x1.jpg" WIDTH="62" HEIGHT="403" BORDER="0"></TD>
<TD ROWSPAN="1" COLSPAN="1" WIDTH="693" HEIGHT="403" valign="top">
<table width="689" border="0" cellspacing="2" cellpadding="2">
<tr align="left" valign="top">
<td width="73"></td>
<td width="602">
<table width="100%" border="0">
<tr bgcolor="##CCCCCC">
<td width="12%"><b><font size="1" face="Verdana, Arial,
Helvetica, sans-serif">Date</font></b></td>
<td width="24%"><b><font size="1" face="Verdana, Arial,
Helvetica, sans-serif">Project</font></b></td>
<td width="25%"><b><font size="1" face="Verdana, Arial,
Helvetica, sans-serif">Work Type</font></b></td>
<td width="9%"><b><font size="1" face="Verdana, Arial,
Helvetica, sans-serif">Staff</font></b></td>
<td width="9%"><b><font size="1" face="Verdana, Arial,
Helvetica, sans-serif">Start</font></b></td>
<td width="9%"><b><font size="1" face="Verdana, Arial,
Helvetica, sans-serif">End</font></b></td>
<td width="12%"><b><font size="1" face="Verdana, Arial,
Helvetica, sans-serif">Total</font></b></td>
</tr>
<tr bgcolor="#IIf(GetReport.CurrentRow Mod 2, DE('FFFFE7'),
DE('99CC99'))#">
<td width="12%"><font size="1" face="Verdana, Arial,
Helvetica,
sans-serif">#DateFormat(GetReport.DateEntered,"MM/DD/YYYY")#</font></td>
<td width="24%"><font size="1" face="Verdana, Arial,
Helvetica, sans-serif"><cfquery name="GetProject" datasource="#datasource#"
username="#username#" password="#password#" dbtype="ODBC">SELECT ProjectID,
ProjectName FROM ProjectManager WHERE ProjectID =
#GetReport.ProjectID#</cfquery>#GetProject.ProjectName#</font></td>
<td width="25%"><font size="1" face="Verdana, Arial,
Helvetica, sans-serif"><cfquery name="GetWorkType" datasource="#datasource#"
username="#username#" password="#password#" dbtype="ODBC">SELECT WorkTypeID,
WorkName FROM WorkTypeManager WHERE WorkTypeID =
#GetReport.WorkTypeID#</cfquery>#GetWorkType.WorkName#</font></td>
<td width="9%"><font size="1" face="Verdana, Arial,
Helvetica, sans-serif"><cfquery name="GetEmpName" datasource="#datasource#"
username="#username#" password="#password#" dbtype="ODBC">SELECT EmployeeID,
EmployeeFName FROM EmployeeManager WHERE EmployeeID =
#GetReport.EmployeeID#</cfquery>#GetEmpName.EmployeeFName#</font></td>
<td width="9%"><font size="1" face="Verdana, Arial,
Helvetica, sans-serif">#GetReport.TimeStarted#</font></td>
<td width="9%"><font size="1" face="Verdana, Arial,
Helvetica, sans-serif">#GetReport.TimeFinished#</font></td>
<td width="12%"><font size="1" face="Verdana, Arial,
Helvetica, sans-serif">#GetReport.TotalTime#</font></td>
</tr>
<tr bgcolor="##CCCCCC">
<td width="12%"><b><font size="1" face="Verdana, Arial,
Helvetica, sans-serif">Date</font></b></td>
<td width="24%"><b><font size="1" face="Verdana, Arial,
Helvetica, sans-serif">Project</font></b></td>
<td width="25%"><b><font size="1" face="Verdana, Arial,
Helvetica, sans-serif">Work Type</font></b></td>
<td width="9%"><b><font size="1" face="Verdana, Arial,
Helvetica, sans-serif">Staff</font></b></td>
<td width="9%"><b><font size="1" face="Verdana, Arial,
Helvetica, sans-serif">Start</font></b></td>
<td width="9%"><b><font size="1" face="Verdana, Arial,
Helvetica, sans-serif">End</font></b></td>
<td width="12%"><b><font size="1" face="Verdana, Arial,
Helvetica, sans-serif">Total</font></b></td>
</tr>
</table>
</td>
</tr>
</table>
</TD>
</TR>
</TABLE>
</BODY>
</HTML>
</cfmail>
Thanks,
Jay Patton
Web Design / Application Design
Web Pro USA
406.549.3337 ext. 203
1.888.5WEBPRO
www.webpro-usa.com
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
