SQL - INSERT INTO <Table> (field1, field2...) VALUES (value1, value2...) - BUT data contains (') single quote

I am chunking through some XML lines to extract the Fieldname and Value (using MID statement), and then writing the result to an Access Table.  This is working just fine UNTIL I encounter data that contains a single quote.

Example String:

    <desc>It's No Hoover! by markp99, Traditional (2/2)</desc>

I can collect the fieldname "desc" and its value "It's No Hoover! by markp99, Traditional (2/2)"

But when I attempt to insert the VALUE into an Access table using:

myValue = "It's No Hoover! by markp99, Traditional (2/2)"
SQL = "INSERT INTO myTable (desc) VALUES ('" & myValue "')"
docmd.runsql SQL

I get an error msg,  I am pretty sure because the single quote (') is being enterpreted and a delimiter.

What should I do to the VALUE string to retain, but ignore, the single quote (')?

Note that I am processing 25K to 100K (or more) lines, so I would prefer not to do any global or line-by-line search and replace.  An incremental performance "tax" will get multiplied...

Any ideas??

Thanks


markp99Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
myValue = "It's No Hoover! by markp99, Traditional (2/2)"
SQL = "INSERT INTO myTable (desc) VALUES ('" & replace(myValue,"'", "''") "')"
docmd.runsql SQL
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
markp99Author Commented:
Note I see a typo in my example in the previous post (my application is correctly formatted).  Should have been written as:

myValue = "It's No Hoover! by markp99, Traditional (2/2)"
SQL = "INSERT INTO myTable (desc) VALUES ('" & myValue & "')"
docmd.runsql SQL

My question is still open, with this correction.

Thanks
0
JoshdanGCommented:
Just like doubling quotes in VB, doubling single quotes in SQL means use the literal instead of ending the string.  Just do a search/replace on the final string you are going to insert.

Replace(myValue, "'", "''")

That's replace one single quote with two single quotes.

Unless you are certain that no single quote can appear in any other string field, I would do the same thing on them.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Rey Obrero (Capricorn1)Commented:
try

myValue = "It's No Hoover! by markp99, Traditional (2/2)"

SQL = "INSERT INTO myTable (desc) VALUES (" & chr(34) & myValue & Chr(34) & ")"
docmd.runsql SQL
0
markp99Author Commented:
AngelIII

Is replace a: quote-tick-tick-quote?
0
markp99Author Commented:
Man,

You guys are FAST!

:-)
0
Rey Obrero (Capricorn1)Commented:
markp99
also, i suggest that you replace the field name  DESC to something else,

DESC is a reserved word for Access and Jet which means descending, use for sorting.
0
markp99Author Commented:
capricorn1

Yep, already changed in my app, just not for the example ;)
0
Rey Obrero (Capricorn1)Commented:
markp99,
did you get it working?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Replace(myValue, "'", "''")

indeed replaces 1 single quote (tick) by 2 single quotes (tick tick)
0
GRayLCommented:
How about:

myValue = "It's No Hoover! by markp99, Traditional (2/2)"
SQL = "INSERT INTO myTable (desc) VALUES (""" & myValue & """)"
docmd.runsql SQL
0
harfangCommented:
Ray, did you mean this?
SQL = "INSERT INTO myTable (desc) VALUES (""" & Replace(myValue, """", """""") & """)"
(sorry, had to ;)
(°v°)
0
harfangCommented:
On a more serious side, I always have a few functions to handle VB to SQL strings, like:

Function QuoteSQL(pvarString) As String
    If IsNull(pvarString) Or Trim(pvarString) = vbNullString Then
        QuoteSQL = "Null"
    Else
        QuoteSQL = "'" & Replace(Trim(pvarString), "'", "''") & "'"
    End If
End Function

This handles Null, Zero-length strings, trims spaces... good practice. So:

    SQL = "INSERT INTO myTable (desc) VALUES (" & QuoteSQL(myValue) ")"

Cheers!
(°v°)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.