Hi all,

I have some select and insert queries that have values that contain text with single quotes (i.e "O'Reilly"). How do I get around this problem when I execute this query as it will error. What can I do with the quote so it used by the query??

I am programming this in Visual Basic 6.

Thanks
Anthony

strMySQLString = " SELECT * FROM MyTable WHERE MyName = " & chr(34) & "Smith" & Chr(34)

strMySQLString = " SELECT * FROM MyTable WHERE MyName = " & chr(34) & strInputLastName & Chr(34)



jiri

Before you build up the SQL string for the query, double the quotes in the name.

Here's a code example for a function to do this, I use it all the time.

'Begin of code sample
Public Function DoubleQuotes(s As String) As String
Dim a As String, b As String, i As Integer

If Len(s) = 0 Then DoubleQuotes = "": Exit Function

a = "": b = ""
For i = 1 To Len(s)
b = Mid(s, i, 1)
If b = "'" Then b = "''"
a = a & b
Next

DoubleQuotes = a
End Function
'End of code sample

A SQL string would then look like:

strSQL = SELECT * FROM People WHERE Name = '" & DoubleQuotes(strName) & "'"

Which database server are you using? Am right in that the problem is with strings with a single quote or apostrophe and the error is returned from the database?

If so, in some variants of SQL you need to put 2 single quotes in the query string, thus:

"O''Reilly"

Note this is a ' followed by another ' not a " although it looks like the later.