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&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
pcalabriaAsked:
Who is Participating?
 
aikimarkCommented:
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

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

 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.
0
 
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
    .AddNew
    !IndicatedManufacturer = strManufacturerAsUploaded
    !PartNumber = strOrderNumber
    !SuspectedLineID = intLineIDold
    !description = strDescription
    !supplier = strSupplier
    !DistiType strDistType 
    !Source = "MyWebsite"
    .Update
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.
1
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"
qd.Execute(dbFailOnError)
qd.Close
db.queryDefs.Delete(qd.Name)
Set qd = Nothing
Set db = Nothing

Open in new window

0
 
hnasrCommented:
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

0
 
Gustav BrockCIOCommented:
Listen to Dale.
0
 
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
0
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.