|
Subject:
|
Catching NULL values
|
|
Posted By:
|
hasanali00
|
Post Date:
|
6/30/2005 5:12:37 AM
|
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
|
|
Reply By:
|
englere
|
Reply Date:
|
7/1/2005 10:29:44 PM
|
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
|
