We help IT Professionals succeed at work.

Query syntax error with quotation marks in the data

I am adjusting a VB6 statement in which the application is making a request from the SQL database and the value contains quotation marks in it.  The result is an error:  Incorrect syntax near 'value'  

The requesting statement from VB6 is:

select count(*), Max(Grouping) from tblOrgHours where Narrative = '" & Text1 & "'"

If Text1 contains quotation marks in it then the error is produced.  How can I avoid this?
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2014
Commented:
select count(*), Max(Grouping) from tblOrgHours where Narrative = '" & Replace(Text1, Chr(34), Chr(34) & Chr(34)) & "'"

Open in new window

Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Const QUOTE = """"

select count(*), Max(Grouping) from tblOrgHours where Narrative = '" & Te" & QUOTE & "xt1 & "'"

Open in new window

Author

Commented:

I am so sorry I made an error in my statement on the original question.


Text1 is a paragraph field on the VB6 form.  It sometimes contains quotation marks within the paragraph. Quotation marks are also found in the Narrative field in the database.  I am doing a comparison with the SQL statement to see if anything from the form(Text1) exists in the database(Narrative)  When executing the sql command thats when I get the error.


How do I resolve this problem?  Sorry for the poor handling of my question.  Please feel free to ask for any other clarification but I hope that clarifys it.

Author

Commented:

I believe the quotation marks that are causing the error look like this in the narrative: 


Bobby when to his friend's house 

OR

He said it was "unsuitable"

Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
Change 34 to 39 in aikimark's code

 or change

Const QUOTE = """" to Const QUOTE = "'"

in mine.
CERTIFIED EXPERT
Top Expert 2014

Commented:
Apostrophe characters shouldn't be a problem.

Did you try my statement?
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
BTW those Chr numbers come from places like this.
CERTIFIED EXPERT
Top Expert 2014

Commented:
If your string contains quote characters, DO NOT change 34 to 39 (per Martin)

Author

Commented:

I just tested my issue and its definitely scenarios where the paragraph in Text1 has an apostrophe.  


ie.  The dog ran to Barry's house  

OR

The 'dog' has some issues


Both of these produce errors

Author

Commented:

I don't believe either of your answers will work.  You see I'm trying to see if this narrative from Text1 exists in the database so if I replace the apostrophe in Text1 with another character, then it won't properly locate the paragraph since the value has essentially changed.  Ugh!

CERTIFIED EXPERT
Top Expert 2014

Commented:
What is the text of the matching item in the database?
CERTIFIED EXPERT
Top Expert 2014

Commented:
Since your Text1 field contains apostrophe characters and not quote characters, then follow Martin's comment and change the 34 values to 39 values in my statement.

Author

Commented:

by golly jee whiz.  I should have just shut up and tried the solution.  Yes.  Martin was right.  change aikmarks solution to 39!

Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I’m glad I was able to help.

If you expand the “Full Biography" section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange Most Valuable Expert (MVE) 2015, 2017
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2019
              Experts Exchange Top Expert VBA 2018, 2019
              Experts Exchange Distinguished Expert in Excel 2018