Records with from Database that have single quotes are still not working correctly for me is this a know topic or am I just missing something in my code. Any feedback would be welcomed. Thanks.

Been using this for years. It's a bit bulky but well tested. Instead of:

    cSQL = "SELECT * FROM Customer WHERE Name='" & cCustomer & "'"

Use:

    cSQL = "SELECT * FROM Customer WHERE Name='" & Str4DB(cCustomer) & "'"

Where:

Public Function Str4DB(strOriginalString As String) As String
    Dim str As String, iPos As Integer
    If IsNull(strOriginalString) Then
        str = "'" & Null2Str(strOriginalString) & "'"
    Else
        str = "'" & ReplaceAll(strOriginalString, "'", "''") & "'"
    End If
    Str4DB = RemoveThisChar("|", str)
End Function

Public Function Null2Str(varVal As Variant, Optional default As String) As String
    On Error GoTo Err_Null2str

    If IsNull(varVal) Then
        If IsMissing(default) Then
            Null2Str = ""
        Else
            Null2Str = default
        End If
    Else
        Null2Str = CStr(varVal)
    End If
    Exit Function

Err_Null2str:
    Null2Str = ""
    Exit Function
End Function

Public Function ReplaceAll(strMainString As String, strFindString As String, strReplaceString As String) As String
  Dim lngLenMain As Long
  Dim lngStartPos As Long
  Dim lngFoundPos As Long
  Dim strNew As String ' string to build with replacements (initialy blank)

  lngLenMain = Len(strMainString)
  lngStartPos = 1

  Do While lngStartPos <= lngLenMain
    lngFoundPos = InStr(lngStartPos, strMainString, strFindString, 0) ' 0 = case sensitive
    If lngFoundPos = 0 Then ' No (more) instances
      ' Append remainder of strMainString to strNew
      strNew = strNew & Right$(strMainString, lngLenMain - (lngStartPos - 1))
      Exit Do
    Else
      ' Found a (new) instance. Replace it.
      ' ie. add the part of strMainString JUST scanned prior to instance
      ' then replacement string
      strNew = strNew & Mid$(strMainString, lngStartPos, lngFoundPos - lngStartPos) & strReplaceString
      lngStartPos = lngFoundPos + Len(strFindString)
    End If
  Loop

  ReplaceAll = strNew
End Function

Public Function RemoveThisChar(strChar As String, str As String) As String
    Dim strNew As String, strTempChar As String
    Dim iX As Integer
    For iX = 1 To Len(str)
        strTempChar = Mid(str, iX, 1)
        If strTempChar <> strChar Then strNew = strNew + strTempChar
    Next iX
    RemoveThisChar = strNew
End Function



Mark Roworth

If I am not mistaken (having had this problem numerous times over the years) single quotes need to be removed and replaced with the proper escape sequence which I don't have at hand, or as a quick test just remove them to see if this corrects the problem. The deal is that the single quotes cause the VB to misinterpret them as syntax. I have had this exact problem using ASP and by removing the quotes the problem is corrected.

In summary, replace any ' with ''.

Mark Roworth