Thanks, Troy and Frank. Both of you were right on; very helpful
suggestions!
Forrest
==================
On 7/5/2011 2:10 AM, Frank Moorman wrote:
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
-------------------------------------------------------------
-------------------------------------------------------------
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
-------------------------------------------------------------
