How to handle [NULL] values of datetime from SQL Server to a datetimepicker

Question

SqlCommand cmd = new SqlCommand("SELECT dateReceived FROM table1", conn);
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();

if (dr.HasRows)
{
     while (dr.Read())
     {
          object sqlDateTime = dr[0];
          DateTime? dt = (sqlDateTime == System.DBNull.Value)
                         ? (DateTime?)null
                         : Convert.ToDateTime(sqlDateTime);

          dtDateReceived.Value = sqlDateTime;
     }
 }

 dr.Close();
 cmd.Dispose();
 conn.Close();

This is my code for getting the dateReceived in table1 considering that it might have a null value on the column dateReceived.

I would like to display it in a datetimepicker in windows form but I am getting an error :

Cannot implicitly convert type 'object' to System.DateTime

All I wanted is to display the value (if not null) onto the datetimepicker control. If null then nothing will happen.

duplicate: https://stackoverflow.com/questions/284364/how-to-alter-a-net-datetimepicker-control-to-allow-enter-null-values — jazb, Dec 04 '18 at 04:00
Possible duplicate of [How to alter a .NET DateTimePicker control to allow enter null values?](https://stackoverflow.com/questions/284364/how-to-alter-a-net-datetimepicker-control-to-allow-enter-null-values) — Tetsuya Yamamoto, Dec 04 '18 at 04:00
still getting the error : "Additional information: Data is Null. This method or property cannot be called on Null values." — MDB, Dec 04 '18 at 05:38
`dtDateReceived.Value = sqlDateTime;` Did you mean to use `sqlDateTime` there, rather than `dt`? — mjwills, Dec 04 '18 at 12:12
Possible duplicate of [Set DateTimePicker value to be null](https://stackoverflow.com/questions/5947726/set-datetimepicker-value-to-be-null) — mjwills, Dec 04 '18 at 12:13

score 0 · Answer 1 · answered Dec 04 '18 at 04:23

0

you can use

var dateTime = dr[0] as DateTime;

it will either return null or datetime.

answered Dec 04 '18 at 04:23

Nayas Subramanian

2,269
21
28

score 0 · Answer 2 · answered Aug 10 '20 at 12:47

You can use this:

static async void Main(string[] args)
{
   SqlConnection connection = new SqlConnection();

   SqlCommand command = new SqlCommand("", connection);

   SqlDataReader reader = command.ExecuteReader();

   while (await reader.ReadAsync())
   {
      if (reader.IsDBNull("columnName"))
      {
         throw new SqlNullValueException("Value in columnName is null");
      }
      else { }
   }
}

score -1 · Answer 3 · answered Dec 04 '18 at 04:10

instead of

object sqlDateTime = dr[0];
    DateTime? dt = (sqlDateTime == System.DBNull.Value)
    ? (DateTime?)null
    : Convert.ToDateTime(sqlDateTime);

use

var dateTime = dr[0] != System.DBNull.Value
  ? (DateTime?)dr.GetDateTime(0) // parse the sql datetime and returns CLR DateTime
  : (DateTime?)null;

Vijunav Vastivch · Answer 4 · 2018-12-04T05:38:31.863

-1

You can use HasValue like

 while (dr.Read())
     {
          DateTime? sqlDateTime = dr[0];
          DateTime? dt = sqlDateTime.HasValue
                             ? sqlDateTime
                         :null;

          dtDateReceived.Value = dt;
     }

Make sure your dtDateReceived is nullable

edited Dec 04 '18 at 05:38

answered Dec 04 '18 at 05:30

Vijunav Vastivch

4,153
1
16
30

Thanks for the downvotes.. just leave a comment need to learn more. – Vijunav Vastivch Dec 04 '18 at 05:39

How to handle [NULL] values of datetime from SQL Server to a datetimepicker

4 Answers4