How Can I update my Query?

[scripter73]

Hi,

I have a query result, CustData. I would like to update 2 fields in the query result, Acct Access, and Current . Since I know the names of these fields, I know that sometimes they are populated and sometimes not. I want to loop through my query result, and say, "If I'm on this particular column and its blank, set the value to 'no'".

Any ideas? Thanks in advance.

scripter73


Change Your Thinking, Change Your Life.

 

[r937]

do you want to change the query result in coldfusion, or do you want to update the actual row data in the database?

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[scripter73]

To actually change the query result, but I figured out how to do it:

<cfloop query="CustData">
<cfif AcctAccess eq "">
<cfset #AcctAccess[CurrentRow]# = "no">
</cfif>
<cfif Current eq "">
<cfset #Current[CurrentRow]# = "no">
</cfif>
</cfloop>

Hope this helps others and thanks for your response.

scripter73


Change Your Thinking, Change Your Life.

 

[TruthInSatire]

if you're doing this for an output you can do it while i'ts presented and skipp the other loops.

<cfoutput query = "myquery">
<tr>
<td><cfif myQuery.colName eq "">No</cfelse>#myQuery.colName</cfif>
</td>
</tr>
</cfoutput>

A common mistake that people make when trying to design something completely foolproof is to underestimate the ingenuity of complete fools.
-Douglas Adams (1952-2001)

 

[ecobb]

Not that it matters, but I've never been a big fan of [red]eq ""[/red]. If someone were to enter a 'space' or two in the field, then [red]eq ""[/red] may not work. For cases like this, I usually trim it and check the legnth. That way, even if there are spaces in it, it still registers as 'nothing' to CF. Also, you don't need the pound signs there.

<cfloop query="CustData">
 <cfif NOT Len(Trim(AcctAccess))>
    <cfset AcctAccess[CurrentRow] = "no">
 </cfif>
 <cfif NOT Len(Trim(Current))>
    <cfset Current[CurrentRow] = "no">
 </cfif>
</cfloop>

Hope This Helps!

Ecobb
Beer Consumption Analyst

"My work is a game, a very serious game." - M.C. Escher

 

[ecobb]

...Or do what bombboy said... (-:



Hope This Helps!

Ecobb
Beer Consumption Analyst

"My work is a game, a very serious game." - M.C. Escher

 

[TruthInSatire]

lol.
again ecobb?

Using trim() is a good thing.
i used to do this
<cfif trim(foo) eq "">

but i've been using
<cfif len(trim(foo))>
as ecobb suggested

either works. just whatever you'd rather see in your pages.

A common mistake that people make when trying to design something completely foolproof is to underestimate the ingenuity of complete fools.
-Douglas Adams (1952-2001)

 

[ecobb]

Man, we're getting good at that, Bombboy! I'm thinking about seeing if TT will issue a "Most Simultaneous Posts" award to us at the end of the year!



Hope This Helps!

Ecobb
Beer Consumption Analyst

"My work is a game, a very serious game." - M.C. Escher

 

[TruthInSatire]

I'm sure we'd take it. I used to post in busier forums than the CF here on TT and i haven't double posted nearly as much, especially with just one person. At least our posts are on the same page. we could be double posting with idiots...

A common mistake that people make when trying to design something completely foolproof is to underestimate the ingenuity of complete fools.
-Douglas Adams (1952-2001)