Single Quotes in SQL String

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

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

VB Code:

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

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

Code:

---

Function Fix39(ByVal Text As String) As String

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

End Function

---

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

Code:

---

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

---

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

Code:

---

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

---

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