Re: Handling single quotes " ' " in data

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Douglas J. Steele (NOSPAM_djsteele_at_NOSPAM_canada.com)
Date: 01/14/05 

Date: Fri, 14 Jan 2005 10:22:57 -0500

There's no reason not to store the single quotes.

Presumably your SQL statement is something like

strSQL = "INSERT INTO MyTable (MyField) " & _
   "VALUES ( ' " & strFilename & " ' )"

(extra spaces added for clarity)

Try either:

strSQL = "INSERT INTO MyTable (MyField) " & _
   "VALUES ( " & Chr$(34) & strFilename & Chr$(34) & " )"

or

strSQL = "INSERT INTO MyTable (MyField) " & _
   "VALUES ( ' " & Replace(strFilename, " ' ", " ' ' ") & " ' )"

(remove the extra spaces in the parameters in the Replace statement before
running!) 

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)
"Irwin Williams" <icon4irwin@hotmail.com> wrote in message
news:OevELPk#EHA.2608@TK2MSFTNGP10.phx.gbl...
> Hi,
> I have been storing file locations in a table in access.  The actual
> location comes from a file object.
> Sometimes a file name may have a single quote in it - and this is causing
an
> error in access.  As such, i've been using the pipe character for internal
> purposes - replacing the " ' " with " | ".  And vice-versa when reporting
> the information to users.
> Does anyone know a better way of handling this?
>
> /irwin
>
>

Relevant Pages

  • Re: Handling single quotes " " in data
    ... With objFile ... > Presumably your SQL statement is something like ... > (remove the extra spaces in the parameters in the Replace statement before ... >> I have been storing file locations in a table in access. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Handling single quotes " " in data
    ... You shouldn't need to change the single quotes at all with that approach. ... >> (remove the extra spaces in the parameters in the Replace statement ... >> Doug Steele, Microsoft Access MVP ... >>> I have been storing file locations in a table in access. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Escape html tags and other dangerous input
    ... The & affects the querystring. ... Single quotes can end the SQL statement allowing for a SQL ... >> injection, but that doesn't help with the ampersand, and the search page ...
    (microsoft.public.dotnet.security)
  • executing an sql statement in perl
    ... and thetime> sysdate - 90 ... however i don't think it likes the 2 sets of single quotes. ... The below sql statement works fine directly in my sql program ...
    (comp.lang.perl.misc)
  • Re: SQL and RPG
    ... the SQL statement is being called ... I don't need the semicolon token, ... ITMI in this example is 6 characters and ITP# is 18 ... If your fields are defined as character you have to put single quotes ...
    (comp.sys.ibm.as400.misc)