[RESOLVED] Apostrophe in String Data causes error.

[kolw]

Hi

I am a noob to VB6 but am supporting some code written by another person a long time ago. Upto now I have been doing OK but I recently hit an issue that is outside of my experience and I was hoping someone could point me in the right direction. The program in question is auto-processing emails (the client is Outlook) and performing actions based upon the content of the email.

The issue I have hit however is that for the first time we received an email from an sender who's email address contained an apostrophe character > ' < e.g. rosie.o'[email protected] which crashed the email lookup function. The failing code is as follows:

Private Function FindRecord(EmlDBObj As Object, SQLSearchString As String)
If EmlDBObj.RecordCount > 0 Then
EmlDBObj.MoveFirst
EmlDBObj.Find SQLSearchString, , adSearchForward
End If

I would greatly appreciate any suggestions of how to handle the apostrophe issue.

Thanks in advance .... Colin

[TysonLPrice]

You need to change the single apostrophe to double. Something like this:

Private Sub Command_Click()
    Dim SQLSearchString As String
    Dim strLastName As String
    
    strSQL = "select * from refuserinfo"
        
    rsTempRecordset.Open strSQL, cnRate, adOpenStatic, adLockReadOnly
    
    
    strLastName = "o'price"
    SQLSearchString = " lastname = '" & Replace(strLastName, "'", "''") & "'"
   
    If Not rsTempRecordset.EOF Then
        rsTempRecordset.Find SQLSearchString, , adSearchForward
        If rsTempRecordsetEOF Then
            MsgBox "No Records Found... Try Again"
        Else
            MsgBox "Records Found"
        End If
    End If
    
End Sub

[kolw]

Thank you for your help with this - I will give it a try

Cheers

[dee-u]

This is already covered in the FAQ section: http://www.vbforums.com/showthread.p...-an-SQL-string

[kolw]

Thank you

[kolw]

Thank you for your help this worked like a charm I just needed to figure out the correct context for the replace function and where to add it.

My SQL Search Function was:

Private Function FindRecord(EmlDBObj As Object, SQLSearchString As String)
If EmlDBObj.RecordCount > 0 Then
EmlDBObj.MoveFirst
EmlDBObj.Find SQLSearchString, , adSearchForward
End If

If EmlDBObj.EOF Then
FindRecord = False
Else
FindRecord = True
End If

End Function

Based on your advice I changed tall the Function Calls from:

If Not FindRecord(DBConn.ESDB_RCP, "[RCP_MAILADDRESS] = '" & xSafe.Sender.Address & "'") Then ......

To the following:

Not FindRecord(DBConn.ESDB_RCP, "[RCP_MAILADDRESS] = '" & Replace(xSafe.Sender.Address, "'", "''") & "'") Then .....

Thank you again for your help