If you got an error message about the where statement, it may also mean that there is a problem in the query that ends with the where statement. So, it may not be the where clause that is causing the problem.
1) Are you updating the name in two different tables? What about normalization? 2) I tried using this query on some test data and finally got it to work. But I had to remove all apostrophes in my data. For instance, O'Bryan had to be changed to Obryan and Renee' had to be changed to Renee. Anyone know how to fix this? I tried preservesinglequotes, but that did not work. Zelda -----Original Message----- From: John Eubanks [mailto:[EMAIL PROTECTED] Sent: Thursday, February 08, 2007 12:01 PM To: CF-Newbie Subject: Re: Extreme Help Wilfred, Many thanks for the insight. I have it pulling everything through the form, but for some reason it gets stuck on the where clause: <cfloop from = "1" to = "#LoopCount#" index="ThisRow"> <cfquery name="Update" datasource="3iReps"> Update RepFinder_Basic.*,RepFinder_Ext.* Set RepFinder_Basic.FirstName = '#Evaluate("Form.FirstName" & ThisRow)#',RepFinder_Basic.LastName = '#Evaluate("Form.LastName" & ThisRow)#',RepFinder_Basic.Region = '#Evaluate("Form.Region" & ThisRow)#',RepFinder_Basic.RepId = '#Evaluate("Form.RepId" & ThisRow)#',RepFinder_Ext.FirstName = '#Evaluate("Form.FirstName" & ThisRow)#',RepFinder_Ext.LastName = '#Evaluate("Form.LastName" & ThisRow)#',RepFinder_Ext.Region = '#Evaluate("Form.Region" & ThisRow)#',RepFinder_Ext.RepId = '#Evaluate("Form.RepId" & ThisRow)#',RepFinder_Ext.Phone = '#Evaluate("Form.Phone" & ThisRow)#',RepFinder_Ext = '#Evaluate("Form.Ext" & ThisRow)#',RepFinder_BegZip = '#Evaluate("Form.BegZip" & ThisRow)#',EndZip = '#Evaluate("Form.EndZip" & ThisRow)#' WHERE RepFinder_Ext.BegZip = '#Evaluate("BegZip" & ThisRow)#' AND RepFinder_Ext.EndZip = '#Evaluate("EndZip" & ThisRow)#' </cfquery> </cfloop> >John, > >One, your query is not set up correctly. > >You need to have it like this. > >This is an example of the update statement in SQL. Not your query. > ><cfquery name="update" datasource="myDSN"> > update TableName > Set ColumnName1 = Value1, > ColumnName2 = Value2 > Where myId = passedID > </cfquery> > >Two, the form you have just before you do the update is set up that >each row on that form is supposed to be one record in the database. > >So, you need to dynamically name your fields in the form to be able to >group the rows together. > >Like so. > ><cfoutput query="getQuery"> > <input type="text" name="FirstName#CurrentRow#" >value="#getQuery.FirstName#"> > <input type="text" name="myId#CurrentRow#" value="#getQuery.id#"> ></cfoutput> > >You will need to know how many records you will be updating. So make a >hidden field to pass the recordcount, this is the easiest way. This >does not go inside the query loop. > ><input type="hidden" name="myLoopCount" value="#getQuery.recordcount#"> > >On the processing page it will something like this. > ><cfloop from="1" to="#Form.myLoopCount#" index="i"> > <cfquery name="doUpdate" datasource="myDSN"> > update TableName > set FirstName = '#Evaluate("Form.FirstName" & i)#' > where id = #Evaluate("myId" & i)# > </cfquery> ></cfloop> > >This should get you headed in the right direction. > >Chuck Weidler >Lockheed Martin Information Technology >3108 Lord Baltimore Drive >Baltimore, MD 21244 >Voice: (443) 348-2169 >Fax: (443) 348-2233 >Email: [EMAIL PROTECTED] > >-----Original Message----- >From: Greg Morphis [mailto:[EMAIL PROTECTED] >Sent: Thursday, February 08, 2007 9:18 AM >To: CF-Newbie >Subject: Re: Extreme Help > >That's your update query, however where's the query that shows the >information? On the previous page? >http://inside.3i-online.com/reps_test/GetReps_Query_Next.cfm?CFID=19835 >0 >2&CFTOKEN=22280223 > >Are you using <cflocation> ? > > >On 2/8/07, John Eubanks <[EMAIL PROTECTED]> wrote: >RepFinder_Basic.FirstName,RepFinder_Basic.LastName,RepFinder_Basic.Regi >o >n,RepFinder_Basic.Region,RepFinder_Basic.RepId,RepFinder_Ext.FirstName, >R >epFinder_Ext.LastName,RepFinder_Ext.Region,RepFinder_Ext.RepId,RepFinde r >_Ext.Phone,RepFinder_Ext.Ext,RepFinder_Ext.BegZip,RepFinder_Ext.EndZip >> Set RepFinder_Basic.FirstName = >'#Form.RepFirstName#',RepFinder_Basic.LastName = >'#Form.RepLastName#',RepFinder_Basic.Region = >'#Form.RepRegion#',RepFinder_Basic.RepId = >'#Form.RepId#',RepFinder_Ext.FirstName = >'#Form.RepFirstName#',RepFinder_Ext.LastName = >'#Form.RepLastName#',RepFinder_Ext.Region = >'#Form.RepRegion#',RepFinder_Ext.RepId = >'#Form.RepId#',RepFinder_Ext.Phone = '#Form.Phone#',RepFinder_Ext = >'#Form.Ext#',RepFinder_BegZip = '#Form.BegZip#',EndZip = >'#Form.EndZip#' >> WHERE RepFinder_Basic.RepId IN (#Form.RepId#) >> </cfquery> >> >> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Newbie/message.cfm/messageid:2506 Subscription: http://www.houseoffusion.com/groups/CF-Newbie/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.15
