Using NOT LIKE in Query String????

[David Lozzi]

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]

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

Post your SQL string and your database type and info.

Ray at work

 

[Ray at]

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

Ray at work

 

[Ray at]

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

 

[Mike]

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.

 

[Ray at]

I mean property. Eegs.

Ray at work

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]

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]

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]

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

 

[Matt Smith]

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

 

[David Lozzi]

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.

 

[TomB]

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?

 

[Bob Barrows]

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

 

[Bob Barrows]

No. He (David, the OP) has not sent a SQL statement to a database.

I take that back.
Bob

 

[David Lozzi]

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.