Need help handling double quotes embeded in a string that must be saved in a database

I need help saving strings which have embedded quote characters.

I capture data from a webpage.  When the description field contains a double quote, my code fails.  I have no control over whether the code contains these quotes.
Is there any way to save the characters in my Access and SQL databases?

Currently I remove the character with the replace command, but it seems there must be a better way.

Example of code in strdescription that fails when I use strDescription in an SQL statement that inserts data into the database.
In this case the 7" portion of the code fails:

<span title="" data-hasqtip="3699" oldtitle='RES SMD; 0603 0R00 5% 0.1W 200PPM 7" T&amp;R 12 NC; 232270296001L'>RES SMD; 0603 0…</span>

Here is the actual code that fails.  A syntax error is produced when a double quote is embeded in strDescription

                        strSQLtext = "Insert Into NameConversionAppend (IndicatedManufacturer,PartNumber,SuspectedLineID, description, supplier, DistiType, Source) Values ('" & strManufacturerAsUploaded & "','" & strOrderNumber & "'," & intLineIDold & ",'" & strDescription & "','" & strSupplier & "','" & strDistType & "','mywebsite');"
                        DoCmd.RunSQL strSQLtext
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Kyle AbrahamsSenior .Net DeveloperCommented:
are you sure it's the double quote and not the single quote?  Also what's the column type of description?


You really should be using parameters to do this as you can get sql injection with your current statement if these are free form fields.
strSQLtext = "Insert Into NameConversionAppend (IndicatedManufacturer,PartNumber,SuspectedLineID, description, supplier, DistiType, Source) Values ('" & strManufacturerAsUploaded & "','" & strOrderNumber & "'," & intLineIDold & ",'" & Replace(strDescription, Chr(34), Chr(34) & Chr(34)) & "','" & strSupplier & "','" & strDistType & "','mywebsite');"

Open in new window


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
Dale FyeOwner, Developing Solutions LLCCommented:
You might want to consider something like:
Dim rs as dao.recordset
set rs = currentdb.openrecordset("SELECT * FROM yourTable WHERE 0 = 1")
With rs
    !IndicatedManufacturer = strManufacturerAsUploaded
    !PartNumber = strOrderNumber
    !SuspectedLineID = intLineIDold
    !description = strDescription
    !supplier = strSupplier
    !DistiType strDistType 
    !Source = "MyWebsite"
End with

Open in new window

Otherwise, you can use syntax like:

Replace(strDescription, "'", "''")

which replaces a single quote in strDescription with two single quotes.  Access will interpret this as a single quote when it inserts the data into your table.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Fabrice LambertFabrice LambertCommented:
Well, single quotes and double quotes are a never-ending nightmare when you have no control on it.
Replacing isn't a solution as it alter data.

Instead of building a query by concatening strings, you can use a parametrized query. Such queries handle quotes without troubles.
Sample code (adapt to fit your needs):
Dim SQL As String
SQL = vbNullString
SQL = SQL & "PARAMETERS myParameter Text ( 255 );" & vbcrlf
SQL = SQL & "INSERT INTO myTable ( myColumn)" & vbcrlf
SQL = SQL & "VALUES ([myParameter]);"

Dim db As DAO.Database
Set db = CurrentDb

Dim qd As DAO.QueryDef
Set qd = db.CreateQueryDef("tempQd", SQL)
qd.Parameters("myParameter").value = "data' with"" quotes"
Set qd = Nothing
Set db = Nothing

Open in new window

Hamed NasrRetired IT ProfessionalCommented:
Helpful notes about quotes:
s = """"""""""""

Open in new window

s is assigned 5 qouotes.
first and last quotes are for string assignment. Then every 2 quotes assign 1 quote to s.
When concatenating, each part is treated similarly, the first and last quotes are for string assignment, and every 2 other quotes assign 1 quote to the string variablr. If you miss one quote, access assumes it and complete it.

String assignment
SQL = "SELECT fld FROM tbl WHERE fld1 = 3"

using text instead of 3
SQL = "SELECT fld FROM tbl WHERE fld1 = ""abc"""

using a variable, var1 = "abc" instead of 3, then we need to concatenate.
SQL = "SELECT fld FROM tbl WHERE fld1 =" & """" & var1 & """"

SQL = "INSERT INTO tbl (fld1, fld2) VALUES (3, 4)
	=> INSERT INTO tbl (fld1, fld2) VALUES (3, 4)

SQL = "INSERT INTO tbl (fld1, fld2) VALUUES (""a"", ""b"")"
	=> INSERT INTO tbl (fld1, fld2) VALUUES ("a", "b")

Assume var1="abc" , and var2="def", then
SQL = "INSERT INTO tbl (fld1, fld2) VALUUES (" & """" & var1 & """" & "," & """" & var2 & """" & ")" 
	=> INSERT INTO tbl (fld1, fld2) VALUUES ("abc","def")

Open in new window

Gustav BrockCIOCommented:
Listen to Dale.
pcalabriaAuthor Commented:
Thanks everyone.. I split the points because the code I actually used was the code provided by Aiki.. my first thought was ..why didn't I think of that...Dale provided another way to use the same approach since I can't seem to remember Chr 34. Other approaches where appreciated but didn't feel as straight forward for my solution...thanks
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

From novice to tech pro — start learning today.