Even better, don't forget to use <cfqueryparam>...
<cfquery name="insQuery" datasource="myDSN"> UPDATE tableName SET institution = <cfqueryparam value="#newVarName#" cfsqltype="CF_SQL_VARCHAR"> </cfquery> It means typing more, but it provides safety against SQL injection attacks. You should always use it just for this reason alone. In addition, with most databases, using <CFQUERYPARAM> creates a "Prepared SQL" which caches the execution plan (on the db side.) This allows for better performance when using the same SQL multiple times. Note: you need to change the cfsqltype as appropriate. You will no longer need single tic-marks around strings with cfqueryparam. All variable data should use cfqueryparam; but constants within the sql statement may perform quicker without it. On 07/05/2011 01:18 AM, Troy Jones wrote: > I think it's something to do with a conflict with trying to reset a query > object value. Try setting the value to a variable with a different name and > inserting the new value. > > <cfoutput query='queryname'> > <cfset newVarName = [your calculated string based on the replaced value of > institution]> > <cfquery name="insQuery" datasource="myDSN"> > UPDATE tableName > SET institution = '#newVarName#' > </cfquery> > </cfoutput> > > or something to that effect. Maybe you've already tried it but that's what > comes to mind. > > Going to bed, I'll check email in the morning to see if you've had any > success. > > Best of luck! > > > ___________________________________________________________________________________________ > > Troy Jones | Developer/Support Technician | Dynapp Inc | 1-800-830-5192 > ext. 603 | dynapp.com | facebook.com/dynapp > > > -----Original Message----- > From: ad...@acfug.org [mailto:ad...@acfug.org] On Behalf Of Forrest C Gilmore > Sent: Tuesday, July 05, 2011 1:04 AM > To: discussion@acfug.org > Subject: Re: [ACFUG Discuss] Update Query Problem > > Yes. I have discovered I must use > > <cfset queryname.institution = newString(value)> > > in order to get Institution to be set to the new value, but I still cannot > get that value to update the database record. > > Forrest > =========================== > > On 7/5/2011 12:46 AM, Troy Jones wrote: >> I'm guessing in your output loop, you are trying to revalue institution with >> a new value i.e.<cfset queryname.institution = newString(value)>? >> >> >> ___________________________________________________________________________________________ >> >> Troy Jones | Developer/Support Technician | Dynapp Inc | >> 1-800-830-5192 ext. 603 | dynapp.com | facebook.com/dynapp >> >> -----Original Message----- >> From: ad...@acfug.org [mailto:ad...@acfug.org] On Behalf Of Forrest C Gilmore >> Sent: Tuesday, July 05, 2011 12:42 AM >> To: ACFUG Discussion >> Subject: [ACFUG Discuss] Update Query Problem >> >> I have a problem which seems like it should have a simple answer, but I >> cannot seem to find it! >> >> I have a Coldfusion datasource using a MS Access table with a field >> named Institution that can have multiple values. I need to replace some >> of the values in this field with new values. For example, everywhere the >> Value A occurs, I want to replace it with ABC, so that if Institution = >> 'A', it will become 'ABC', and if Institute= 'A, G' it will become 'ABC, G'. >> >> I am able to use the ColdFusion Replace function to create a new string >> containing the proper replacement value, but I cannot seem to replace >> the records from the database with this new string. I've tried CFUpdate >> as well as the SQL Update, but neither seems to work. >> >> Using CFQuery, I pull the ID and Institution values from the records I >> want to update from the database. Inside a CFOutput loop based on the >> query, I then use Replace to compute the NewString that will replace the >> old value of Institution. But CFSet will not cause Institution to take >> on this new value, so CFUpdate does not work, and if I try to use the >> standatd SQL Update statement inside a CFQuery, it throws a syntax error. >> >> What am I missing here? >> >> Forrest C. Gilmore >> >> >> >> >> ------------------------------------------------------------- >> To unsubscribe from this list, manage your profile @ >> http://www.acfug.org?fa=login.edituserform >> >> For more info, see http://www.acfug.org/mailinglists >> Archive @ http://www.mail-archive.com/discussion%40acfug.org/ >> List hosted by http://www.fusionlink.com >> ------------------------------------------------------------- >> >> >> >> >> >> ------------------------------------------------------------- >> To unsubscribe from this list, manage your profile @ >> http://www.acfug.org?fa=gin.edituserform >> >> For more info, see http://www.acfug.org/mailinglists >> Archive @ http://www.mail-archive.com/discussion%40acfug.org/ >> List hosted by http://www.fusionlink.com >> ------------------------------------------------------------- >> >> >> > > > ------------------------------------------------------------- > To unsubscribe from this list, manage your profile @ > http://www.acfug.org?fa=login.edituserform > > For more info, see http://www.acfug.org/mailinglists > Archive @ http://www.mail-archive.com/discussion%40acfug.org/ > List hosted by http://www.fusionlink.com > ------------------------------------------------------------- > > > > > > ------------------------------------------------------------- > To unsubscribe from this list, manage your profile @ > http://www.acfug.org?fa=gin.edituserform > > For more info, see http://www.acfug.org/mailinglists > Archive @ http://www.mail-archive.com/discussion%40acfug.org/ > List hosted by http://www.fusionlink.com > ------------------------------------------------------------- > > > > ------------------------------------------------------------- To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by http://www.fusionlink.com -------------------------------------------------------------
