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
            .Fields("Name") = txtName.Text
            MsgBox "Record has been Saved", vbInformation


Edited by abelingaw: n/a

6 Years
Discussion Span
Last Post by AndreRet

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

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

Edited by debasisdas: n/a


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.


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
DoubleChar = s

End Function
This article has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.