Query not updating table

Page 1 of 1

2 Replies - 2841 Views - Last Post: 13 July 2012 - 07:57 AM Rate Topic: -----

#1 boby0701  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 11-July 12

Query not updating table

Posted 11 July 2012 - 09:05 AM

Hello,

I just got hired on as Admin for a small business and one of the job tasks they want me to do is maintain their coldfusion website. I have some background in programming HTML and C#, but no background in coldfusion or SQL.

I am trying to get a table on the website to update, it is supposed to gather sales numbers from a database and display it in columns by year. Right now it displays 2008, 2009, 2010, and 2012 data. I am trying to update it to display 2009, 2010, 2011, and 2012 data.

I have replaced all fields relating to 08,09,10 with 09,10,11, and have confirmed that the column names y2009, y2010, and y2011 are correct in the 2nd query.

The page isn't throwing any errors, the table just isn't updating to include the y2011 column data. I have run out of ideas. Maybe I am missing something in other parts of the code since I don't understand it all so well so I will include everything. 



<cfset securepage="sales"><cfinclude template="../includes/sls_securepage.cfm">

<cfparam name="theYear" default="#Year(Now())#">
<cfif IsDefined('URL.y') AND IsNumeric(URL.y)>
	<cfset theYear = Val(URL.y)>
</cfif>

<cfswitch expression="#theYear#">
	<cfcase value="2009">
		<cfset theField = "user_b">
	</cfcase>
	<cfcase value="2010">
		<cfset theField = "user_c">
	</cfcase>
	<cfcase value="2011">
		<cfset theField = "user_d">
	</cfcase>
	<cfdefaultcase>
		<cfset theField = "ytdsls">
	</cfdefaultcase>
</cfswitch>

<cfset title = title & " :: Top Customers in " & theYear>

<cfset maxrows = 500>
<cfset showall = True><!--- True | False --->
<cfset showbreaks = True><!--- True | False --->
	<cfset hit20percent = False><!--- False | True --->
	<cfset hit80percent = False><!--- False | True --->
	
<!--- Order by --->
<cfif IsDefined('URL.ob') AND Len(Trim(URL.ob))>
	<cfset orderby = Left(Trim(URL.ob),20)>
<cfelse>
	<cfset orderby = theField>
</cfif>

<!--- Order Direction --->
<cfif IsDefined('URL.od') AND Left(Trim(URL.od),4) EQ "ASC">
	<cfset orderdir = "ASC">
	<cfset lnkod = "DESC">
<cfelse>
	<cfset orderdir = "DESC">
	<cfset lnkod = "ASC">
</cfif>

<cfset errorArray = ArrayNew(1)>
<cfset dumpArray = ArrayNew(1)>

<div style="width: 550px; padding: 10px 0px 0px 5px;">
	<a href="sls_territory.cfm" class="lnkdark" style="float:right;">Return to summary</a>
	<span class="pgtitletxt"><cfoutput>#title#</cfoutput></span>
	<br />Top customers in 
		<cfoutput>
		<a href="sls_territory.cfm?pg=topcusts&y=2009" class="lnkdark" style="margin-left:10px;">2009</a>
		<a href="sls_territory.cfm?pg=topcusts&y=2010" class="lnkdark" style="margin-left:10px;">2010</a>
        <a href="sls_territory.cfm?pg=topcusts&y=2011" class="lnkdark" style="margin-left:10px;">2011</a>
		<a href="sls_territory.cfm?pg=topcusts" class="lnkdark" style="margin-left:10px;">#Year(Now())#</a>
		</cfoutput>
		
		<cfoutput>
		<a href="sls_territory.cfm?pg=topcusts_xls&od=#orderdir#&ob=#orderby#&print=y&y=#theYear#" class="lnkdark" style="float:right;">download</a>
		</cfoutput>
</div>
		
<cftry>
	<CFQUERY DATASOURCE="gcto1" result="getCust1_query" NAME="getCust1" maxrows="-1">
	SELECT custno, company, contact, address1, address2, city, state, zip, 
	adddate, phone, phone2, phonecell, faxno, indust, terr, salesmn, email, 
	balance, lastpay, ldate, lpymt, ytdsls, ptdsls, user_b, user_c, user_d
	FROM Customers
	WHERE (
		terr = <cfqueryparam value="#getTerr.terr#" cfsqltype="CF_SQL_VARCHAR" maxlength="5">
		AND 
		#theField# > 0
		)
	ORDER BY custno ASC
	</CFQUERY>
	<!--- 
	<cfset temp = StructInsert(getCust1_query, "Results", getCust1, true)>
	<cfset temp = ArrayAppend(dumpArray,getCust1_query)>
	<cfdump var="#getCust#" label="getCust" expand="false">
	 --->
	
	<CFQUERY dbtype="query" NAME="getCust" result="getCust_query">
	SELECT custno, company, contact, address1, address2, city, state, zip, 
	adddate, phone, phone2, phonecell, faxno, indust, terr, salesmn, email, 
	balance, lastpay, ldate, lpymt, ytdsls, ptdsls, ytdsls AS y#Year(Now())#, 
	user_b, user_b AS y2009, 
	user_c, user_c AS y2010, 
	user_d, user_d AS y2011
	FROM getCust1
	ORDER BY #orderby# #orderdir#<cfif orderby neq "custno">, custno #orderdir#</cfif>
	</CFQUERY>
	<!--- 
	<cfset temp = StructInsert(getCust_query, "Results", getCust, true)>
	<cfset temp = ArrayAppend(dumpArray,getCust_query)>
	<cfdump var="#getCust_query#" label="getCust_query" expand="false">
	 --->
	
	<!--- \\\ Search Results --->
	<cfif getCust.recordcount gt 0>
		<cfset terrtotal = Evaluate('ArraySum(ListToArray(ValueList(getCust.#theField#)))')>
		<cfset totpercent = 0>
	
		<div class="headerrow" style="display:block;width:770px;height:14px;color:black;text-decoration:none;margin-top:4px;">
			<!--- 
			<div style="float:right;width:300px; height:14px;text-align:right;overflow:hidden;">
				Total sold in <cfoutput>#getTerr.terr# in #Year(Now())#: #DollarFormat(terrtotal)#</cfoutput>
			</div>
			 --->
			<!--- Show how many records were found --->
			<div style="float:left;width:300px; height:14px;overflow:hidden;">
				<cfif getCust.recordcount eq maxrows>
					Displaying first <cfoutput>#maxrows#</cfoutput> records.
				<cfelse>
					Customers Found: <cfoutput>#getCust.recordcount#</cfoutput>
				</cfif>
			</div>
		</div>
		
		<!--- \\\ Search results header --->
		<cfset rowcolor="f2f2f2"><!--- ffffff | f2f2f2 --->
		<div class="headerrow" style="display:block;width:770px;height:14px;color:black;text-decoration:none;margin-top:4px;background-color: ###rowcolor#;">
			<div style="float:left;width:25px; height:14px;overflow:hidden;">&nbsp;</div>
			<div style="float:left;width:60px; height:14px;overflow:hidden;">Cust</div>
			<div style="float:left;width:185px;height:14px;overflow:hidden;">Company</div><!--- 115
			<div style="float:left;width:150px;height:14px;overflow:hidden;">Contact</div>
			<div style="float:left;width:60px; height:14px;overflow:hidden;">&nbsp;&nbsp;Add Date</div>
			<div style="float:left;width:60px; height:14px;overflow:hidden;">Last Sale</div>
			<div style="float:left;width:80px; height:14px;overflow:hidden;">Address</div>
			<div style="float:left;width:80px; height:14px;overflow:hidden;">City</div> --->
			<div style="float:left;width:30px; height:14px;overflow:hidden;">&nbsp;&nbsp;ST</div>
			<div style="float:left;width:50px; height:14px;overflow:hidden;">Indust</div><!--- 
			<div style="float:left;width:30px; height:14px;overflow:hidden;">Terr</div>
			<div style="float:left;width:60px; height:14px;overflow:hidden;">Lst Sale</div> --->
			<div style="float:left;width:80px; height:14px;overflow:hidden;text-align:right;"><a href="sls_territory.cfm?pg=topcusts&y=2009" class="lnkdark" style="color:black;">2009&nbsp;<img src="images/sortdesc.gif" style="border:0px;"/></a></div>
			<div style="float:left;width:80px; height:14px;overflow:hidden;text-align:right;"><a href="sls_territory.cfm?pg=topcusts&y=2010" class="lnkdark" style="color:black;">2010&nbsp;<img src="images/sortdesc.gif" style="border:0px;"/></a></div>
			<div style="float:left;width:80px; height:14px;overflow:hidden;text-align:right;"><a href="sls_territory.cfm?pg=topcusts&y=2011" class="lnkdark" style="color:black;">2011&nbsp;<img src="images/sortdesc.gif" style="border:0px;"/></a></div>
			<div style="float:left;width:80px; height:14px;overflow:hidden;text-align:right;"><a href="sls_territory.cfm?pg=topcusts&y=<cfoutput>#Year(Now())#</cfoutput>" class="lnkdark" style="color:black;"><cfoutput>#Year(Now())#</cfoutput>&nbsp;<img src="images/sortdesc.gif" style="border:0px;"/></a></div>
			<div style="float:left;width:50px; height:14px;overflow:hidden;text-align:right;">Cust %</div>
			<div style="float:left;width:50px; height:14px;overflow:hidden;text-align:right;">Total %</div>
		</div>
		<!--- /// Search results header --->
		
		<!--- \\\ Search results scroll area --->
		<cfif IsDefined('URL.print') AND URL.Print eq "y">
		<div id="custSearchResults" style="width:790px;background-color:white;border-top:1px solid #cacaca;border-bottom:1px solid #cacaca;border-left:1px solid #cacaca;">
		<cfelse>
		<div id="custSearchResults" style="width:790px;height:400px;overflow:scroll;overflow-x: hidden;overflow-y: scroll;background-color:white;border-top:1px solid #cacaca;border-bottom:1px solid #cacaca;border-left:1px solid #cacaca;">
		</cfif>
		
		<cfset tot2009 = 0>
		<cfset tot2010 = 0>
		<cfset tot2011 = 0>
		<cfset totysls = 0>
		<cfloop query="getCust">
		<cfoutput>
			<cfset tot2009 = tot2009 + Val(y2009)>
			<cfset tot2010 = tot2010 + Val(y2010)>
			<cfset tot2011 = tot2011 + Val(y2011)>
			<cfset totysls = totysls + Val(Evaluate('y#Year(Now())#'))>
			
			<cfset custpercent = IIF(Val(Evaluate(theField)) gt 0, '#Evaluate(Val(Evaluate(theField))/Val(terrtotal))*100#',DE('0'))>
			<cfset totpercent = totpercent + custpercent>
			
	  		<cfif rowcolor eq "e5e8ff"><cfset rowcolor="f2f2f2"><cfelse><cfset rowcolor="e5e8ff"></cfif>
			<div class="headerrow" style="width:770px;height:14px;margin-top:4px;background-color: ###rowcolor#;">
				<a href="sls_showcust.cfm?custnum=#Trim(custno)#" style="width:770px;height:20px;color:black;text-decoration:none;">
					<div style="float:left;width:25px; height:14px;overflow:hidden;white-space:nowrap;">#getCust.currentrow#&nbsp;</div>
					<div style="float:left;width:60px; height:14px;overflow:hidden;white-space:nowrap;">#Trim(custno)#&nbsp;</div>
					<div style="float:left;width:185px;height:14px;overflow:hidden;white-space:nowrap;">#Trim(Company)#&nbsp;</div><!--- 115
					<div style="float:left;width:150px;height:14px;overflow:hidden;white-space:nowrap;">#Trim(Contact)#&nbsp;</div>
					<div style="float:left;width:60px; height:14px;overflow:hidden;white-space:nowrap;">&nbsp;&nbsp;#DateFormat(adddate,'mm/dd/yy')#</div>
					<div style="float:left;width:60px; height:14px;overflow:hidden;white-space:nowrap;">#DateFormat(ldate,'mm/dd/yy')#</div>
					<div style="float:left;width:80px; height:14px;overflow:hidden;white-space:nowrap;">#Trim(Address1)#&nbsp;</div>
					<div style="float:left;width:80px; height:14px;overflow:hidden;white-space:nowrap;">&nbsp;#Trim(City)#</div> --->
					<div style="float:left;width:30px; height:14px;overflow:hidden;white-space:nowrap;">&nbsp;&nbsp;#Trim(state)#</div>
					<div style="float:left;width:50px; height:14px;overflow:hidden;white-space:nowrap;">#Trim(Indust)#&nbsp;</div><!--- 
					<div style="float:left;width:30px; height:14px;overflow:hidden;white-space:nowrap;">#Trim(Terr)#&nbsp;</div>
					<div style="float:left;width:60px; height:14px;overflow:hidden;white-space:nowrap;">#DateFormat(ldate,'mm/dd/yyyy')#</div> --->
					<div style="float:left;width:80px; height:14px;overflow:hidden;white-space:nowrap;text-align:right;">#DollarFormat(Val(y2009))#&nbsp;</div>
					<div style="float:left;width:80px; height:14px;overflow:hidden;white-space:nowrap;text-align:right;">#DollarFormat(Val(y2010))#&nbsp;</div>
					<div style="float:left;width:80px; height:14px;overflow:hidden;white-space:nowrap;text-align:right;">#DollarFormat(Val(y2011))#&nbsp;</div>
					<div style="float:left;width:80px; height:14px;overflow:hidden;white-space:nowrap;text-align:right;">#DollarFormat(Val(ytdsls))#&nbsp;</div>
					<div style="float:left;width:50px; height:14px;overflow:hidden;white-space:nowrap;text-align:right;">#DecimalFormat(Val(custpercent))#</div>
					<div style="float:left;width:50px; height:14px;overflow:hidden;white-space:nowrap;text-align:right;">#DecimalFormat(Val(totpercent))#&nbsp;</div>
				</a>
			</div><!--- 
			<cfif Val(totpercent) gte 20 AND NOT hit20percent>
				<cfset hit20percent = TRUE>
				<cfif showbreaks>
					<div class="headerrow" style="width:770px;height:14px;margin-top:4px;background-color: gray;">
						<div style="float:left;width:770px;height:14px;overflow:hidden;white-space:nowrap;">&nbsp;</div>
					</div>
				</cfif>
			</cfif> --->
			<cfif Val(totpercent) gte 80 AND NOT hit80percent>
				<cfif NOT showall><cfbreak></cfif>
				<cfset hit80percent = TRUE>
				<cfif showbreaks>
					<div class="headerrow" style="width:770px;height:14px;margin-top:4px;background-color: blue;">
						<div style="float:left;width:770px;height:14px;overflow:hidden;white-space:nowrap;">&nbsp;</div>
					</div>
				</cfif>
			</cfif>
		</cfoutput>
		</cfloop>
		</div>
		<!--- /// Search results scroll area --->
		<cfoutput>
		<div class="headerrow" style="width:770px;height:14px;margin-top:4px;background-color: white;color:black;">
			<div style="float:left;width:350px;height:14px;overflow:hidden;white-space:nowrap;text-align:right;">Totals:&nbsp;</div>
			<div style="float:left;width:80px; height:14px;overflow:hidden;white-space:nowrap;text-align:right;">#DollarFormat(Val(tot2009))#&nbsp;</div>
			<div style="float:left;width:80px; height:14px;overflow:hidden;white-space:nowrap;text-align:right;">#DollarFormat(Val(tot2010))#&nbsp;</div>
			<div style="float:left;width:80px; height:14px;overflow:hidden;white-space:nowrap;text-align:right;">#DollarFormat(Val(tot2011))#&nbsp;</div>
			<div style="float:left;width:80px; height:14px;overflow:hidden;white-space:nowrap;text-align:right;">#DollarFormat(Val(totysls))#&nbsp;</div>
		</div>
		</cfoutput>
	<cfelse>
		<br />No records found.<br /><br />
	</cfif>
	<!--- /// Search Results --->
	
	<cfcatch>
		<!--- Custom search error --->
		<cfdump var="#cfcatch#" label="cfcatch_getCust" expand="true">
	</cfcatch>
</cftry>
<!--- 
<cfoutput>
<br />terrtotal: #terrtotal#
</cfoutput>
 --->
<p style="font-size:9px;color:#cacaca;">
	This report is based on the customer's year-to-date sales only.
</p>


Is This A Good Question/Topic? 0
  • +

Replies To: Query not updating table

#2 Craig328  Icon User is offline

  • I make this look good
  • member icon

Reputation: 1546
  • View blog
  • Posts: 2,990
  • Joined: 13-January 08

Re: Query not updating table

Posted 13 July 2012 - 06:41 AM

Welcome to DIC boby0701!

So, that's an awful lot of code for someone to pore through to try and discern your issue. The first question that needs answering though is: did your getcust query bring back 2011 data (user_d, y2011). Seeing as that's what you're trying to output later, it makes sense to see if you brought any back.

The best way to do that would be to do a CFDUMP of the getcust query right after the query closes on line 94. This will display on the screen so if this is a live site, you'll want to put it in, run it once and quickly take it back out or put a cfif block around it checking for your logged in ID in order to run it. I notice you're giving the query result set a different name. I'm not a big fan of that unless you're needing info on the query performance (I did see your commented out cfdump on line 98) and I'd suggest you just do the dump of the named query.

If you do see a column entitled y2011 (because that's what you use later to build tot2011) then at least you know your query is doing the job and the issue is likely further down where you're compiling the totals.

Basically, the way to tackle an issue like you're having is to line step through it confirming what you're code assumes is working. Do a bunch of temporary outputs and dumps as you go and eventually you'll find the line that's giving you trouble.

Good luck!
Was This Post Helpful? 0
  • +
  • -

#3 boby0701  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 11-July 12

Re: Query not updating table

Posted 13 July 2012 - 07:57 AM

Thanks for the input, I re-enabled that cfdump and here is the result, it appears that y2011 is pulled, it just is not compiling the data, do you have any ideas on where to begin? I really am quite unfamiliar with web coding.




getCust_query - struct
CACHED false
COLUMNLIST ADDDATE,ADDRESS1,ADDRESS2,BALANCE,CITY,COMPANY,CONTACT,CUSTNO,EMAIL,FAXNO,INDUST,LASTPAY,LDATE,LPYMT,PHONE,PHONE2,PHONECELL,PTDSLS,SALESMN,STATE,TERR,USER_B,USER_C,USER_D,Y2009,Y2010,Y2011,Y2012,YTDSLS,ZIP
EXECUTIONTIME 16
RECORDCOUNT 256
Results
getCust_query - query - Top 256 of 256 Rows
SQL SELECT custno, company, contact, address1, address2, city, state, zip, adddate, phone, phone2, phonecell, faxno, indust, terr, salesmn, email, balance, lastpay, ldate, lpymt, ytdsls, ptdsls, ytdsls AS y2012, user_b, user_b AS y2009, user_c, user_c AS y2010, user_d, user_d AS y2011 FROM getCust1 ORDER BY ytdsls DESC, custno DESC
Was This Post Helpful? 0
  • +
  • -
Page 1 of 1