Saving text with Quotes

[abelingaw]

Ok, I'm having problem on how to save texts with quotes.
Let's say I'm saving a value in a textbox like O'Neil.

That's where I receive an error.
How do I save names or texts like O'Neil or texts with qoutes?

Here's my code for saving:

With RecSet
        .Open "Select * from EmpRecord", DBLink, adOpenDynamic, adLockOptimistic
    
        .AddNew
            
            .Fields("Name") = txtName.Text
                     
        .Update
            MsgBox "Record has been Saved", vbInformation

Thanks

[debasisdas]

You need to replace ' (single quote) with ''(two single quotes)

Use the function replace or just play with CHR(39).

[abelingaw]

Ok, im kinda new to this but i did try.

Nagan = txtfname.Text
Mystring = "Insert into EmpRecord replace(Nagan," '", "''") Values ('" & Nagan & "')"

But the rest of the code just turns into a comment line (').
And where should I put it? Sorry, its my first time dealing with this.

[AndreRet]

This is a very common data entry bug for interfaces to SQL. Use the function
below to automatically double any SQL characters (NOTE these vary between
databases, they are not universal so you should check which apply to the
db you are using) which occur IN the data. You still need to provide any
OUTSIDE SQL punctuation.

This also seems to work in MySql.

Example -

sql= "SELECT * FROM CustTbl WHERE CustLastName ='" & doubleChar(txtName)
& "' "

Public Function DoubleChar(sqlStr As String) As String
'takes SQL wildcard and quote characters and doubles them to make literal
'e.g. "O'Brian" >> "O''Brian"
Dim i As Integer
Dim s As String
Dim tempChar As String

For i = 1 To Len(sqlStr)
tempChar = Mid$(sqlStr, i, 1)
Select Case tempChar
Case "'", "*", "?" 'modify this list for your db specific version
of SQL
s = s & tempChar & tempChar
Case Else
s = s & tempChar
End Select
Next
DoubleChar = s

End Function