Link to home
Start Free TrialLog in
Avatar of APS NZ
APS NZFlag for New Zealand

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.MDatabase#">
  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.MDatabase#">
  UPDATE #GetInfo.MTable#
  SET #GetInfo.MTable#.#I# = Mid([#Evaluate("GetInfo.MTable." & 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.
Avatar of _agx_
_agx_
Flag of United States of America image

You want to update the table with what kind of values: data from another query, FORM fields, ...?

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.MTable." & I)#],3,200)<br>
</CFLOOP>
Avatar of APS NZ

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
ASKER CERTIFIED SOLUTION
Avatar of _agx_
_agx_
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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#">
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.
>>>

   SET #GetInfo.MTable#.#I# = Mid([Evaluate(#GetInfo.MTable#.#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')
>  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>
@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
Avatar of APS NZ

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.