Replacing Double quotes with TWO Single Quotes

30 Oct 2006 7:26 PM

Justin Fancy

Hi everyone,

I need to replace all instances of a double quote(") with two single
quotes('') in a text file. I already have some replacements of strings
going on, but I tried this one, but the syntax is being read wrong.

Here is the code:

            lineFile1 = sr.ReadLine

            Dim sb As New System.Text.StringBuilder(lineFile1)
            sb.Replace("C:", "")
            sb.Replace("x:", "")
            sb.Replace("wwwroot", "")
            sb.Replace("\", "/")
            sb.Replace("!", "1")
            sb.Replace("&", "2")
            sb.Replace("cs-uri-stem", "")
            lineFile1 = sb.ToString()

The reason why I want to achieve this is because when I read the lines
of the text file, using SB.REPLACE(""","''") into an access database
table, it gives me an unhandled exception error:

SYNTAX ERROR: MISSING OPERATOR IN QUERY EXPRESSION

which means that the database is treating it as an sql statement, which
is not what I want. I want it to be sent o the table without any
errors. Its a big txt file, so it runs for a while, but it hangs up on
the double quotes. I don't suppose it will hang on the single quotes if
I change it?

Any suggestions??

Justin Fancy

30 Oct 2006 8:25 PM

ImageAnalyst

The problem is that via some weird VB logic, you need FOUR double
quotes to get a single double quote character, not three as you had it.
Put this in your code and try again:
Public Const strDoubleQuote As String = """" 'Via weird VB logic, this
evaluates to a single double-quote mark
sb.Replace(strDoubleQuote , "''") ' The last string is " followed
by ' followed by ' followed by " which is just two single quotes
enclosed in double quotes.
Regard,
ImageAnalyst

==========================================================
Justin Fancy wrote:
Show quoteHide quote

> Hi everyone,
>
> I need to replace all instances of a double quote(") with two single
> quotes('') in a text file. I already have some replacements of strings
> going on, but I tried this one, but the syntax is being read wrong.
>
> Here is the code:
>
>             lineFile1 = sr.ReadLine
>
>             Dim sb As New System.Text.StringBuilder(lineFile1)
>             sb.Replace("C:", "")
>             sb.Replace("x:", "")
>             sb.Replace("wwwroot", "")
>             sb.Replace("\", "/")
>             sb.Replace("!", "1")
>             sb.Replace("&", "2")
>             sb.Replace("cs-uri-stem", "")
>             lineFile1 = sb.ToString()
>
> The reason why I want to achieve this is because when I read the lines
> of the text file, using SB.REPLACE(""","''") into an access database
> table, it gives me an unhandled exception error:
>
> SYNTAX ERROR: MISSING OPERATOR IN QUERY EXPRESSION
>
> which means that the database is treating it as an sql statement, which
> is not what I want. I want it to be sent o the table without any
> errors. Its a big txt file, so it runs for a while, but it hangs up on
> the double quotes. I don't suppose it will hang on the single quotes if
> I change it?
>
> Any suggestions??
>
> Justin Fancy

31 Oct 2006 8:58 AM

Peter Macej

> The problem is that via some weird VB logic, you need FOUR double
> quotes to get a single double quote character, not three as you had it.

It's not weird at all. Quote has to have some escape sequence in most
languages otherwise the compiler couldn't parse the strings. In C (and
C#, C++ or Java ) this sequence is \". In VB it is "".

--
Peter Macej
Helixoft - http://www.helixoft.com
VSdocman - Commenter and generator of class documentation for C#, VB
..NET and ASP .NET code

30 Oct 2006 9:41 PM

Herfried K. Wagner [MVP]

"Justin Fancy" <justinfa***@gmail.com> schrieb:
> The reason why I want to achieve this is because when I read the lines
> of the text file, using SB.REPLACE(""","''") into an access database
> table, it gives me an unhandled exception error:
>
> SYNTAX ERROR: MISSING OPERATOR IN QUERY EXPRESSION

'sb.Replace(ControlChars.Quote, "''")' or 'sb.Replace("""", "''")'.

--
M S Herfried K. Wagner
M V P <URL:http://dotnet.mvps.org/>
V B <URL:http://dotnet.mvps.org/dotnet/faqs/>

31 Oct 2006 7:13 PM

ImageAnalyst

Yes that will probably work too. I just haven't retrained my memory
yet to remember whether ControlChars.Quote is a single quote
(apostrophe) or double quote (quotation mark). Unfortunately there's
no ControlChars.Apostrophe to make it super-explicit and distinct, so
the ambiguity remains (for me at least). And the tool tip /
intelllisense on it is just a mishmash of tick marks so it's hard to
see how it's really defined. I'll try to remember from now on.
Regard,
ImageAnalyst

Herfried K. Wagner [MVP] wrote:
Show quoteHide quote

> "Justin Fancy" <justinfa***@gmail.com> schrieb:
> > The reason why I want to achieve this is because when I read the lines
> > of the text file, using SB.REPLACE(""","''") into an access database
> > table, it gives me an unhandled exception error:
> >
> > SYNTAX ERROR: MISSING OPERATOR IN QUERY EXPRESSION
>
> 'sb.Replace(ControlChars.Quote, "''")' or 'sb.Replace("""", "''")'.
>
> --
> M S Herfried K. Wagner
> M V P <URL:http://dotnet.mvps.org/>
> V B <URL:http://dotnet.mvps.org/dotnet/faqs/>