We help IT Professionals succeed at work.
Get Started

VB.NET 2005 Replace function not replacing Single Quote with BackSlash Single Quote

Ted Palmer
Ted Palmer asked
Last Modified: 2011-10-19
My OS is Windows XP SP2 and my IDE is Visual Studio 2005. My program is written in VB.NET 2005. My RDBMS is MySQL version 5.0.45

Context: I have been working on this program for about 2 years now so I have lots of experience writing dynamic SQL that successfully interfaces with MySQL. The commented out code
'Return Chr(39) & Input.Replace("'"c, "\'") & Chr(39)
was written about one year 5 months ago and was working just fine in VB.NET 2003. I upgraded to VB.NET 2005 last January and have not rigerously tested this function, FixSingleQuote1, since then. It just seemed to work when I used it till today when I happen to stress it a little harder. The commented line of code was provided to me by another EE Expert and was the only line of code in the whole function till today. He said it used Regular Expression syntax which I was not familiar with at the time, but I made up some rigorious test cases and it worked without any problems. I took him at his word and I still do. I confess I never really understood how it worked, but since it did, I didn't question it. I just used it and was glad for a freebee that solved a problem for which I had a much less elegent solution that only worked when there was just one single quote in the input text string.

Today I copied and pasted from a webpage some biographical data on one of the US Supreme Court Justices that I was using as test data. It had a few single quotes in it that caused MySQL to return an error message saying my SQL syntax had an error in it. Maybe this was the first time since converting to VB.NET 2005 that I have actually tried to enter any text that had single quotes in it.

I am just trying to convert all occurances of a string of length one from a single quote character to a two character string of backslash single quote and return the whole string delimited by single quote characters, The help gives the following example:

Dim TestString As String = "Shopping List"
' Returns "Shipping List".
Dim aString As String = Replace(TestString, "o", "i")

This looks like a very simple function to use. In debug I can step through the code and see the single quote characters in my Input string. I can execute the assignment statement and look at the Output string. Nothing has changed. The single quotes are still there without the backslash character preceding them. Char(92) = ASCII backslash character and Char(39) is the single quote character.

This is so simple I can't see where I am doing anything wrong. If I am wrong, I'm not too proud to admit it. If this is a known bug in VB.NET 2005, is there a commonly used work around?

Thank you,
Ted Palmer
Public Function FixSingleQuote1(ByVal Input As String) As String
        Dim oldSingleQuote As String = "'"
        'Dim newSingleQuote As String = "\'"
        Dim newSingleQuote As String = Chr(92) & "'"
        Dim Output As String = ""
        'Return Chr(39) & Input.Replace("'"c, "\'") & Chr(39)
        'Return Chr(39) & Input.Replace("'"c, "''") & Chr(39)
        Output = Chr(39) & Replace(Input, oldSingleQuote,newSingleQuote) & Chr(39) '<-- As of 2008-07-08 by Ted Palmer
        Return Output '<-- As of 2008-07-08 by Ted Palmer
    End Function 'FixSingleQuote1

Open in new window

Watch Question
This problem has been solved!
Unlock 1 Answer and 21 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE