Problem with cfquery
Problem with cfquery
(OP)
I am having an issue and I think it is the server but want to make sure. I have changed the coding so much to minimalize what could be causing the error hence the simplistic queries. The output is perfect at the end and I can paste it into the sql online and it works. Is there a way on a coldfusion server to block the update from the server?
This is my code
This is the output when I block the update query otherwise I get an error with no info.
Which works fine.
This is my code
CODE --> coldfusion
<cfinclude template="security.cfm"> <cfif !isdefined("session.admin")> <cflocation url="denied.cfm"> </cfif> <cfquery name="get_cities" datasource="#request.dsn#"> SELECT * FROM state_cities WHERE yr2012 IS NULL LIMIT 2 </cfquery> <cfloop query="get_cities"> <cfquery name="get_county" datasource="#request.dsn#"> SELECT county FROM zip_code WHERE city='#get_cities.name#' AND state_prefix='#get_cities.state#' </cfquery> <cfquery name="get_2012" datasource="#request.dsn#"> SELECT *,((unemployed/labor_force)*100) AS unemppct FROM employment WHERE county LIKE '%#get_county.county# County, #get_cities.state#%' AND info_year='2012' </cfquery> <cfquery name="get_2011" datasource="#request.dsn#"> SELECT *,((unemployed/labor_force)*100) AS unemppct FROM employment WHERE county LIKE '%#get_county.county# County, #get_cities.state#%' AND info_year='2011' </cfquery> <cfquery name="get_2010" datasource="#request.dsn#"> SELECT *,((unemployed/labor_force)*100) AS unemppct FROM employment WHERE county LIKE '%#get_county.county# County, #get_cities.state#%' AND info_year='2010' </cfquery> <cfquery name="get_2009" datasource="#request.dsn#"> SELECT *,((unemployed/labor_force)*100) AS unemppct FROM employment WHERE county LIKE '%#get_county.county# County, #get_cities.state#%' AND info_year='2009' </cfquery> <cfquery name="get_2008" datasource="#request.dsn#"> SELECT *,((unemployed/labor_force)*100) AS unemppct FROM employment WHERE county LIKE '%#get_county.county# County, #get_cities.state#%' AND info_year='2008' </cfquery> <cfoutput> UPDATE state_cities SET yr2012='#get_2012.unemppct#',yr2011='#get_2011.unemppct#',yr2010='#get_2010.unemppct#',yr2009='#get_2009.unemppct#',yr2008='#get_2008.unemppct#' WHERE city_id=#get_cities.city_id# </cfoutput> <cfquery name="update_info" datasource="#request.dsn#"> UPDATE state_cities SET yr2012='#get_2012.unemppct#',yr2011='#get_2011.unemppct#',yr2010='#get_2010.unemppct#',yr2009='#get_2009.unemppct#',yr2008='#get_2008.unemppct#' WHERE city_id=#get_cities.city_id# </cfquery> </cfloop>
This is the output when I block the update query otherwise I get an error with no info.
CODE
UPDATE state_cities SET yr2012='4.80769230769',yr2011='5.76923076923',yr2010='5.94059405941',yr2009='6.06060606061',yr2008='3' WHERE city_id=5
Which works fine.
If you can't stand behind your troops, stand in front of them.
Semper Fidelis
Jim
RE: Problem with cfquery
If you can't stand behind your troops, stand in front of them.
Semper Fidelis
Jim
RE: Problem with cfquery
RE: Problem with cfquery
If you can't stand behind your troops, stand in front of them.
Semper Fidelis
Jim
RE: Problem with cfquery
If you can't stand behind your troops, stand in front of them.
Semper Fidelis
Jim
RE: Problem with cfquery
Having said all that, is there a reason you need to store the aggregates in a table, rather than doing it dynamically, like with a VIEW?
RE: Problem with cfquery
If you can't stand behind your troops, stand in front of them.
Semper Fidelis
Jim
RE: Problem with cfquery
> FROM employment WHERE county LIKE
Also, for data integrity, it is better to store the distinct counties in a separate table. Then have the "employment" table store the ID, rather than the name.