Solved

Urgent -Using Escape characters in my query- Please help

Posted on 2006-06-12
4
215 Views
Last Modified: 2010-04-23


I am trying to use the escape characters in my query and I am getting the syntax error as
Incorrect syntax near 'Ifft & Co., P.A.'.  
Please help.


 strSQLQuery = "SELECT id,firm,residentmanager,address, city +', '+ state+', ' + zip as address2,phone,discipline,issuerenewdate,registrationexpiredate,certstatus FROM tblboadata where ksboatype ='" & "F" & "' and (firm LIKE('%'+Replace(Replace(Replace(Replace(Firm,'\', '\\'), '[', '\['), '_', '\_'), '%', '\%')+'%') ESCAPE '\')'" & sFirm & "'"
0
Comment
Question by:amruthanet
  • 2
  • 2
4 Comments
 
LVL 17

Expert Comment

by:ZeonFlash
ID: 16887609
The end of your SQL string looks fishy:
       ESCAPE '\')'" & sFirm & "'"

That will put something like:  ESCAPE '\')'sFirmContents'

What is an example of what can be in sFirm?
0
 

Author Comment

by:amruthanet
ID: 16887939
Below are the examples for the querystring(sFirm)

Ifft & Co., P.A.
Schmidt & Eudaley CPA's, P.A.
Schulteis and Walsh, P.A.

So should I need to remove the single quote?

Thanks,
0
 
LVL 17

Accepted Solution

by:
ZeonFlash earned 500 total points
ID: 16888316
No need to remove the single quotes, just replace them with 2 single quotes.  Does the following work?

"SELECT id,firm,residentmanager,address, city +', '+ state+', ' + zip as address2,phone,discipline,issuerenewdate,registrationexpiredate,certstatus FROM tblboadata where ksboatype ='" & "F" & "' and firm LIKE '%" & sFirm.Replace("\", "\\").Replace("[", "\[").Replace("_", "\_").Replace("%", "\%").Replace("'", "''") & "%'"
0
 

Author Comment

by:amruthanet
ID: 16888623
Thanks a bunch.  It worked as a charm.
0

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question