Single Quotes in SQL statement

[OlgaW]

I would like to be able to insert a statement into my table which contains a number of single quotes. For ex.,
'John's friend'.
What do I surround this statement with in my SQL to avoid errors?

Thank you,

Olga

[JHausmann]

Double quotes - "

[honeybee]

JHausMann, I just want to clarify...

Do you mean the string should be "John's Friend" ?

Thanks in advance for your reply...

[JHausmann]

yup.

"John's Friend"

[Negative0]

I am not sure what version of VB you are using so here is a solution that will work for sure:

Public Function FixQuotes(A As String)

Dim i As Integer
i = InStr(A, "'")

Do While i > 0
  A = Left(A, i) & Mid(A, i)
  i = InStr(i + 2, A, "'")
Loop

FixQuotes = A

End Function

Then use it in this fashion:

YourString = "John's Friend"
YourString = FixQuotes(YourString)  ' String now is John''s Friend

Also you could use this function to fix all text boxes on a form:

Public Sub FixFormQuotes(glCallfrm As Form)
Dim mycontrol As Control
For Each mycontrol In glCallfrm.Controls
If TypeOf mycontrol Is TextBox Then
  mycontrol.Text = FixQuotes(mycontrol.Text)
End If
Next

End Sub

If you are using VB6 you could also use the replace function, just replace single quotes with two single quotes.

Hope this helps,

[JHausmann]

It's kind of hard (not impossible, but hard) to wrap a SQL command in single quotes in VB, simply because the compiler will treat it as a comment....

[Brent vanSchaayk]

There may be an even easier way to doing this. If you are using a sybase database try making a stored proceedure in the database and pass your string to it as an argument, it may even speed things up.