Hi.  I seem to remember that there was some VB function that one could
use to convert strings before insertion into the database to avoid SQL
insertion errors such as inserting strings with 's and other
characters.

Does anyone know what that is?

probably something along these lines:

public function quotedtext(byval thetext as string) as string
quotedtext="'" & replace(thetext,"'","''") & "'"
end function

What needs to be changed will vary from one database to the next, so there's
no built-in VB function that will do this.  It's easy enough to build
something of your own, though.  For example (written off the top of my head,
so no guarantees):

Public Function ServerString(ByVal strIn As String) As String
    strIn = Replace(strIn, "'", "''")
    strIn = "'" & strIn & "'" 'If you want to automatically wrap your string
in single quotes.
    ServerString = strIn
End Function

The more complex function that I use, which handles most of the common data
types and situations, is (see my comments throughout):

Public Function ServerString(ByVal varText As Variant) As String
    Select Case VarType(varText)
        Case VbVarType.vbBoolean
            If CBool(varText) Then ServerString = "1" Else ServerString =
"0"
        Case VbVarType.vbByte, VbVarType.vbCurrency, VbVarType.vbDecimal,
VbVarType.vbDouble, VbVarType.vbInteger, VbVarType.vbLong,
VbVarType.vbSingle
            ServerString = CStr(varText)
        Case VbVarType.vbDate
            ServerString = "'" & Format(varText, "mm/dd/yy h:nn:ss am/pm") &
"'" 'Note:  we use something slightly different, so this one is typed off
the top of my head, but I believe it follows the official SQL standard.
        Case VbVarType.vbEmpty
            ServerString = "NULL" 'You may want to handle Empty values
differently; up to you.
        Case VbVarType.vbNull
            ServerString = "NULL"
        Case VbVarType.vbString
            varText = Replace(varText, "'", "''")
            varText = Replace(varText, vbCrLf, "' + CHAR(13) + CHAR(10) +
'")
            varText = Replace(varText, "+ '' +", "+")
            varText = "'" & varText & "'"
            If Left$(varText, 5) = "'' + " Then varText = Mid$(varText, 6)
            If Right$(varText, 5) = " + ''" Then varText = Left$(varText,
Len(varText) - 5)
            ServerString = varText
        Case Else
            Err.Raise vbObjectError + &H201, "ServerString", "Type not
supported: " & TypeName(varText) 'Use your own error number here; I used
vbObjectError + &H201 as a generic number.
    End Select
End Function



Rob
Show quoteHide quote

InStr anomaly?
using Line Input
Stop loading data process
Application crashing - trying to track down the problem.
Convert decimal to fraction string
Looking for an easy way to achive this problem.
email attachments?
Vb6 to check if a text file is already opened
Server my program is trying to connect to is not a trusted site with XP?
Date Picker Control