Help! Handling Apostrophe while saving data to MSAccess

[dotnetprogrammer]

I have a problem saving textbox's text data to MS Access using SQL Update in VB. I have tried Replace command like REPLACE(MYSTRING, "'", "''&quot, but in vein!
Yet, if I enter a string using two single quotes instead of one like O''Conner or O''Neil, string is saved as intended (O'Conner or O'Neil).
Any help will be appreciated!
fred

 

[JohnYingling]

Apostophes only cause problems in SQL statements not the data. Double up the apostophes in the SQL WHERE Clause etc, not the actual data. Doubling apostrophes in the data makes no sense whatsoever. You will have to translate back EVERWHERE, every web page, data control etc. etc.
By the way REPLACE is a function not a method so if you only executed
REPLACE(MYstring,"'","''&quot you threw away the returned string. It should be.
MYstring = REPLACE(MYstring,"'","''&quot

http://WWW.VBCompare.Com

 

[dotnetprogrammer]

John
You are right! My problem is in SQL statement.
If a user enters a name or street-name like O'Brian
or O'Hara in the VB form, when the data is being updated in MS Access table using UPDATE command, vbcatches the apostrophe as an error.
If I enter two apostrophes in the textbox, instead of one, VB does not error. Yet, the following Replace command does not stop the problem:
(Following is the Replace command I have tried
txtFName= REPLACE(txtFName, "'", "''&quot
txtLName= REPLACE(txtLName, "'", "''&quot
txtAddress= REPLACE(txtAddress, "'", "''&quot
Any suggestions? Thanks,
fred

 

[MichaelRed]

Programatically search for and replace the lone single quote with Double Quote, Single Quote, Double Quote.

e.g. ' ==> "'"

It has been a while, but 'cogito' the 'trick is to make it into three strings which are concatenated within the sql.



Try this.

Public Function basCaptureApostrophy(strIn As String) As String

    Dim strRep As String
    Dim Quo As String * 1
    Dim Amper As String * 1
    Dim Apost As String * 1
    Dim strOut As String
    
    Quo = Chr(34)
    Amper = Chr(38)
    Apost = Chr(39)
    strRep = Space(1) & Amper & Space(1) & Quo & Apost & Quo & Space(1) & Amper & Space(1)

    Dim StrSplit As Variant

    StrSplit = Split(strIn, Apost)
    If (UBound(StrSplit) <> 0) Then
        For Idx = 0 To UBound(StrSplit) - 1
            strOut = strOut & Quo & StrSplit(Idx) & Quo & strRep
        Next Idx
        strOut = strOut & Quo & StrSplit(UBound(StrSplit)) & Quo
     Else
        strOut = StrSplit(0)
    End If
    
    basCaptureApostrophy = strOut

End Function

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[Dzidze]

Use this Function in a start up module. Then whenever you need it, just make a refence to it in your coding...

Function Apostrophe(rsSQL As String) As String
Dim sSQL As String
Dim nCounter As Integer

On Error GoTo err_apostrophe

For nCounter = 1 To Len(rsSQL)
sSQL = sSQL & IIf(Mid(rsSQL, nCounter, 1) = "'", "''", Mid(rsSQL, nCounter, 1))
Next

Apostrophe = sSQL

Exit Function
err_apostrophe:
MsgBox Error(Err)
End Function