E_ID='#RESOURCE_ID#'
If this is an integer field, it shouldn't have the single quotes around the
value.
Prepare to hear feedback on using "select *".
-----Original Message-----
From: Michael S. Kimmett [mailto:[EMAIL PROTECTED]]
Sent: Thursday, March 15, 2001 2:34 PM
To: CF-Talk
Subject: Nested Query
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
