?
Solved

Problem with looping through a list and using the values in a query

Posted on 2012-08-26
9
Medium Priority
?
410 Views
Last Modified: 2012-08-27
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.
0
Comment
Question by:jdthedj
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 52

Expert Comment

by:_agx_
ID: 38335197
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
 
LVL 3

Author Comment

by:jdthedj
ID: 38335203
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
 
LVL 52

Accepted Solution

by:
_agx_ earned 2000 total points
ID: 38335227
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
Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

 
LVL 16

Expert Comment

by:Gurpreet Singh Randhawa
ID: 38335950
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
 
LVL 52

Expert Comment

by:_agx_
ID: 38336035
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
 
LVL 13

Expert Comment

by:srikanthmadishetti
ID: 38336497
>>>

   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
 
LVL 52

Expert Comment

by:_agx_
ID: 38337778
>  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
 
LVL 13

Expert Comment

by:srikanthmadishetti
ID: 38338084
@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
 
LVL 3

Author Closing Comment

by:jdthedj
ID: 38338235
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The technique is by far very Simple! How we can export the ColdFusion query results to DOC file?  Well before writing this I researched a lot in Internet but did not found a good Answer anyways!  So i thought now i should share my small snippet w…
PROBLEM: How to add your own buttons to the bottom toolbar with paging info ( result count ). While creating a cfgrid, I ran into an issue where I wanted to embed my own custom buttons where the default ones ( insert / delete / etc… ) are for aes…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question