How do I Evaluate Null value from database?

[justapimp]

I have been looking for a way to evaluate if a column in a database has a null value in it. So far, every method suggested have failed and I would like to know anyone else have successfully evaluate a null value in C# Visual Studio 2005.

[bbqchickenrobot]

you can use nullable types i.e. - int? myVar = ... ;

you can also check to see if the value is = to System.DbNull...

[justapimp]

How exactly do I do that, I've use IsDBNull, Convert.IsDBNull, IsDBNull.Value and nothing works and I've tried type casting. ????

[ravichandra]

The following can help you but i am sure whether this is the right way.

While selecting the column check for null values like

select ISNULL(class,-1) from student

If the column class contains null values,the query returns -1 value.
Using this value you can identity the column contains null value.

Good Luck.

[hollystyles]

You need to compare the column value with System.DBNull.Value

So assuming a DataTable for example:

if( myDataTable.Rows[0]["someColumn"].Equals(System.DBNull.Value) )
{
          //Handle situation for null value.
}

[justapimp]

I tried that method already and it does not work.

[hollystyles]

Hmm... well it works for me. You'll have to post some code or give more information.

[ericstenson]

you can also get the value of the cell and use .text.tostring
then test the length of the string len(abc) > 0

[justapimp]

Here is the code

[WebMethod]
    public int TestDBNullValue(string PID)
    {
        int ErrorValue = 0;
        if (PID.Equals(String.Empty))
        {
            ErrorValue = 10110; //PID is null or empty
        }
        else
        {
            DeviceTableAdapter DeviceAdapter = new DeviceTableAdapter();
            JeanPierre.DeviceDataTable DeviceTable = DeviceAdapter.GetDeviceUIDByPID(PID);

            if (DeviceTable.Count.Equals(0))
            {
                ErrorValue = 1111; //No device found
            }
            else
            {
                JeanPierre.DeviceRow DevRows = DeviceTable[0];

                if(DevRows.TrackingMode.Equals(System.DBNull.Value))
                {
                    ErrorValue = 1;
                }
                else
                {
                    ErrorValue = 41;
                }
            }
        }
        return (ErrorValue);
    }

[ericstenson]

i converted from vb.net to c#, so it might not be accurate...

{
if (Strings.len(PID) == 0) {
}
}

[justapimp]

That part of the code works fine. What I was doing there is evaluating whether the query return a row and if it did, I'll move on to the next step and evaluate if the data in the column is NULL (The part I can't get to work).

[ericstenson]

why don't you count the rows?

[justapimp]

with the above code it is always guarantee to have a row returned so I don't need to count it. the problem is whether a column is null or not.

[ericstenson]

so test the length of the text in a cell.

[hollystyles]

Aha, you are using a strongly typed DataTable.

You must be handling the DBNull somehow otherwise you would be getting an exception when populating the table.

Have you tried just == null ?

[justapimp]

Already have and its still the same. Somebody suggested that I use the is null in the query itself but I've never constructed a query like that before. Can anyone of you let me know if it is possible to do that?

[streamline]

There is a good article here on coding for evaluating a null assignment to a database field.

My working solution solution based upon the reading from the page below is as follows

strTemp = rs.Fields(1)
strTemp = "" & strTemp ' to resolved database null evaluation in an IF statement
If strTemp = "" then
returned_rs.stremailaddres = "some string"
else
returned_rs.stremailaddress = rs.Fields(1)
endif

read for more details
http://www.brainbell.com/tutors/Visual_Basic/newfile073.html

[Kusno]

Why don't you try to check null in SQL query ?

SELECT ISNULL(EmployeeAddress,'') as EmployeeAddress FROM MasterEmployee ORDER BY EmployeeID

in code :

If Rd.Item("EmployeeAddress").toString.Trim() ="" Then

End Id

Thanks.