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?
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

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.
Dale FyeCommented:
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.
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.

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

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
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.

All Courses

From novice to tech pro — start learning today.