Re: ADO Recordset FIND problem
From: Al Dunbar [MS-MVP] (alan-no-drub-spam_at_hotmail.com)
Date: Sun, 30 May 2004 14:18:48 -0600
"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
> 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
> your SQL strings, you have to escape the SQL string delimiter when
> 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
> string, and the query engine that executes the string. For example, you
> 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
> 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
As you say a bit farther down, it can be tough to keep track of this. I
think you missed a double quote, i.e.:
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""
> It can be tough to keep track of this, which is one of the reasons I
> to avoid dynamic sql, using stored procedures or saved parameter queries
> 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"