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
Printable View
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
then when you make your SQL statment... i would useCode:Public Function Apost(sString as string)
Apost = Replace("'","''")
End Function
Code:sSql = "INSERT INTO tblTable [MyField] Values('" & apost(txtName) & "')"
It reads a LOT better if you use chr$(34) instead of doubling the
quotes.