System.Data.SQLite
View Ticket
Not logged in
Ticket UUID: 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:
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:
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.