stewatts
asked on
Performing Calculations With CF
Hi
I am trying to update a value in CF by adding 1 to it (or possibly performing other calculations on he value.
The value in the Database is currently 2000 and I am using the following code:
<cfquery name="MyQuery" DATASOURCE="Data">
SELECT * FROM MyTableTBL
</cfquery>
<CFOUTPUT QUERY="MyQuery">
<cfset Claim_Ref=Claim_Ref+'1'>
#claim_ref#
</CFOUTPUT>
The returned value is still only 2000 when it should be 2001, any ideas what I am doing wrong. Sorry I know this is probably simple
Thanks
I am trying to update a value in CF by adding 1 to it (or possibly performing other calculations on he value.
The value in the Database is currently 2000 and I am using the following code:
<cfquery name="MyQuery" DATASOURCE="Data">
SELECT * FROM MyTableTBL
</cfquery>
<CFOUTPUT QUERY="MyQuery">
<cfset Claim_Ref=Claim_Ref+'1'>
#claim_ref#
</CFOUTPUT>
The returned value is still only 2000 when it should be 2001, any ideas what I am doing wrong. Sorry I know this is probably simple
Thanks
You are going to have to create a new variable instead of set Claim_Ref
<cfset nClaim_Ref=Claim_Ref+'1'>
tim_cs is right...
The reason it doesn't work is because the value of Claim_Ref is coming from the database, and you can't overwrite the query value inside of the OUTPUT loop, so a new variable is the best option.
You can set the scope of your variable like this to avoid problems also:
<CFOUTPUT QUERY="MyQuery">
<cfset Variables.Claim_Ref = VAL(Claim_Ref) + 1>
#Variables.claim_ref#
</CFOUTPUT>
The reason it doesn't work is because the value of Claim_Ref is coming from the database, and you can't overwrite the query value inside of the OUTPUT loop, so a new variable is the best option.
You can set the scope of your variable like this to avoid problems also:
<CFOUTPUT QUERY="MyQuery">
<cfset Variables.Claim_Ref = VAL(Claim_Ref) + 1>
#Variables.claim_ref#
</CFOUTPUT>
<CFOUTPUT QUERY="MyQuery">
<cfset Claim_Ref_new =i ncrementvalue(Claim_Ref)>
#Claim_Ref_new#
</CFOUTPUT>
<cfset Claim_Ref_new =i ncrementvalue(Claim_Ref)>
#Claim_Ref_new#
</CFOUTPUT>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Exactly what mrichmon said...I just wanted to elaborate on is a small bit.
When you loop through a query with cfoutput when you use a variable's value (output it in some cases) it will default to the query's scope. #claim_ref# is the same as #myquery.claim_ref# this is equally true when using it in cfset when you use the variable claim_ref its defaulted scope is the query's scope. When you set a variables value it defaults to the variable scope. A better option than trying to remeber any of what I just said is to always scope your variables.
When you loop through a query with cfoutput when you use a variable's value (output it in some cases) it will default to the query's scope. #claim_ref# is the same as #myquery.claim_ref# this is equally true when using it in cfset when you use the variable claim_ref its defaulted scope is the query's scope. When you set a variables value it defaults to the variable scope. A better option than trying to remeber any of what I just said is to always scope your variables.
Note that MyQuery.Claim_Ref always references the first row, unless you add [currentrow] like this:
<cfoutput query="MyQuery">
<cfset nClaim_Ref = MyQuery.Claim_Ref[currentr
#nClaim_Ref#
</cfoutput>
If you don't want to use an additional variable, there's a function called querySetCell which works like this:
<cfoutput query="MyQuery">
<cfset querySetCell(MyQuery, "Claim_Ref", MyQuery.Claim_Ref[currentr
#Claim_Ref#
</cfoutput>
Then there might be a third, often overlooked option: the database:
select Claim_Ref + 1 as Claim_Ref from tablename
I'm not sure whether you can use it in this case, since it can't be told from the code you gave. But if you can use the database it usually is faster.
<cfoutput query="MyQuery">
<cfset nClaim_Ref = MyQuery.Claim_Ref[currentr
#nClaim_Ref#
</cfoutput>
If you don't want to use an additional variable, there's a function called querySetCell which works like this:
<cfoutput query="MyQuery">
<cfset querySetCell(MyQuery, "Claim_Ref", MyQuery.Claim_Ref[currentr
#Claim_Ref#
</cfoutput>
Then there might be a third, often overlooked option: the database:
select Claim_Ref + 1 as Claim_Ref from tablename
I'm not sure whether you can use it in this case, since it can't be told from the code you gave. But if you can use the database it usually is faster.
Tell me you just overlooked my answer, have you? :)
With only 25 points to the question the points can only be awarded to one answer - not split. In taht case the points are awarded to the first correct answer. If there had been more points I would have definately recommended a split with your answer as it is also helpful.
<cfset Claim_Ref=incrementvalue(C
#claim_ref#
</CFOUTPUT>