Re: ADO Recordset FIND problem

From: Al Dunbar [MS-MVP] (alan-no-drub-spam_at_hotmail.com)
Date: 05/30/04


Date: Sun, 30 May 2004 14:18:48 -0600


"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:uAEOHdeREHA.1396@TK2MSFTNGP12.phx.gbl...
> 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

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"

/Al

> 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

  • Re: how can i change the text delimiter
    ... we receive the data in csv format ... ... Defines the character used to quote fields that ... Defines the character that will be used to separate ... Extracts fields from the CSV record in string. ...
    (comp.lang.python)
  • Re: Difference between and : symbols
    ... lisp> ... You can inhibit evaluation with the special operator QUOTE. ... The specific thing that confused you is why ASDF functions ... But what ASDF really wants is a string. ...
    (comp.lang.lisp)
  • Re: whats wrong with this code
    ... fgetcreturns an int and that's for a good reason: EOF isn't a char ... And what happens if the string you wrote into the file was empty and the ... delimiter character is all there is for that string? ... to find that out because you don't look at the first character. ...
    (comp.unix.programmer)
  • Re: ADO Recordset FIND problem
    ... That is the only character that can be confused with a string ... Keep in mind, if you use a double quote as a string delimiter, ... if you wish to use double quotes to delimit ...
    (microsoft.public.scripting.vbscript)
  • RE: Which is better?
    ... > You've lost me here Thomas. ... if you want to use the double quote character ... inside of such a string, you have to escape that character ...
    (perl.beginners)