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.

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

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.

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.
}

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

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);
    }

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).

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.

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 ?

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?

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

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.

This article has been dead for over six months. Start a new discussion instead.