Apostrophe Has Been Replaced, Still Getting Error 1

[soho34]

Hi,

Trying to insert a record into my database with this:
NOTES is the field that will probably contain an apostrophe like the words: Here's

         <cfif IsDefined("notes")>
			<cfset notes = Replace(Fullname,"'","''")>
		</cfif>
			
		<cftransaction>
		<cfquery datasource="#request.dsn#">
		insert into packages
		(
		name,orderid,notes
		<cfif IsDefined("form.tempid")>
		,tempid
		</cfif>
		<cfif IsDefined("form.tempid")>
		,clientid
		</cfif>
		,createddatetime
		)
		values (
		'#name#',
		'#order#',
		'#notes#'
		<cfif IsDefined("form.tempid")>
		,'#tempid#'
		</cfif>
		<cfif IsDefined("form.tempid")>
		,'#clientid#'
		</cfif>		
		,#createodbcdatetime(request.nowzone)#
		)
		</cfquery>

Cold Fusion gets past the Replace, but still won't do my insert, because the error says:


Error Diagnostic Information
ODBC Error Code = 22001 (String data right truncation)


[Microsoft][ODBC SQL Server Driver][SQL Server]String or binary data would be truncated.


SQL = "insert into packages ( name,orderid,notes ,tempid ,clientid ,createddatetime ) values ( 'Anthony-9002 Package', '9002', '

Here''''s the letter sent from Q:



What am I doing wrong?

Thanks,
soho34

 

[soho34]

My bad. Don't worry about it. I think I needed to increase the size of the field.

Thanks anyway.

 

[TruthInSatire]

coldfusion will automatically change ' to '' if you do it before coldfusion does it will STILL do it and you'll end up with '''' which will write '' to the DB.

if you want to do the replace ' with '' yourself you'll have to use preservesinglequotes()

<cfset notes = Replace(Fullname,"'","''")>
<cfquery>
..
..
..
'#preserveSingleQuotes(notes)#'
...
..
..
</cfquery>

A common mistake that people make when trying to design something completely foolproof is to underestimate the ingenuity of complete fools.
-Douglas Adams (1952-2001)