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

Mark A Matte markamatte at hotmail.com
Wed Jan 16 09:50:24 CST 2008


Borge,

I have a SP that ran an INSERT that had a similar issue...Yes double quotes are needed...but I found the stupid thing would run 1 day...but if I changed it at all after it ran...it would fail(even with correct syntax).  Even more oddly...by the time I got it to run again...the syntax was exactly what I started with.  I never really understood this...but someone on the list suggested replacing all double quotes with char(34)....and I have not had a single issue since.

Good Luck,

Mark A. Matte

> From: pcs at azizaz.com
> To: accessd at databaseadvisors.com
> Date: Wed, 16 Jan 2008 22:35:29 +1000
> Subject: Re: [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" 
>>Subject: Re: [AccessD] Escaping a Single Quotation Mark in
> a pass through update query
>>To: 
>>
>>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
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com

_________________________________________________________________
Share life as it happens with the new Windows Live.
http://www.windowslive.com/share.html?ocid=TXT_TAGHM_Wave2_sharelife_012008



More information about the AccessD mailing list