1

I am wanting to know how i can check if a field contains null value and replace it with text N/A or just not display the field. But i don't want the code to break if the field contains null i want it to continue until all fields are filled with a value.

C# Code

using (var connection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
    using (var command = connection.CreateCommand())
    {
        command.CommandText = "SELECT FirstName, LastName, Date FROM EOI WHERE (FormID = '13')";
        connection.Open();
        using (var reader = command.ExecuteReader())
        {
            while(reader.Read())
            {
                Label1.Text = reader["FirstName"].ToString();
                Label2.Text = reader["LastName"].ToString();
                DateTime Text = Convert.ToDateTime(reader["Date"]);
                Label3.Text = Text.ToString("d");
            }
        }
    }
Ondrej Janacek
  • 12,486
  • 14
  • 59
  • 93
Mr.Turtle125
  • 15
  • 2
  • 5
  • [http://stackoverflow.com/questions/4604414/best-way-to-check-if-a-data-table-has-a-null-value-in-it][1] check above link I think it will help you. [1]: http://stackoverflow.com/questions/4604414/best-way-to-check-if-a-data-table-has-a-null-value-in-it – Manoj Naik Oct 19 '13 at 06:38
  • possible duplicate of [Most efficient way to check for DBNull and then assign to a variable?](http://stackoverflow.com/questions/221582/most-efficient-way-to-check-for-dbnull-and-then-assign-to-a-variable) – nawfal Dec 11 '13 at 16:41

4 Answers4

1

You can either check this in C#:

if(reader["FirstName"]==null)
{
 // Do something
}

or in T-SQL with ISNULL:

SELECT ISNULL(FirstName,'N/A'), ISNULL(LastName,'N/A'), Date FROM EOI
Semih Yagcioglu
  • 4,011
  • 1
  • 26
  • 43
1

I'm assuming that the null value you will have a problem with is the Date column. A DateTime in C# cannot be null since it is a struct. You would want to cast it to a nullable DateTime instead:

DateTime? date = (DateTime?)reader["Date"];

Now it's up to you to perform logic when transforming this to a string:

dateLabel.Text = date != null ? date.Value.ToString("d") : "N/A";

For the string columns just rewrite it as follows since strings are already nullable:

firstNameLabel.Text = (string)reader["FirstName"] ?? "N/A";
Trevor Elliott
  • 11,292
  • 11
  • 63
  • 102
0

You can either check in your select statement or in code. In SQL:

SELECT IsNull(FirstName, 'N/A') as FirstName, 
        Coalesce(LastName, 'N/A') as LastName, Date FROM EOI WHERE (FormID = '13');

In .Net, you need to compare it to DbNull.Value:

Label1.Text = reader["FirstName"] == DBNull.Value ? "N/A" :  Convert.ToString(reader["FirstName"]);

Note that in the above example, Convert.ToString() will convert nulls to empty strings. This is a third example of what you can do.

ps2goat
  • 8,067
  • 1
  • 35
  • 68
  • Used the example with DBNull.Value and worked thank you heaps for the quick response and help. – Mr.Turtle125 Oct 19 '13 at 06:58
  • I should also state that IsNull() and Coalesce() are two similar TSQL functions that do relatively the same thing. Some people have big debates about it like AMD vs Intel, but I say those who are interested in that scenario can look those functions up by themselves. – ps2goat Oct 19 '13 at 07:03
0

If you know the types in advance you can use (pseudo code as I'm typing on an iPhone):

KnownType myData = reader.IsDbNull(fieldname) ? MyDefaultValue : reader.GetKnownType(fieldname)

E.g. String myData = reader.IsDbNull(fieldname) ? "" : reader.GetString(fieldname)

String dateValue = reader.IsDbNull(fieldname) ? "No date" : reader.GetDate(fieldname).ToString()

This is more efficient and minimises casting. For maximum efficiency you should also use the field index rather than the field name. Every time you use the field name, the index has to be calculated: reader("Date") is reader.GetValue(reader.GetOrdinal("Date"))

Macrotech
  • 1
  • 1