Microsoft KB Archive/187871

= PRB: Cannot Filter ADO Recordsets for Nulls =

Article ID: 187871

Article Last Modified on 3/2/2005

-

APPLIES TO


 * Microsoft ActiveX Data Objects 2.0
 * Microsoft Data Access Components Software Development Kit 2.0
 * Microsoft Data Access Components Software Development Kit 2.1

-



This article was previously published under Q187871



SYMPTOMS
Using the Filter property of an ActiveX Data Objects (ADO) 2.0 Recordset to filter out records containing NULLs always fails. It may fail with the following error or may accept the Filter property, but fail to apply the filter:

Run-time error '3001': The application is using arguments that are of the wrong type, are out of acceptable range, or are in conflict with one another.



CAUSE
It is not possible to use the ADO Recordset's Filter property to filter records for NULL values using ADO 2.0.



RESOLUTION
Change the SQL statement used to populate the recordset so that it handles the nulls. For instance, the following would only retrieve the records that have a null value for state field:

rs.Open "select * from authors where state is null", cn

Another workaround is to first loop through the recordset and build an array of bookmarks for the records where the desired column is null (or not null) and then filter on the bookmark array. This method does require the recordset to support bookmarks.

Sample Code
Note You must change User UID= and PWD= to the correct values before you run this code. Make sure that UID has the appropriate permissions to perform this operation on the database. 'Don't forget to add a reference to the 'Microsoft ActiveX Data Objects Library Dim cn As New Connection Dim rs As New Recordset Dim vFields As Variant

'This example is connecting to SQL Server's Sample Pubs database. 'You may want to change the server name to reference your local 'SQL Server cn.Open "driver={SQL Server};" & _

"server=local;uid= ;pwd= ;database=pubs"

rs.CursorLocation = adUseClient 'Create some records with Null values cn.Execute ("update authors set state = NULL where state = 'UT'")

rs.Open "select * from authors", cn, adOpenStatic, adLockBatchOptimistic

'Build an array of bookmarks of records where desired field contains 'null. i = 0 Do While rs.EOF <> True

If IsNull(rs(6).Value) Then ReDim Preserve vFields(i) vFields(i) = rs.Bookmark i = i + 1 End If     rs.MoveNext

Loop

Debug.Print rs.RecordCount rs.Filter = vFields Debug.Print rs.RecordCount

'Reverse the changes made by the .Execute call above. cn.Execute ("update authors set state = 'UT' where state = NULL")



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"

