CODEWITHSUNDEEP
c#asp.netsql-server
vyclarks
vyclarks

Reputation: 878

Field NULL in SQL

I have a table in DB:

REGULAR(num(bigint) not allow null, title(nvarchar(10)), content(nvarchar(500)), path(nvarchar(50)) allow null)

with some data:

1.REGULAR1(1|title1|content1|path1)
2.REGULAR2(2|title2|content2|)--> path is not inputed (null)

I excute some query in SQL DB server 2008:

1. select PATH from REGULAR where num='2'; -> result is PATH:NULL

But

2. select count(*) from REGULAR where PATH = NULL; --> result is COUNT:0 (it must be 1 which has num=2)

Therefore, when I excute a queries from Webform it works wrong

string sql= select * from REGULAR;
Datatable regular= excute(sql)....
for(int i=0;i<regular.Rows.Count; i++)
{
    if(regular.Rows[i]["path"]!=null)
       {
            Textbox1.Text= "a";//do something else...
       }
    else 
        Textbox1.Text+="b";//...
}

and the result is: Textbox.Text= "ab"---> it much be ="a". Is there any mistake???

Upvotes: 0

Views: 85

Answers (3)

Damith
Damith

Reputation: 63065

It is not possible to test for NULL values with comparison operators, such as =, <, or <>.

We will have to use the IS NULL and IS NOT NULL operators instead.

if you have two records with one with path null and another with path not null

what you get as result is correct, because one item append a to textbox and another append b to textbox. so the final result is ab

Upvotes: 3

Samiey Mehdi
Samiey Mehdi

Reputation: 9414

replace 

if(regular.Rows[i]["path"]!=null)

with:

if(!String.IsNullOrEmpty(regular.Rows[i]["path"]))

Upvotes: 1

volkh4n
volkh4n

Reputation: 412

you should use "null" with "is":

select count(*) from Regular where PATH is NULL

Upvotes: 3

Related Questions