Using NOT LIKE in Query String????

Discussion in 'ASP General' started by David Lozzi, Aug 8, 2003.

  1. David Lozzi

    David Lozzi Guest

    OK, I know how to use [field LIKE '%keyword%'] to pull up all records that
    contains the keyword, but what about not containing the keyword? I've tried
    NOT LIKE but i get

    Arguments are of the wrong type, are out of acceptable range, or are in
    conflict with one another

    Any ideas?
    Thanks!

    --
    David Lozzi
    Associated Business & Technology Group
    www.associatedbtg.com

    I should've known that....but I had a brain fart.
     
    David Lozzi, Aug 8, 2003
    #1
    1. Advertising

  2. David Lozzi

    Ray at Guest

    SELECT [whatever] FROM [whatever] WHERE [whatever] NOT LIKE '%whatever%'

    Post your SQL string and your database type and info.

    Ray at work

    "David Lozzi" <> wrote in message
    news:...
    > OK, I know how to use [field LIKE '%keyword%'] to pull up all records that
    > contains the keyword, but what about not containing the keyword? I've

    tried
    > NOT LIKE but i get
    >
    > Arguments are of the wrong type, are out of acceptable range, or are in
    > conflict with one another
    >
    > Any ideas?
    > Thanks!
    >
    > --
    > David Lozzi
    > Associated Business & Technology Group
    > www.associatedbtg.com
    >
    > I should've known that....but I had a brain fart.
    >
    >
     
    Ray at, Aug 8, 2003
    #2
    1. Advertising

  3. David Lozzi

    Ray at Guest

    Yes, I see. I also get the same error when trying NOT like. Why are you
    doing this like this?

    Ray at work



    "David Lozzi" <> wrote in message
    news:%...
    > I see. I am using the filter option of the recordset. I am just loading

    the
    > recordset with my own information, this is not being pulled from a
    > database... like this:
    >
    > set rec = server.CreateObject("ADODB.Recordset")
    > rec.CursorLocation = 3
    > rec.Fields.Append "user", adVarChar, 255
    > rec.Fields.Append "supervisor", adVarChar, 255
    > ........
    > rec.Fields.Append "HazardDate", adDate
    > rec.Fields.Append "OSHA", adVarChar, 10
    > rec.Open
    > rec.filter = filt
    >
    > if NOT LIKE is used for SQL queries, is there an option for this scenario?
    >
    > thanks,
    >
    > --
    > David Lozzi
    > Associated Business & Technology Group
    > www.associatedbtg.com
    >
     
    Ray at, Aug 8, 2003
    #3
  4. David Lozzi

    Ray at Guest

    I cannot find any definitive statement indicating that NOT like is not
    supported in the filter method of an RS, but I do see others having the
    problems without any solutions, out there on the WWW. Hmm. Perhaps someone
    else has read this thread this far and can enlighten us.

    Ray at work

    "David Lozzi" <> wrote in message
    news:%...
    > I see. I am using the filter option of the recordset. I am just loading

    the
    > recordset with my own information, this is not being pulled from a
    > database... like this:
    >
    > set rec = server.CreateObject("ADODB.Recordset")
    > rec.CursorLocation = 3
    > rec.Fields.Append "user", adVarChar, 255
    > rec.Fields.Append "supervisor", adVarChar, 255
    > ........
    > rec.Fields.Append "HazardDate", adDate
    > rec.Fields.Append "OSHA", adVarChar, 10
    > rec.Open
    > rec.filter = filt
    >
    > if NOT LIKE is used for SQL queries, is there an option for this scenario?
    >
    > thanks,
    >
    > --
     
    Ray at, Aug 8, 2003
    #4
  5. David Lozzi

    Mike Guest

    "Ray at <%=sLocation%>" <myfirstname at lane34 dot com> wrote in message
    news:eQJT$...
    > I cannot find any definitive statement indicating that NOT like is not
    > supported in the filter method of an RS, but I do see others having the
    > problems without any solutions, out there on the WWW. Hmm. Perhaps

    someone
    > else has read this thread this far and can enlighten us.


    Can you use not as a logical operator in those statements? For simplicity,
    something like NOT TRUE?

    If so, then it sounds like a syntax issue. Are precedence operators
    allowed? You could try to evaluate LIKE '%xxx%' and then negate it with a
    NOT.

    NOT (LIKE '%xxx%')

    I don't know why it would be so picky about syntax, but it's worth a try.

    --
    Mike
     
    Mike, Aug 8, 2003
    #5
  6. David Lozzi

    Ray at Guest

    I mean property. Eegs.

    Ray at work

    "Ray at <%=sLocation%>" <myfirstname at lane34 dot com> wrote in message
    news:%...
    > Aaron, would you happen to know if "not like" is officially unsupported in
    > the filter method?
    >
    > And I 100% agree, but OP did not answer why he chose this method.
    >
    > Ray at work
    >
    > "Aaron Bertrand - MVP" <> wrote in message
    > news:...
    > > > I see. I am using the filter option of the recordset.

    > >
    > > Why? Build a SQL string with a WHERE clause. Much more efficient and

    > less
    > > problematic... if you're only interested in the records that are not

    like
    > > '%whatever%', why bother bringing back ALL rows and then filtering them?
    > >
    > >

    >
    >
     
    Ray at, Aug 8, 2003
    #6
  7. > Aaron, would you happen to know if "not like" is officially unsupported in
    > the filter method?


    I think Tom's link implies that it is not supported.

    If the search string is only one character, you can mimic a NOT LIKE using
    LIKE, at least against SQL Server, this way (try it in Query Analyzer):



    DECLARE @strPattern VARCHAR(32)
    SET @strPattern = 'o'

    CREATE TABLE [dbo].[fakeUsers]
    (
    [name] [VARCHAR] (32)
    )

    INSERT fakeUsers VaLUES('frank')
    INSERT fakeUsers VaLUES('bob')
    INSERT fakeUsers VaLUES('moo')

    SELECT * FROM fakeUsers WHERE name NOT LIKE '%'+@strPattern+'%'
    SELECT * FROM fakeUsers WHERE name LIKE REPLICATE('[^'+@strPattern+']',
    DATALENGTH(name))

    DROP TABLE fakeUsers


    However I'm not confident that ADO will interpret this the same way. And it
    won't work quite as easily if the search sequence is larger than one
    character...

    A
     
    Aaron Bertrand - MVP, Aug 8, 2003
    #7
  8. > I mean property. Eegs.

    Oh, you're going to hell now, because when you said "method" nobody knew
    what you were talking about.
     
    Aaron Bertrand - MVP, Aug 8, 2003
    #8
  9. David Lozzi

    Ray at Guest

    Considering I think I said it multiple times, I think you're right. I'm
    already working on www.hell.666 site for Lucifer.

    Ray at work

    "Aaron Bertrand - MVP" <> wrote in message
    news:...
    > > I mean property. Eegs.

    >
    > Oh, you're going to hell now, because when you said "method" nobody knew
    > what you were talking about.
    >
    >
     
    Ray at, Aug 8, 2003
    #9
  10. David Lozzi

    Matt Smith Guest

    "David Lozzi" <> wrote in message
    news:...
    > OK, I know how to use [field LIKE '%keyword%'] to pull up all records that
    > contains the keyword, but what about not containing the keyword? I've

    tried
    > NOT LIKE but i get
    >
    > Arguments are of the wrong type, are out of acceptable range, or are in
    > conflict with one another


    Since I've never used the filter property this is a long shot but my
    literature on the recordset object suggests that you should use relational
    operators (<, <=,>,>=,= and <>) for this sort of operation.
    Have you tried this already?

    Matt
     
    Matt Smith, Aug 11, 2003
    #10
  11. David Lozzi

    David Lozzi Guest

    What I am doing is reading through a recordset pulled from a SQL statement
    query. I am loading some of these fields along with other fields based on
    mathematical assumptions on the previous fields. For example:

    sql = Select statement
    rec = recordset

    do until sql.eof
    cnt = cnt + 1
    rec("a") = sql("a")
    rec("b") = sql("b")
    rec("c") = sql("d") - (sql("e") / 4")
    rec("d") = cnt
    sql.movenext
    loop

    now i want to be able to filter the rec recordset. THe user has the option
    to search NOT keyword. Can I repost a SQL Select statement against the rec
    recordset?

    thanks!

    --
    David Lozzi
    Associated Business & Technology Group
    www.associatedbtg.com

    I should've known that....but I had a brain fart.
    "Aaron Bertrand - MVP" <> wrote in message
    news:...
    > > Can you provide an actual full SQL statement that gives you the error?

    >
    > The problem is that the error is not coming from a SQL statement that

    David
    > is generating. The error is coming from attempting to apply a NOT LIKE
    > criteria to the filter property of an ADODB.Recordset. In other words,

    he's
    > already sent a SQL statement to the database, and now in the ASP code he
    > wants to limit the records he's already retrieved by applying a filter.
    > Which, IMHO, belongs in the WHERE clause of the original statement.
    >
    >
     
    David Lozzi, Aug 11, 2003
    #11
  12. David Lozzi

    TomB Guest

    I think you should consider showing a little more code.
    When you say you are reading through a recordset pulled from a SQL statement
    query....Does this mean you really are using a database? then you are
    creating the ad hoc recordset?

    Could you not use your "filter" logic before adding the item to the
    recordset?
    Would an array work better?




    "David Lozzi" <> wrote in message
    news:...
    > What I am doing is reading through a recordset pulled from a SQL statement
    > query. I am loading some of these fields along with other fields based on
    > mathematical assumptions on the previous fields. For example:
    >
    > sql = Select statement
    > rec = recordset
    >
    > do until sql.eof
    > cnt = cnt + 1
    > rec("a") = sql("a")
    > rec("b") = sql("b")
    > rec("c") = sql("d") - (sql("e") / 4")
    > rec("d") = cnt
    > sql.movenext
    > loop
    >
    > now i want to be able to filter the rec recordset. THe user has the option
    > to search NOT keyword. Can I repost a SQL Select statement against the rec
    > recordset?
    >
    > thanks!
    >
    > --
    > David Lozzi
    > Associated Business & Technology Group
    > www.associatedbtg.com
    >
    > I should've known that....but I had a brain fart.
    > "Aaron Bertrand - MVP" <> wrote in message
    > news:...
    > > > Can you provide an actual full SQL statement that gives you the error?

    > >
    > > The problem is that the error is not coming from a SQL statement that

    > David
    > > is generating. The error is coming from attempting to apply a NOT LIKE
    > > criteria to the filter property of an ADODB.Recordset. In other words,

    > he's
    > > already sent a SQL statement to the database, and now in the ASP code he
    > > wants to limit the records he's already retrieved by applying a filter.
    > > Which, IMHO, belongs in the WHERE clause of the original statement.
    > >
    > >

    >
    >
     
    TomB, Aug 11, 2003
    #12
  13. David Lozzi

    Bob Barrows Guest

    My initial instinct is to agree with Tom. I haven't seen any reason yet for
    the ad hoc recordset. That's not to say you don't have one: just that you
    haven't sufficiently explained why you need one. So far, there isn't
    anything you are doing that could not be done more efficiently with an
    array. Even if you do need the ad hoc recordset, the "NOT" functionality
    should be pushed back further into the SQL statement where it belongs (as
    suggested by Aaron and others). You're pulling too much data across the
    wire.

    Bob Barrows

    David Lozzi wrote:
    > What I am doing is reading through a recordset pulled from a SQL
    > statement query. I am loading some of these fields along with other
    > fields based on mathematical assumptions on the previous fields. For
    > example:
    >
    > sql = Select statement
    > rec = recordset
    >
    > do until sql.eof
    > cnt = cnt + 1
    > rec("a") = sql("a")
    > rec("b") = sql("b")
    > rec("c") = sql("d") - (sql("e") / 4")
    > rec("d") = cnt
    > sql.movenext
    > loop
    >
    > now i want to be able to filter the rec recordset. THe user has the
    > option to search NOT keyword. Can I repost a SQL Select statement
    > against the rec recordset?
    >
    > thanks!
    >
    >
    > I should've known that....but I had a brain fart.
    > "Aaron Bertrand - MVP" <> wrote in message
    > news:...
    >>> Can you provide an actual full SQL statement that gives you the
    >>> error?

    >>
    >> The problem is that the error is not coming from a SQL statement
    >> that David is generating. The error is coming from attempting to
    >> apply a NOT LIKE criteria to the filter property of an
    >> ADODB.Recordset. In other words, he's already sent a SQL statement
    >> to the database, and now in the ASP code he wants to limit the
    >> records he's already retrieved by applying a filter. Which, IMHO,
    >> belongs in the WHERE clause of the original statement.
     
    Bob Barrows, Aug 11, 2003
    #13
  14. David Lozzi

    Bob Barrows Guest

    Bob Barrows wrote:
    > No. He (David, the OP) has not sent a SQL statement to a database.


    I take that back.
    Bob
     
    Bob Barrows, Aug 11, 2003
    #14
  15. David Lozzi

    David Lozzi Guest

    OK, I will work with that.

    Thank you all for your support.

    --
    David Lozzi
    Associated Business & Technology Group
    www.associatedbtg.com

    I should've known that....but I had a brain fart.
    "Bob Barrows" <> wrote in message
    news:#...
    > My initial instinct is to agree with Tom. I haven't seen any reason yet

    for
    > the ad hoc recordset. That's not to say you don't have one: just that you
    > haven't sufficiently explained why you need one. So far, there isn't
    > anything you are doing that could not be done more efficiently with an
    > array. Even if you do need the ad hoc recordset, the "NOT" functionality
    > should be pushed back further into the SQL statement where it belongs (as
    > suggested by Aaron and others). You're pulling too much data across the
    > wire.
    >
    > Bob Barrows
    >
    > David Lozzi wrote:
    > > What I am doing is reading through a recordset pulled from a SQL
    > > statement query. I am loading some of these fields along with other
    > > fields based on mathematical assumptions on the previous fields. For
    > > example:
    > >
    > > sql = Select statement
    > > rec = recordset
    > >
    > > do until sql.eof
    > > cnt = cnt + 1
    > > rec("a") = sql("a")
    > > rec("b") = sql("b")
    > > rec("c") = sql("d") - (sql("e") / 4")
    > > rec("d") = cnt
    > > sql.movenext
    > > loop
    > >
    > > now i want to be able to filter the rec recordset. THe user has the
    > > option to search NOT keyword. Can I repost a SQL Select statement
    > > against the rec recordset?
    > >
    > > thanks!
    > >
    > >
    > > I should've known that....but I had a brain fart.
    > > "Aaron Bertrand - MVP" <> wrote in message
    > > news:...
    > >>> Can you provide an actual full SQL statement that gives you the
    > >>> error?
    > >>
    > >> The problem is that the error is not coming from a SQL statement
    > >> that David is generating. The error is coming from attempting to
    > >> apply a NOT LIKE criteria to the filter property of an
    > >> ADODB.Recordset. In other words, he's already sent a SQL statement
    > >> to the database, and now in the ASP code he wants to limit the
    > >> records he's already retrieved by applying a filter. Which, IMHO,
    > >> belongs in the WHERE clause of the original statement.

    >
    >
     
    David Lozzi, Aug 12, 2003
    #15
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Brian Henry

    subdomains pulling in like a query string

    Brian Henry, Jan 2, 2004, in forum: ASP .Net
    Replies:
    2
    Views:
    457
    Brian Henry
    Jan 2, 2004
  2. Patrick Kowalzick
    Replies:
    5
    Views:
    501
    Patrick Kowalzick
    Mar 14, 2006
  3. Roland Hall

    ASP LIKE query using parameterized query

    Roland Hall, Jan 12, 2007, in forum: ASP General
    Replies:
    8
    Views:
    232
    Roland Hall
    Jan 13, 2007
  4. roadrunner
    Replies:
    1
    Views:
    263
    Gunnar Hjalmarsson
    Feb 8, 2006
  5. nick
    Replies:
    1
    Views:
    513
    David Mark
    Feb 13, 2011
Loading...

Share This Page