I am having the worst time trying to figure out why the last query( See Below ) in
this chain of if statements is not working. The error that I am getting from the
CFServer when I try to execute the last query is:
Message: ODBC Error Code = 37000 (Syntax error or access violation)
Detail: [Microsoft][ODBC SQL Server Driver][SQL Server]Error converting data type
varchar to numeric.
The query is being populated from a form and all the other queries are working except
for the last one (However all the queries are working in the MS Query Analyzer when
hard coded with data). I have done a <CFOUTPUT> to check what type of data is being
input into the query, but the data is correct! Anyone have an idea as to why this
last query is not working?
Thanks in advance.
Michael
<!--- THE QUERY --->
<CFIF isDefined("RESOURCE_ID") AND RESOURCE_ID IS NOT "">
<CFIF isDefined("PROJECT_ID") AND PROJECT_ID IS NOT "">
<CFIF isDefined("OPEN_DATE") AND OPEN_DATE IS NOT "">
<CFIF isDefined("CLOSE_DATE") AND CLOSE_DATE IS NOT "">
<CFQUERY name="SearchByName"
datasource="#request.site.ODBC_DataSource#"
dbtype="ODBC"
username="#request.site.ODBC_UserName#"
password="#request.site.ODBC_UserPassword#"
cachedwithin="#CreateTimeSpan(0,0,15,0)#">
SELECT * FROM CMD_CTR, ACT_ITEM, EMPLOYEE
WHERE CMD_CTR.ACTION_ID=ACT_ITEM.ACTION_ID
AND CMD_CTR.E_ID=EMPLOYEE.E_ID
AND CMD_CTR.E_ID='#RESOURCE_ID#'
AND CMD_CTR.P_ID='#PROJECT_ID#'
AND ACT_ITEM.OPEN_DATE='#OPEN_DATE#'
AND ACT_ITEM.CLOSE_DATE='#CLOSE_DATE#'
</CFQUERY>
<CFELSE>
<CFQUERY name="SearchByName"
datasource="#request.site.ODBC_DataSource#"
dbtype="ODBC"
username="#request.site.ODBC_UserName#"
password="#request.site.ODBC_UserPassword#"
cachedwithin="#CreateTimeSpan(0,0,15,0)#">
SELECT * FROM CMD_CTR, ACT_ITEM, EMPLOYEE
WHERE CMD_CTR.ACTION_ID=ACT_ITEM.ACTION_ID
AND CMD_CTR.E_ID=EMPLOYEE.E_ID
AND CMD_CTR.E_ID='#RESOURCE_ID#'
AND CMD_CTR.P_ID='#PROJECT_ID#'
AND ACT_ITEM.OPEN_DATE='#OPEN_DATE#'
</CFQUERY>
</CFIF> <!--- END OF CFIF FOR CLOSE DATE --->
<CFELSE>
<CFQUERY name="SearchByName"
datasource="#request.site.ODBC_DataSource#"
dbtype="ODBC"
username="#request.site.ODBC_UserName#"
password="#request.site.ODBC_UserPassword#"
cachedwithin="#CreateTimeSpan(0,0,15,0)#">
SELECT * FROM CMD_CTR, ACT_ITEM, EMPLOYEE
WHERE CMD_CTR.ACTION_ID=ACT_ITEM.ACTION_ID
AND CMD_CTR.E_ID=EMPLOYEE.E_ID
AND CMD_CTR.E_ID='#RESOURCE_ID#'
AND CMD_CTR.P_ID='#PROJECT_ID#'
</CFQUERY>
</CFIF> <!--- END OF CFIF FOR OPEN DATE --->
<CFELSE>
<!---THIS IS THE QUERY THAT IS NOT WORKING! --->
<CFQUERY name="SearchByName"
datasource="#request.site.ODBC_DataSource#"
dbtype="ODBC"
username="#request.site.ODBC_UserName#"
password="#request.site.ODBC_UserPassword#"
cachedwithin="#CreateTimeSpan(0,0,15,0)#">
SELECT * FROM CMD_CTR
WHERE
E_ID='#RESOURCE_ID#'
</CFQUERY>
</CFIF><!--- END OF CFIF FOR PROJECT_ID --->
<CFELSE>
<CFSET errmess = "You did not enter any information to search by.">
<CFLOCATION URL="index.cfm?fuseaction=error&message=#errmess#">
</CFIF><!--- END OF CFIF FOR RESOURCE_ID --->
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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
