Just imagine that the CategoryID column in News_News table has a null value.

Now if call
public NewsDetails GetDetails(int newsID)
        {......
if (news.Tables[0].Rows.Count > 0)
                {
                    DataRow rowNews = news.Tables[0].Rows[0];
                                        details.CategoryID = (int)rowNews["CategoryID"];
.....}

This would give me an runtime error: System.InvalidCastException: Specified cast is not valid

Because CategoryID contained a NULL value instead of an int.

Its the same thing with a DataSet. If I bind a dataset to a datagrid and some of the columns contain a NULL values, again the code will break.

How can I catch these types of errors?? What can I do to ensure that when there are NULL values in the table, my code does not break.


Thanks

The first thing you need to understand is that NULL in a database column has a meaning: it means that column was not populated. NULLs are not evil, but they are misunderstood. Would it be right for me to let you do math on a NULL, or use it as a key for another table? No, and that's by design.

The thing that burns a lot of people is that NULL is NEVER equal to anything, not even another NULL.

If you are working with a column that MUST be populated in every case, then you put a NOT NULL constraint on it. Nobody will ever be able to insert NULL into that column.

What if you are trying to read a column that is supposed to allow NULL values? There are several ways of testing for null. This example is for a SqlDataReader (C# syntax, but you can easily convert if needed):

Test before reading:

         string s;
         if (rdr.IsDbNull(0))
           s = "NULL";
         else
           s = rdr.GetString(0);

Or, if you want to read the value as a string, this will automatically convert a NULL to an empty string:

        rdr["col_name"].ToString(); // return empty string for NULL

Or use the SQL function ISNULL to translate a NULL value to something else from within SQL Server:

        select isnull(CategoryID,0) from ...

Eric