Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1163
  • Last Modified:

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


0
markp99
Asked:
markp99
  • 4
  • 3
  • 2
  • +3
1 Solution
 
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
 
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
  • 3
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now