Re: ADO Recordset FIND problem

From: Bob Barrows [MVP] (reb01501_at_NOyahoo.SPAMcom)
Date: 05/30/04 

Date: Sat, 29 May 2004 21:41:56 -0400

Yes. That is the only character that can be confused with a string
delimiter. Keep in mind, if you use a double quote as a string delimiter,
then the double-quote character needs to be escaped if you wish it to be
treated literally. For example, if you wish to use double quotes to delimit
your SQL strings, you have to escape the SQL string delimiter when assigning
the SQL string to a vbscript string variable. This can get confusing. You
have to realize there are two "execution" engines involved when sending
strings to be executed in a database: the engine used to create the initial
string, and the query engine that executes the string. For example, you are
creating a string in vbscript using a double-quote to delimit it:

strvar = "this is a string"

The query engine will never see those double-quotes. However, let's say your
data contains literal double-quotes, for example "9/16" wrench". Assigning
that to a vbscript string variable will require you to escape the literal
double-quote character:

strvar = "9/16"" wrench

despite the fact that if you are using it in a sql statement, the
double-quote won't matter:

strvar = "select <field list> from sometable where somefield='9/16""
wrench'"

It can be tough to keep track of this, which is one of the reasons I prefer
to avoid dynamic sql, using stored procedures or saved parameter queries
instead.

Bob Barrows

Charles E Finkenbiner wrote:
> Hi Bob,
>
> Thanks for the pointer, it does work now. Since the single quote is
> used to delimit text can I 'assume' that it is the only special
> character that needs to be doubled?
>
>
> Thanks for your help,
>
> Charles
>
> On 5/29/2004 8:38 AM, Bob Barrows [MVP] wrote:
>> Charles E Finkenbiner wrote:
>>
>>> Hi All,
>>>
>>> Well, 1 small bug came up to bite me in the rear. When I do a text
>>> FIND I enclose the text in single quotes. So, the FIND string would
>>> be: Description = 'John MacDonald'. Works great until I ran into
>>> trouble. I found that some descriptions have single quotes (could be
>>> any special character) in them. So, my FIND string is now:
>>> Description = 'John Mc'Donald'. This causes an error that I can not
>>> seem to get around. I tried using double quotes instead like,
>>> Description = "John Mc'Donald", but I still get the error message.
>>>
>>> How do you FIND text when the text contains special characters?
>>>
>>>
>>> Thanks for any help,
>>>
>>> Charles
>>
>>
>> In both SQL and vbscript, "special" characters can be "escaped" by
>> doubling them up. The only characters that need to be escaped are
>> the quote characters that would normally be interpreted as string
>> delimiters. Take your example:
>>
>> Description = 'John Mc'Donald'
>>
>> In this expression, a single quote is being used to delimit the
>> string, so that is the default interpretation the parser will use
>> when it encounters that character in your expression. The problem
>> arises because the same character might also be interpreted
>> literally as an apostrophe, which is how you intend it to be
>> interpreted in your example. To tell the parser to interpret it
>> literally, escape it by doubling it up"
>>
>> Description = 'John Mc''Donald'
>>
>> That's two apostrophes, not a single double-quote character. When
>> the parser encounters those characters, it will interpret them as a
>> single apostrophe. Again, both vbscript and SQL work the same way.
>> Other languages may be different. For example, in jscript,
>> characters are escaped using a backspace (\).
>>
>> I usually use the Replace function to replace any occurrences of an
>> apostrophe with two apostrophes when I'm using single quotes for
>> delimiters:
>>
>> sSearch = "John Mc'Donald"
>> rs.Find "Description = '" & Replace(sSearch,"'","''")
>>
>> HTH,
>> Bob Barrows 

-- 
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Relevant Pages

  • [TOMOYO #15 3/8] Common functions for TOMOYO Linux.
    ... This file contains common functions (e.g. policy I/O, pattern matching). ... Since TOMOYO Linux is a name based access control, ... TOMOYO Linux's string manipulation functions make reviewers feel crazy, ... the Linux kernel accepts all characters but NUL character ...
    (Linux-Kernel)
  • RfD: Escaped Strings version 4
    ... the S" string can only contain printable characters, ... the S" string cannot contain the '"' character, ... as an escape character for the entry of characters that cannot be ... \b BS (backspace, ASCII 8) ...
    (comp.lang.forth)
  • RfD: Escaped Strings version 4
    ... the S" string can only contain printable characters, ... the S" string cannot contain the '"' character, ... as an escape character for the entry of characters that cannot be ... \b BS (backspace, ASCII 8) ...
    (comp.lang.forth)
  • Re: RfD: Escaped Strings
    ... the S" string can only contain printable characters, ... the S" string cannot contain the '"' character, ... \b BS (backspace, ASCII 8) ... \ ** escapes to characters much as C does. ...
    (comp.lang.forth)
  • Re: Delimiting problems
    ... > So how can I delimit the " character? ... Now when I have a piece of string such as ... If you want to compare against several values, ...
    (comp.lang.c)
Loading