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
[url]www.associatedbtg.com[/url]

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

"David Lozzi" <dlozzi@associatedbtg.com> wrote in message
news:OR9WBicXDHA.388@TK2MSFTNGP10.phx.gbl...

> 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
> [url]www.associatedbtg.com[/url]
>
> I should've known that....but I had a brain fart.
>
>

[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



"David Lozzi" <dlozzi@associatedbtg.com> wrote in message
news:%23Fc3ovcXDHA.1900@TK2MSFTNGP10.phx.gbl...

> 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
> [url]www.associatedbtg.com[/url]
>

[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

"David Lozzi" <dlozzi@associatedbtg.com> wrote in message
news:%23Fc3ovcXDHA.1900@TK2MSFTNGP10.phx.gbl...

> 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,
>
> --

[Mike]

"Ray at <%=sLocation%>" <myfirstname at lane34 dot com> wrote in message
news:eQJT$PdXDHA.3248@tk2msftngp13.phx.gbl...

> 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

[Ray at]

I mean property. Eegs.

Ray at work

"Ray at <%=sLocation%>" <myfirstname at lane34 dot com> wrote in message
news:%236HkU1dXDHA.2648@TK2MSFTNGP09.phx.gbl...

> 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@TRASHaspfaq.com> wrote in message
> news:e9JmywdXDHA.2476@tk2msftngp13.phx.gbl...

> > > 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?
> >
> >

>
>

[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 [url]www.hell.666[/url] site for Lucifer.

Ray at work

"Aaron Bertrand - MVP" <aaron@TRASHaspfaq.com> wrote in message
news:OGEGV8dXDHA.2344@TK2MSFTNGP09.phx.gbl...

> > I mean property. Eegs.

>
> Oh, you're going to hell now, because when you said "method" nobody knew
> what you were talking about.
>
>

[Matt Smith]

"David Lozzi" <dlozzi@associatedbtg.com> wrote in message
news:OR9WBicXDHA.388@TK2MSFTNGP10.phx.gbl...

> 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
[url]www.associatedbtg.com[/url]

I should've known that....but I had a brain fart.
"Aaron Bertrand - MVP" <aaron@TRASHaspfaq.com> wrote in message
news:OCPVpkBYDHA.2572@TK2MSFTNGP09.phx.gbl...

> > 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]

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
[url]www.associatedbtg.com[/url]

I should've known that....but I had a brain fart.
"Aaron Bertrand - MVP" <aaron@TRASHaspfaq.com> wrote in message
news:OCPVpkBYDHA.2572@TK2MSFTNGP09.phx.gbl...

> > 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]

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" <dlozzi@associatedbtg.com> wrote in message
news:u3h7T6BYDHA.2572@TK2MSFTNGP09.phx.gbl...

> 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
> [url]www.associatedbtg.com[/url]
>
> I should've known that....but I had a brain fart.
> "Aaron Bertrand - MVP" <aaron@TRASHaspfaq.com> wrote in message
> news:OCPVpkBYDHA.2572@TK2MSFTNGP09.phx.gbl...

> > > 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]

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" <dlozzi@associatedbtg.com> wrote in message
news:u3h7T6BYDHA.2572@TK2MSFTNGP09.phx.gbl...

> 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
> [url]www.associatedbtg.com[/url]
>
> I should've known that....but I had a brain fart.
> "Aaron Bertrand - MVP" <aaron@TRASHaspfaq.com> wrote in message
> news:OCPVpkBYDHA.2572@TK2MSFTNGP09.phx.gbl...

> > > 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]

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" <aaron@TRASHaspfaq.com> wrote in message
> news:OCPVpkBYDHA.2572@TK2MSFTNGP09.phx.gbl...

>>> 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]

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" <aaron@TRASHaspfaq.com> wrote in message
> news:OCPVpkBYDHA.2572@TK2MSFTNGP09.phx.gbl...

>>> 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]

Bob Barrows wrote:

> 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
[url]www.associatedbtg.com[/url]

I should've known that....but I had a brain fart.
"Bob Barrows" <reb_01501@yahoo.com> wrote in message
news:#sqdZuCYDHA.2352@TK2MSFTNGP12.phx.gbl...

> 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" <aaron@TRASHaspfaq.com> wrote in message
> > news:OCPVpkBYDHA.2572@TK2MSFTNGP09.phx.gbl...

> >>> 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]

OK, I will work with that.

Thank you all for your support.

--
David Lozzi
Associated Business & Technology Group
[url]www.associatedbtg.com[/url]

I should've known that....but I had a brain fart.
"Bob Barrows" <reb_01501@yahoo.com> wrote in message
news:#sqdZuCYDHA.2352@TK2MSFTNGP12.phx.gbl...

> 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" <aaron@TRASHaspfaq.com> wrote in message
> > news:OCPVpkBYDHA.2572@TK2MSFTNGP09.phx.gbl...

> >>> 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.

>
>