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.
LVL 3
jdthedjAsked:
Who is Participating?
 
_agx_Connect With a Mentor Commented:
EDIT:

               > MFields is a comma separated list

So the list contains the names of all the columns in #MTable#?  And those columns are what contain the data with

                 "...a pattern like CHI200000/100000/2312 and I want to remove the CH."

?

If yes, you don't need evaluate. Try removing it ie

               UPDATE #GetInfo.MTable#
               SET #GetInfo.MTable#.#I# = Mid([#GetInfo.MTable#.#I#,3,200)

If that doesn't work, I need to see the actual values being generated.  Run this debug code inside your query loop and post the output:

           ....
         <CFSET MyFieldList = #GetInfo.MFields#>
          <CFLOOP List="#MyFieldList#" Index="I">
               <strong>Showing update for #MTable#</strong>:
               UPDATE #GetInfo.MTable#
               SET #GetInfo.MTable#.#I# = Mid([Evaluate(#GetInfo.MTable#.#I#)],3,200)    
              <br>
          </CFLOOP>
          ....
0
 
_agx_Commented:
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>
0
 
jdthedjAuthor Commented:
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
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Gurpreet Singh RandhawaWeb DeveloperCommented:
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#">
0
 
_agx_Commented:
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.
0
 
srikanthmadishettiCommented:
>>>

   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')
0
 
_agx_Commented:
>  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>
0
 
srikanthmadishettiCommented:
@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
0
 
jdthedjAuthor Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.