Bruce -

Not sure if you found a solution yet... I would strongly recommend getting away from cfqueryparam's. They seem to be driver (version-wise) dependant in many cases. In other words, they sometimes mis-interpret what is being passed through them. It's a lot more stable (in my experience) to build your own SQL statements from the ground up and not to use and CF tags within the cfquery tag. This way you can control exactly what is going to be passed through the driver (as long as the block size is not limiting you somehow - which it can in the case of a large amount of text...).

Another solution is to use stored procedures. In addition to providing (in most cases) another level of protection between your data and the user, they are usually faster (unless there is a ton of dynamically evaluated SQL in the script - usually this will not be the case, though), and they seem to be pretty flexible about the driver on the CF server not being exactly up to date with the version on the SQL server itself. But, using this option requires that you learn the nuances of stored procs, and in the case of most sites, it is usually not worth the hassle. If it's a query which will be run thousands of times a day, it will probably cut many seconds off the CPU / disk time of your db server.

I think going with the first option of getting away from using tags within the cfquery tag is probably your best bet.


From: "Bruce Phillips" <[EMAIL PROTECTED]>
Reply-To: [EMAIL PROTECTED]
To: <<[EMAIL PROTECTED]>>
Subject: [KCFusion] Quotation Marks in Form Field Value
Date: Tue, 24 Jun 2003 13:28:40 -0500

I need to have quotation marksinserted into fields in our SQL Server
2000 tables when the user types them into the form field.  However, what
now happens is SQL Server 2000 cuts off the the text the user typed into
the form field when it reaches the first quotation mark.  My code for
the insert is below.

I believe this has something to do with quotation marks normally being
used to surround the text that should be inserted.  How do I make SQL
Server 2000 include the quotation marks users type into the text box?

Thanks for the help.

Bruce

<cfquery  datasource="#dsn#" name="insertcat">
 update categories
 set catname_internal = <cfqueryparam cfsqltype="CF_SQL_VARCHAR"
value="#form.CATNAME_Internal#">,
 catname_display = <cfqueryparam cfsqltype="CF_SQL_VARCHAR"
value="#form.CATNAME_Display#">,
 CATRequireProposal=<cfif isdefined("form.CATRequireProposal")>
  <cfqueryparam cfsqltype="CF_SQL_BIT" value="1">
 <cfelse>
  <cfqueryparam cfsqltype="CF_SQL_BIT" value="0">
 </cfif>,
 CATPurpose = <cfqueryparam cfsqltype="CF_SQL_VARCHAR"
value="#form.CATPurpose#">,
 CATTime = <cfqueryparam cfsqltype="CF_SQL_VARCHAR"
value="#form.CATTime#">,
CATAttendance = <cfqueryparam cfsqltype="CF_SQL_VARCHAR"
value="#form.CATAttendance#">,
 CATSubmission = <cfqueryparam cfsqltype="CF_SQL_VARCHAR"
value="#form.CATSubmission#">,
CATUploadPapers=<cfif isdefined("form.CATUploadPapers")>
  <cfqueryparam cfsqltype="CF_SQL_BIT" value="1">
 <cfelse>
  <cfqueryparam cfsqltype="CF_SQL_BIT" value="0">
 </cfif>
 <cfif len(form.callforpaperfile)>
 ,CatAcceptEmail=<cfqueryparam cfsqltype="CF_SQL_VARCHAR"
value="#file.serverfile#">
 </cfif>
 where catid = <cfqueryparam cfsqltype="CF_SQL_INTEGER"
value="#form.catid#">
 </cfquery>

Bruce Phillips
Society of Teachers of Family Medicine
913-906-6000 ext 5405
[EMAIL PROTECTED]


_________________________________________________________________
Protect your PC - get McAfee.com VirusScan Online http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963




______________________________________________________________________
The KCFusion.org list and website is hosted by Humankind Systems, Inc.
List Archives........ http://www.mail-archive.com/[EMAIL PROTECTED]
Questions, Comments or Glowing Praise.. mailto:[EMAIL PROTECTED]
To Subscribe.................... mailto:[EMAIL PROTECTED]
To Unsubscribe................ mailto:[EMAIL PROTECTED]



Reply via email to