PRB: Cannot Filter ADO Recordsets for Nulls

Exención de responsabilidades de contenido KB retirado

Este artículo se refiere a productos para los que Microsoft ya no ofrece soporte técnico. Por tanto, el presente artículo se ofrece "tal cual" y no será actualizado.

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=<username> and PWD=<strong password> 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=<username>;pwd=<strong password>;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"

References

Data Access SDK Help; search on: "Filter Method"


ADO Help; search on: "Filter Method"
Propiedades

Id. de artículo: 187871 - Última revisión: 06/22/2014 - Revisión: 1

Comentarios