The query that Matt suggested works.
One problem however. CF chokes when there is a <NULL> value or an empty
field.
In the getRecords query, I can filter by using WHERE directoryPhone IS NOT
NULL.
But is there a way to filter out records that have an empty value ("")?
Mark
_____
From: Cornillon, Matthieu (Consultant)
[mailto:[EMAIL PROTECTED]
Sent: Thursday, September 02, 2004 1:58 PM
To: CF-Talk
Subject: RE: Looping Question
Mark,
The problem is that your loop index (memberID) is changing, but nothing
about the query is. You are repeatedly referring to getRecords.memberID,
which, without a CFOUTPUT or CFLOOP tag, will always just give you the first
record. In fact, I am surprised that you are doing anything other than
updating one record 3500 times, since getRecords.memberID will always pull
memberID from the first row of getRecords.
Two options for solving this.
1) Loop over the query:
<CFOUTPUT query="getRecords">
<CFQUERY name="updateRecords"...>
UPDATE #REQUEST.prefix#_Members_LIstBU1
SET directoryPhone = #Replace(getRecords.directoryPhone, "-",
"","ALL")#
WHERE memberID = #getRecords.memberID#
</cfquery>
</CFOUTPUT>
2) Keep your loop the same, but use array notation to get at the different
rows in the query:
<cfloop index="memberID" from="1" to="#getRecords.recordcount#">
<cfquery name="updateRecords" datasource="#REQUEST.dsnSQL#"
username="#REQUEST.dsnUID#" password="#REQUEST.dsnPWD#">
UPDATE #REQUEST.prefix#_Members_LIstBU1
SET directoryPhone =
#Replace(getRecords.directoryPhone[memberID], "-", "", "ALL")#
WHERE memberID = #getRecords.memberID[memberID]#
</cfquery>
</cfloop>
If you do option 2, I would change your index to something like "i" to avoid
confusion.
HTH,
Matthieu
<!--- Get a count of the records prior to insertion --->
<cfquery name="getRecords" datasource="#REQUEST.dsnSQL#"
username="#REQUEST.dsnUID#" password="#REQUEST.dsnPWD#">
SELECT memberID, directoryPhone
FROM #REQUEST.prefix#_Members_ListBU1
</cfquery>
Query Recordcount = <cfoutput>#getRecords.recordcount#</cfoutput><br />
Processing ...
<cfloop index="memberID" from="1" to="#getRecords.recordcount#">
<cfquery name="updateRecords" datasource="#REQUEST.dsnSQL#"
username="#REQUEST.dsnUID#" password="#REQUEST.dsnPWD#">
UPDATE #REQUEST.prefix#_Members_LIstBU1
SET directoryPhone = #Replace(getRecords.directoryPhone, "-", "",
"ALL")#
WHERE memberID = #getRecords.memberID#
</cfquery>
</cfloop>
Process Complete.
_____
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
