[AccessD] Escaping a Single Quotation Mark in a pass through update query

Gustav,
Doubling is what is needed

(Doubling was one of the first things I did - but somehow I 
convinced myself it didn't work - and I never went back to 
check again - one of those moments sshhhh!)

I tested and found that CHAR(13) is adequate to cause a line 
break if you paste the contents of the nvarchar(max) field 
into a Word document, whereas for a line break to appear 
when viewing the column as a memo field in Access; and 
similar if pasting contents into a .txt file you require the 
CHAR(13) + CHAR(10) in order to get a line break. 

Thanks!

borge

---- Original message ----
>Date: Wed, 16 Jan 2008 11:49:37 +0100
>From: "Gustav Brock" <Gustav at cactus.dk>  
>Subject: Re: [AccessD] Escaping a Single Quotation Mark in 
a pass	through update query  
>To: <accessd at databaseadvisors.com>
>
>Hi Borge
>
>You may have to double the single quotes:
>
>  strLog = "Houston - We've got massive problems!"
>  strLog = Replace(strLog, "'", "''")
>
>By the way, wouldn't you need a CR+LF and not just a CR 
here:
>
>SET ExceptionLog = '" & strLog & "' + CHAR(13) + CHAR(10) +
>
>/gustav
>
>>>> pcs at azizaz.com 16-01-2008 11:21:24 >>>
>Someone please help:
>
>I want to update a column with a string that contains a 
>Single Quotation Mark using a pass through query to SQL2005
>
>The code bit with the SQL string looks like:
>
>Dim strSQL As String
>Dim strLog As String
>Dim strOK As String
>
>strLog = "Houston - We've got massive problems!"
>
>strSQL = "UPDATE RMS_DATA.dbo.tblBookingRequestExceptionLog 
>SET ExceptionLog = '" & strLog & "' + CHAR(13) + 
>ExceptionLog;"
>
>strOK = fncExecuteSP(strSQL)
>
>As long as the strLog does not contain a single quotation 
>mark the strSQL updates OK.
>
>I've lost patience on this - help please!!
>
>borge
>
>
>-- 
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com