| Ticket Hash: | a75e1ecfe140fad3a396b13aa19b12046030e8dd | |||
| Title: | cannot detect null or empty in field with parameterized query | |||
| Status: | Closed | Type: | Incident | |
| Severity: | Important | Priority: | Medium | |
| Subsystem: | Native_Assembly | Resolution: | Works_As_Designed | |
| Last Modified: | 2018-04-26 16:40:36 | |||
| Version Found In: | 3.22 | |||
| User Comments: | ||||
anonymous added on 2018-03-25 12:58:24:
(text/x-fossil-plain)
Hello,
Here are is how the fields are defined:
" PersId INTEGER PRIMARY KEY UNIQUE NOT NULL, " +
" MiddleName NCHAR (50) DEFAULT NULL," +
" BirthDate TEXT NULL DEFAULT ''," +
" Gender NCHAR (1) NULL," +
I inserted rows with NULL in the NCHAR and TEXT fields.
Then, searching for NULL values in these fields did no return anything.
Example query:
"SELECT * FROM tblPerson WHERE MiddleName = @MiddleName "
Example setting parameter:
oDataAdapter.SelectCommand.Parameters.AddWithValue(("@MiddleName"), DBNull.Value)
Note that this issue manifests only with parameterized queries.
Thank you.
Florin
mistachkin added on 2018-03-26 18:24:00: (text/x-fossil-plain) Checking for equality with NULL in SQL may not produce the desired results. In general, expressions that have one or more NULL operands will produce a NULL result. The SQL query in this ticket should probably be revised to use the IS NULL check with an OR operator in order to find the matching rows. | ||||
