View Single Post
  #3 (permalink)  
Old March 11th, 2005, 05:14 AM
Bruce Bruce is offline
Authorized User
 
Join Date: Jun 2003
Location: Cape Town, Western Cape, South Africa.
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default

You can also write a function as follows:

'//start code
'use this one for normal text issues
Function sqlFixUp(TextIn)
  'fix the string being sent to the database
  sqlFixUp = ReplaceStr(TextIn, "'", "''", 0)
End Function
'use this one for non-printable characters as well
Function JetsqlFixUp(TextIn)
  'fix the string that comes from the database
  Dim strTMP As String
  strTMP = ReplaceStr(TextIn, "'", "''", 0)
  strTMP = ReplaceStr(TextIn, "&", "&&", 0)
  JetsqlFixUp = ReplaceStr(strTMP, "|", "' & chr(124) & '", 0)
End Function

'this is a generic function that is being called by both above.
Function ReplaceStr(TextIn, ByVal SearchStr As String, ByVal Replacement As String, _
                    ByVal CompMode As Integer)

   Dim WorkText As String, Pointer As Integer
   If IsNull(TextIn) Then
    ReplaceStr = Null
   Else
    WorkText = TextIn
    Pointer = InStr(1, WorkText, SearchStr, CompMode)
    Do While Pointer > 0
      WorkText = Left(WorkText, Pointer - 1) & Replacement & _
                       Mid(WorkText, Pointer + Len(SearchStr))
         Pointer = InStr(Pointer + Len(Replacement), WorkText, _
                         SearchStr, CompMode)
    Loop
    ReplaceStr = WorkText
    End If
End Function
'//end code

Regards

Bruce
Reply With Quote