ADO Error 3001 1

[Andrzejek]

Search on Google and on this Forum did not help, so here it is....

I am trying to Filter my ADODB recordset:

strSQL = "SELECT MYITEM.PSQTY_ITEM_ICDE1, MYITEM.PSQTY_CATEGORY_KEY, " _
    & " MYITEM.PSQTY_PROJECTNUMBER, MYITEM.PSQTY_ITEM_NO, " _
    & " MYITEM.PSQTY_DESIGN_QUANTITY, MYITEM.PSQTY_ITEM_SUPPLEMENT1, " _
    & " MYITEM.PSQTY_ITEM_SUPPLEMENT2, MYITEM.PSQTY_ITEM_SUPPLEMENT3 " _
    & " FROM S4111000.PSITEM_QUANTITIES MYITEM, S4111000.PSLET_INFORMATION MYLET " _
    & " WHERE MYITEM.PSQTY_PROJECTID = MYLET.PSLET_PROJECTID " _
    & " AND MYITEM.PSQTY_PROJECTNUMBER = MYLET.PSLET_PROJECTNUMBER " _
    & " AND (MYLET.PSLET_CONTRACTLETDATE = TO_DATE('" & LET_DATE & "', 'MM/DD/YYYY')) " _
    & " AND (MYITEM.PSQTY_ITEM_ICDE1 IS NOT NULL)"

recOne.Open strSQL, Cn

recOne.Filter = " (PSQTY_ITEM_SUPPLEMENT1 = '" & strSupl1 & "') " [blue]_
& " AND (PSQTY_ITEM_SUPPLEMENT2 IS NULL) " _
& " AND (PSQTY_ITEM_SUPPLEMENT3 IS NULL)"[/blue]

For some reason Filter errors when using all Filter (including code in Blue): Run-time error 3001 - Arguments are of the wrong type, are out of acceptable range or are in conflict with one another.

If Blue piece of code is not included, Filter works fine.

So, what's the deal with IS NULL in ADODB Filter?

I can take entire Filter and run the code in TOAD or any other way in my database (I use Data Environment) and it is fine, IS NULL works the way it should.

Any ideas.....?

Have fun.

---- Andy

 

[Andrzejek]

MicroSoft's Help and Support page:

CAUSE
It is not possible to use the ADO Recordset's Filter property to filter records for NULL values using ADO 2.0.
(...)
STATUS
This problem applies to version 2.0 of MDAC. This problem has been fixed in MDAC 2.5 or later. The following code filters the recordset in MDAC 2.5 or later:

rs.filter= "state=null"

I did change to 2.5, and 2.7 and 2.8 - still the same problem :-(

Have fun.

---- Andy

 

[harebrain]

Get rid of the parentheses. You don't need them, and it seems that Filter doesn't behave with ANDed grouped expressions.

 

[JoeAtWork]

I'm not sure if this would make a difference, but you have two spaces between the closing parenthesis and each AND

Maybe change to this:

& "AND (PSQTY_ITEM_SUPPLEMENT2 IS NULL) " _
& "AND (PSQTY_ITEM_SUPPLEMENT3 IS NULL)"

 

[ChewDoggie]

Try specifying a client-side cursor:

recOne.CursorLocation = adUseClient
recOne.Open strSQL, Cn

ChewDoggie



The True measure of an individual is how he treats a person who can do him absolutely no good.

 

[Andrzejek]

harebrain - parentheses do not matter.

JoeAtWork - spaces do not matter.

ChewDoggie - I already have adUseClient

Looks to me IS NULL does not work in a Filter. I ended up using a simple work around - I created a separate recordset since it is a 'one shot deal' and it will not be used a lot.

Thanks everybody.


Have fun.

---- Andy

 

[JerryKlmns]

Operator must be one of the following: <, >, <=, >=, <>, =, or LIKE.

Looks like IS NULL is not an option.

Would this work?

& "AND (PSQTY_ITEM_SUPPLEMENT2 & '' = '') " _
& "AND (PSQTY_ITEM_SUPPLEMENT3 & '' = '')"

 

[SBerthold]

In the filter property, this works for me:

Want no NULLs:

rs.Filter = "(AnyField<>null)"

Want NULLs:

rs.Filter = "(AnyField=null)"

Build a table with 3 fields (fld1=text, fld2=long, fld3=text) and no default values, and no input required, and "Allow zero length" for the text fields.

Add two records, one with all fields with values, and one with only the first field with a value (A1 for the first record and B2 for the second).
So, record 1 with fld1="A1" has NULLs in fld2 and fld3. Record "B2" doesn't.

Build your ado recordset (client cursor) off of this table.
Check the recordcount. Should be 2.

Use the filter:
rs.Filter = "(fld1<>null AND fld2=null AND fld3=null)"

Check the recordcount and the first field's value. Should be 1 and "A1"

Use the filter:
rs.Filter = "(fld1<>null AND fld2<>null AND fld3<>null)"

Check the recordcount and the first field's value. Should be 1 and "B1"

 

[Andrzejek]

My SQL teacher told me:

Nothing equals to NULL, not even a NULL.

In my PL/SQL against ORACLE it does not work: <> NULL or = NULL. Is has to be IS NULL or IS NOT NULL

Have fun.

---- Andy

 

[JerryKlmns]

Is has to be IS NULL or IS NOT NULL

Help says you cannt

 

[SBerthold]

>In my PL/SQL against ORACLE it does not work:

Here, using a client cursor, it has nothing to do with any dbms provider.

It has to do with the client adodb provider, which is not dependent on the dbms.

The client provider is adodb's own provider, and the recordset is just based on the data and meta data given by the provider, and not on the dbms provider's syntax.

So, as JerryKlmns has pointed out, if the ado client provider says it wants "= NULL" and cannot interpert "IS NULL", then so it is, and will only work as such.

You need to make sure the recordset is using a client cursor in order to see this. Contrary, a server side cursor uses the dbms provider's syntax, and there you need in most cases "IS NULL".

 

[SBerthold]

>, and not on the dbms provider's syntax.

Bad english.

Should read:

, and then [after that] does not use the dbms provider's syntax.

 

[BobRodes]

Good summary SB. For what it's worth, I noticed that = null worked in SQL Server, so I went and tried it in Query Analyzer to be sure and it also works there. IS NULL and = NULL are interchangeable in SQL Server, it seems.

 

[JoeAtWork]

>> IS NULL and = NULL are interchangeable in SQL Server, it seems.

I'm pretty sure that's a configurable setting. If I had to figure it out, I would probably search under ANSI NULL in books online.

 

[SBerthold]

IMO, "Value = NULL" is logically wrong and the "IS" operator is not the same as the "=" operator.

IS NULL and = NULL are interchangeable in SQL Server, it seems

IMO, it shouldn't really be used [any longer], but for backward compatibility where some dbms' used to allow it, and now wants to conform to the sql standards, those may still allow it.

In the ADO client provider, the "IS" AND "NOT" operators are not allowed. I guess when they decided to build in a check for a NULL Marker: Unknown, they choose to use the equals sign, an already existing operator.
And, at this higher level, it probably doesn't make a difference, apart from understanding as to why it is used instead of "IS" and apart from the need to use standards, as it could make a difference at a dbms level.

 

[JerryKlmns]

Now tell me, aren't SBerthold's posts worth a pinky ?