APS NZ
asked on
Problem with looping through a list and using the values in a query
I am trying to update field data in several tables using a list of fieldnames and looping through the list.
This query works because I hard wire the field and value.
<CFQUERY Name="UpdateFields" Datasource="#GetInfo.MData base#">
UPDATE TempCommissions SET TempCommissions.IVal = Mid([IVal],3,200)
</CFQUERY>
This query doesn't work and tells me the field IVal is undefined on the right side of the evaluation.
<CFSET MyFieldList = #GetInfo.MFields#>
<CFLOOP List="#MyFieldList#" Index="I">
<CFQUERY Name="UpdateFields" Datasource="#GetInfo.MData base#">
UPDATE #GetInfo.MTable#
SET #GetInfo.MTable#.#I# = Mid([#Evaluate("GetInfo.MT able." & I)#],3,200)
</CFQUERY>
What am I doing wrong? I have tried just using Mid([#Evaluate(I)#],3,200) and that doesn't work either.
This query works because I hard wire the field and value.
<CFQUERY Name="UpdateFields" Datasource="#GetInfo.MData
UPDATE TempCommissions SET TempCommissions.IVal = Mid([IVal],3,200)
</CFQUERY>
This query doesn't work and tells me the field IVal is undefined on the right side of the evaluation.
<CFSET MyFieldList = #GetInfo.MFields#>
<CFLOOP List="#MyFieldList#" Index="I">
<CFQUERY Name="UpdateFields" Datasource="#GetInfo.MData
UPDATE #GetInfo.MTable#
SET #GetInfo.MTable#.#I# = Mid([#Evaluate("GetInfo.MT
</CFQUERY>
What am I doing wrong? I have tried just using Mid([#Evaluate(I)#],3,200)
ASKER
Thanks _agx_ I am trying to strip the first two letters from the data in various fields. The data in a field has a pattern like CHI200000/100000/2312 and I want to remove the CH.
I start with
<CFQUERY Name="GetInfo" datasource="Other#Sfx#">
SELECT * FROM CHI_Change
ORDER BY CNum
</CFQUERY>
<CFOUTPUT QUERY="GetInfo">
CHI_Change has the following fields
MDatabase
MTable
MFields
MFields is a comma separated list
I start with
<CFQUERY Name="GetInfo" datasource="Other#Sfx#">
SELECT * FROM CHI_Change
ORDER BY CNum
</CFQUERY>
<CFOUTPUT QUERY="GetInfo">
CHI_Change has the following fields
MDatabase
MTable
MFields
MFields is a comma separated list
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
if u just want to remove CH, you can actually do this
<cfset old = "CHI200000/100000/2312">
<cfset new = #RemoveChars(old,1,2)#>
<cfdump var="#new#">
<cfset old = "CHI200000/100000/2312">
<cfset new = #RemoveChars(old,1,2)#>
<cfdump var="#new#">
myselfrandhawa, sounds like he's trying to do the update in SQL which means he needs to use db functions, not CF functions.
> <cfset new = #RemoveChars(old,1,2)#>
Don't use "new" for a variable name because it's a reserved word in later versions. Also no need for the # signs there.
> <cfset new = #RemoveChars(old,1,2)#>
Don't use "new" for a variable name because it's a reserved word in later versions. Also no need for the # signs there.
>>>
SET #GetInfo.MTable#.#I# = Mid([Evaluate(#GetInfo.MTa ble#.#I#)] ,3,200)
You are trying to set a new value to column IVAL of MTable with modified current value of that column (Removing CH),
But when you are doing this Evaluate(#GetInfo.MTable#. #I#) coldfusion will check for Ival key available in GetInfo.MTable which is ur table name and throws error as it couldn't find that . Remember that this is your column name and not a Coldfusion variable so when you use evaluate to get it's value it will throw error as that variable is not available.
I guess you need to write inner query which can get that columns value
If Oracle you can use some thing like this
replace('123123tech', '123');
SET #GetInfo.MTable#.#I# = replace( (select #GetInfo.MTable#.#I# from #GetInfo.MTable# where ID = which you are going to use in your update statement),'CH')
SET #GetInfo.MTable#.#I# = Mid([Evaluate(#GetInfo.MTa
You are trying to set a new value to column IVAL of MTable with modified current value of that column (Removing CH),
But when you are doing this Evaluate(#GetInfo.MTable#.
I guess you need to write inner query which can get that columns value
If Oracle you can use some thing like this
replace('123123tech', '123');
SET #GetInfo.MTable#.#I# = replace( (select #GetInfo.MTable#.#I# from #GetInfo.MTable# where ID = which you are going to use in your update statement),'CH')
> If Oracle you can use some thing like this
> replace( ... , 'CH')
replace() is too broad. It removes "CH" anywhere in the string, not just at the beginning. You need to use a string function like MID, SUBSTRING, .... Exact syntax is db dependent. In MS SQL this query would remove "CH" from the beginning of the value.
UPDATE TableName
SET SomeColumn = SUBSTRING(SomeColumn, 3 LEN(SomeColumn)-2)
WHERE SomeColumn LIKE 'CH_%'
... in CF code
<CFLOOP List="#MyFieldList#" Index="colName">
<cfquery ....>
UPDATE #GetInfo.MTable#
SET #colName# = Substring( #colName#, 3, Len(#colName#)-2 )
WHERE #colName# LIKE 'CH_%' <=== value starts with "CH_" ....
</cfquery>
</CFLOOP>
> replace( ... , 'CH')
replace() is too broad. It removes "CH" anywhere in the string, not just at the beginning. You need to use a string function like MID, SUBSTRING, .... Exact syntax is db dependent. In MS SQL this query would remove "CH" from the beginning of the value.
UPDATE TableName
SET SomeColumn = SUBSTRING(SomeColumn, 3 LEN(SomeColumn)-2)
WHERE SomeColumn LIKE 'CH_%'
... in CF code
<CFLOOP List="#MyFieldList#" Index="colName">
<cfquery ....>
UPDATE #GetInfo.MTable#
SET #colName# = Substring( #colName#, 3, Len(#colName#)-2 )
WHERE #colName# LIKE 'CH_%' <=== value starts with "CH_" ....
</cfquery>
</CFLOOP>
@agx, correct but I gave replace just like an example , Mainly I was trying to explain why he was getting the CF error and more over that string looks like to have a specific format and I don't think it will have multiple CH in that string
ASKER
Thanks for all the replies, and sorry for the delay in replying - I got called out on an emergency and ran out of work time.
@_agx_: Taking out the Evaluate fixed it for me thanks.
@_agx_: Taking out the Evaluate fixed it for me thanks.
Add some debugging to output the sql generated. What do you get?
<CFSET MyFieldList = #GetInfo.MFields#>
<CFLOOP List="#MyFieldList#" Index="I">
<strong>Debug</strong>:
UPDATE #GetInfo.MTable#
SET #GetInfo.MTable#.#I# = Mid([#Evaluate("GetInfo.MT
</CFLOOP>