asked on

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:

?strdescription
<span title="" data-hasqtip="3699" oldtitle='RES SMD; 0603 0R00 5% 0.1W 200PPM 7" T&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

Kyle Abrahams, PMP

are you sure it's the double quote and not the single quote? Also what's the column type of description?

oldtitle='RES

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.

ASKER CERTIFIED SOLUTION

aikimark

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Dale Fye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Fabrice Lambert

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"
qd.Execute(dbFailOnError)
qd.Close
db.queryDefs.Delete(qd.Name)
Set qd = Nothing
Set db = Nothing

Open in new window

Hamed Nasr

Helpful notes about quotes:

s = """"""""""""

Open in new window

s is assigned 5 qouotes.
HOW?
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.

EXAMPLES:

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 Brock

Listen to Dale.

pcalabria

ASKER

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