jeffrey.demulling at usbank.com
jeffrey.demulling at usbank.com
Fri Mar 7 16:40:44 CST 2008
Here is code I use to handle this problem when doing SQL updates on MS SQL
Server DBs
'---------------------------------------------------------------------------------------
' Module : mdlReplace
' DateTime : 2004-Mar-29 16:14
' Author : Jeffrey F. Demulling
' Purpose : Used to clean up sql statements with quotes(apostrophes) and
double quotes and make
' it so it can be used in on the fly statements
' Modified : 2004-Mar-29 16:14 - Jeffrey F. Demulling
' Notes : Parts of the code in this module come from Tom Jordan at
TriniDat Software Entwicklung GmbH of Duesseldorf BMGH
'---------------------------------------------------------------------------------------
Option Compare Database
Public Function AddQuote(vData As Variant) As String
'---------------------------------------------------------------------------------------
' Procedure : AddQuote
' DateTime : 2004-Mar-29 16:15
' Author : Jeffrey F. Demulling
' Purpose : Adds double quotes to the string passed, and also cleans up
any double
' and or single quotes within the string passed
' Modified : 2004-Mar-29 16:15 - Jeffrey F. Demulling
'---------------------------------------------------------------------------------------
'
'Const QUOTE = """" '4 quotation marks in a row
Const QUOTE = "'"
On Error GoTo AddQuote_Error
If IsNull(vData) Then
AddQuote = "NULL"
Else
AddQuote = QUOTE & ReplaceQuotes(vData, QUOTE, QUOTE & QUOTE) &
QUOTE
End If
On Error GoTo 0
Exit Function
AddQuote_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
AddQuote of Module mdlReplace"
End Function
Public Function ReplaceQuotes(ByVal varValue As Variant, ByVal strFind As
String, ByVal strReplace As String) As Variant
'---------------------------------------------------------------------------------------
' Procedure : ReplaceQuotes
' DateTime : 2004-Mar-29 16:15
' Author : Jeffrey F. Demulling
' Purpose : Searches the passed string for quotes (double and single) and
cleans them up.
' Modified : 2004-Mar-29 16:15 - Jeffrey F. Demulling
'---------------------------------------------------------------------------------------
'
Dim intLenFind As Integer
Dim intLenReplace As Integer
Dim intPos As Integer
On Error GoTo ReplaceQuotes_Error
If IsNull(varValue) Then
ReplaceQuotes = Null
Else
intLenFind = Len(strFind)
intLenReplace = Len(strReplace)
intPos = 1
Do
intPos = InStr(intPos, varValue, strFind, 0)
If intPos > 0 Then
varValue = Left(varValue, intPos - 1) & strReplace & _
Mid(varValue, intPos + intLenFind)
intPos = intPos + intLenReplace
End If
Loop Until intPos = 0
End If
ReplaceQuotes = varValue
On Error GoTo 0
Exit Function
ReplaceQuotes_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
ReplaceQuotes of Module mdlReplace"
End Function
Jeffrey F. Demulling
Project Manager
U.S. Bank Corporate Trust Services
60 Livingston Avenue
EP-MN-WS3C
St. Paul, MN 55107-2292
Ph: 651-495-3925
Fax: 651-495-8103
email: jeffrey.demulling at usbank.com
"Arthur Fuller"
<fuller.artful at gm
ail.com> To
Sent by: "Access Developers discussion and
accessd-bounces at d problem solving"
atabaseadvisors.c <accessd at databaseadvisors.com>
om cc
Subject
03/07/2008 04:28 Re: [AccessD] FW: CodeL
PM
Please respond to
Access Developers
discussion and
problem solving
<accessd at database
advisors.com>
If you do both Replace() calls, yes, and the count of occurrences is
irrelevant since it replaces them all.
Arthur
On 3/7/08, Eric Starkenburg <eric.starkenburg at home.nl> wrote:
>
> Arthur,
>
> Does this work when both characters exist in one sentence?
> Maybe the single quote once and two double quotes.
>
>
>
> Regards,
> Eric
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
>
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Arthur Fuller
> Sent: Friday, March 07, 2008 11:07 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] FW: CodeL
>
> This is overkill, I think, Eric. You just have to know how to handle SQL
> containing either single or double quotes or neither. It's quite
> straightforward, really. You can eliminate all the hassle for any column
> that might contain these values by doubling up on the single and double
> quotes, which you can do in exactly one statement each:
>
> Replace( [columnName], Chr(39), (Chr(39) & Chr(39) )
> and similarly
> Replace( [columnName], Chr(34), (Chr(34) & Chr(34) )
>
> You can wrap both calls into a function if you wish. The code does
nothing
> on values that don't contain the offending characters.
>
> Having been bitten by surnames like O'Hara and embedded quotations, if I
> suspect that this might ever occur, I just pre-empt it right from the
> beginning.
>
> hth,
> Arthur
>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
U.S. BANCORP made the following annotations
---------------------------------------------------------------------
Electronic Privacy Notice. This e-mail, and any attachments, contains information that is, or may be, covered by electronic communications privacy laws, and is also confidential and proprietary in nature. If you are not the intended recipient, please be advised that you are legally prohibited from retaining, using, copying, distributing, or otherwise disclosing this information in any manner. Instead, please reply to the sender that you have received this communication in error, and then immediately delete it. Thank you in advance for your cooperation.
---------------------------------------------------------------------