How do I write an SQL statement that queries all records with no value for a field? (ASP)

Sometimes we need to query all records with no value for a field. Should we use RS ("XX") = "" Or isnull (RS ("XX? To draw a conclusion, we have to start from the beginning: under what circumstances will the field have no value? This is closely related to our conclusion.

1. What causes no value in the field?

We will create a table to test it. The table has only two field IDS (automatic numbers) and AA (text ):

1. Set the "allow Null String" attribute of the AA field to "no". Test Results

Reference content RS ("AA") = "mzwu.com" ---> added successfully
RS ("AA") = "" ---> the field 't8. A' cannot be a zero-length string.
RS ("AA") = empty ---> added successfully
RS ("AA") = NULL ---> added successfully

2. Set the "allow Null String" attribute of the AA field to yes. Test Result

Reference content RS ("AA") = "mzwu.com" ---> added successfully
RS ("AA") = "" ---> added successfully
RS ("AA") = empty ---> added successfully
RS ("AA") = NULL ---> added successfully

After the test, the table records in the database are as follows:

From the test results above, it is not difficult to draw a conclusion that the absence of field values may be one of the following four situations:

● RS ("AA") = ""
● RS ("AA") = empty
● RS ("AA") = NULL
● When multiple fields exist in the table, some fields add values. If some fields are not added, the field does not have a value.

Ii. RS ("AA") = "" Or isempty (RS ("AA") or isnull (RS ("AA "))

We use RS ("AA") = "", isempty (RS ("AA ")) and isnull (RS ("AA:

1. Test RS ("AA") = ""

Reference content RS ("AA") = "" 'returns true
Isempty (RS ("AA") 'returns false
Isnull (RS ("AA") 'returns false

2. Test RS ("AA") = empty

Reference content RS ("AA") = "" 'result-
Isempty (RS ("AA") 'returns false
Isnull (RS ("AA") 'returns true

3. Test RS ("AA") = NULL

Reference content RS ("AA") = "" 'result-
Isempty (RS ("AA") 'returns false
Isnull (RS ("AA") 'returns true

4. Test fields with no value added

Reference content RS ("AA") = "" 'result-
Isempty (RS ("AA") 'returns false
Isnull (RS ("AA") 'returns true

OK. Now we can draw a conclusion:
When you use RS ("XX") = "" to add a field value, we apply RS ("XX") = "" to determine whether it has no value;
When you use RS ("AA") = NULL or RS ("AA") = empty to add a field value or do not add a value to the field, we apply isnull (RS ("XX ")) to determine whether it has no value;
Therefore, to obtain accurate results, use RS ("XX") = "" Or isnull (RS ("XX") to query all records with no values in the field "))

: For fields without values, the RS ("XX") & "" = "" result is true and is irrelevant to the adding method. Therefore, you can directly use RS ("XX ") & "" = "" replace rs ("XX") = "" Or isnull (RS ("XX "))