NULL in where statement

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for universal data access
Post Reply
CodeManKS
Posts: 5
Joined: Wed 27 Mar 2013 10:50

NULL in where statement

Post by CodeManKS » Mon 25 Mar 2019 14:09

Hi,

i'm new on devConnect universal. Actually i have the task to update some rows by the statement "where b_id is NULL".

Here is my statement:

update test set b_id=:b_id where b_id is @b_id
Parameter b_id: 2
Parameter @b_id: DBNULL.value

This returns errors. How do I have to do this?

Thanks for help.

Pinturiccio
Devart Team
Posts: 2406
Joined: Wed 02 Nov 2011 09:44

Re: NULL in where statement

Post by Pinturiccio » Wed 27 Mar 2019 16:24

You can use parameters in static SQL statements only in positions where the SQL syntax allows a parameter. For example, you cannot provide table names as parameters.

Therefore, parameters can send only values, not query parts.

Parameters cannot be used for building a query from parts. IS NULL or IS NOT NULL are query clauses, not values, and a parameter cannot be treated as a query clause.

Thus, you need to perform this query without a parameter in the WHERE clause and specify the IS NULL clause in the query text.

CodeManKS
Posts: 5
Joined: Wed 27 Mar 2013 10:50

Re: NULL in where statement

Post by CodeManKS » Thu 28 Mar 2019 13:36

I wrote now the whole 'where statement' together and did not define and set the parameter. So it worked.
Thank you!

Post Reply