Single Quotes in SQL String

[nadira]

How do I code my program so that if a user enters a single quote in a string value that I have to pass as a SQL statement, there will be no ODBC error.

like "David's" ... it come back with an odbc invalid format error.


Thanks

[MetallicaD]

You need to run that string through the wringer.. create a small function that you can pass a string into with a boolean that determines if you want to encode for SQL or decode for text... then in that function, use the Replace function and replace any ' with something like &sq.. or reverse, replace &sq with ' on the way out

Public Function SQLStringConv(str as String, bolInOut as Boolean) as String
If bolInOut Then
   SQLStringConv = Replace(str, "'", "&sq")
Else
   SQLStringConv = Replace(str, "&sq", "'")
End If
 
End Function

-mcd

[JasonGS]

You can also double the character to 'escape' it. This function works pretty good for me anyways...

Function Fix39(ByVal Text As String) As String

    Fix39 = Replace(Text, Chr(39), Chr(39) & Chr(39))

End Function

[kleinma]

pretty much the same thing.. but i made a function in a module

Public Function Apost(sString as string)
    Apost = Replace("'","''")
End Function

then when you make your SQL statment... i would use

sSql = "INSERT INTO tblTable [MyField] Values('" & apost(txtName) & "')"

[DerFarm]

It reads a LOT better if you use chr$(34) instead of doubling the
quotes.