none
DBNull and DateTime again.. RRS feed

  • Question

  • Yes, DateTime and DBNull again, please dont be angry :-)

    I have read several posts regarding the handling od DBNull and DataTime and I understand I cannot set DBNull vaue to a DatTime column. However, I have to do so, and there must be a way to do so ! There must be a way, because DataTable (?) does this by default, when the value of a DateTime column in the result set of an sql qurey is null.

    To be more precise, think the following :

    - you have an MS SQL database with table - Table1 - and a column of it - call it TimeStamp - declared as DateTime
    - this TimeStamp column contains sql null values for many rows

    Now, I make a query agasinst Table1 which returns rows in which TimeStamp is null. When I enumerate through the Rows property of a DataTable, I must make a comparison like this :

    if (DBNull.Value.Equals(rowOfTable1["TimeStamp"]))

    in order to avoid excaptions.

    Eventually, the value of the TimeStamp column is DBNull.Value, isn't it ? This DBNull value was set some by .Net data handling component, not me.

    I created a new partial class which is derived from a TableAdapter used above, and I want to set the value of the TimeStamp column to DBNull in certain conditions. I just can't find the right way to do this. However, this would be essential, since the rest of my code relies on the comparison of the TimeStamp field value against DBNull.Value.

    I am not sure if I was clear - just let me know it not - but is there a way to accoplish this or not ? I wonder if not, because it is indeed done somewhere in the code ...

    Regards,

    Lac

     

     

    • Moved by Aspen VJ Tuesday, March 1, 2011 2:52 AM (From:Windows Forms Data Controls and Databinding)
    Sunday, February 27, 2011 6:20 PM

Answers

  •  

    Thanks for your answer. Honestly, I do not really uderstand the way it would assign a null value to a DateTime, but in the meantime I found the ultimate solution to my problem. Generally, it is :

     

    void dataTable_RowChanged(object sender, DataRowChangeEventArgs e) 
    
    {
     if (...)
     {
       e.Row["aDateTimeFieldName"] = DBNull.Value;
      }
    }
    
    
    • Marked as answer by Lac Thursday, March 3, 2011 12:39 PM
    Thursday, March 3, 2011 12:36 PM

All replies

  • You know what can you do, instead of declaring a DateTime value as null (which you have already found out that`s not possible), you can set the time to some (very) old time, which is impossible to be in your project known as datetime to use.

    What I am talking about is to set the "null" time to some value like 1.1.1900. When ever you want to set it to null simply assign this one to 1.1.1900 to database.

    And when you will retreive these database dateTime values back out, and you will find some date with va value 1.1.1900, you will know (actually the code will), that this time is not set, and show it as blank.

    Is this understood?

    Mitja

    Sunday, February 27, 2011 6:26 PM
  • Thanks Mitja, all right, I can do that but I can only consider this a "workaround" rather than a solution. Because, concerning the above, a simple question comes in my mind : what is the myterious value set originally by some data component of .net, what I can compare to DBNull.Value and that comparison retursn true ? How can I reproduce this ? No way ?? I simly can't believe that...

    Lac

    Sunday, February 27, 2011 6:35 PM
  • Believe me, I was trying hard to use a null somehow, but there is simply not possible to use it on a type of DateTime. You will have to understand that. I do now.

    If you think a bit, Time actually can never be nothing, null or that does NOT exist. Am I right? Time is always running, its not stoppable. Thats why you cannot set it to be null.

    Other solution, which is even more "workAroundable", is to change the column type to something else, like a varchar (string), or better to some integer (but which again cannot be null). Changing to type varchar you will then be able to have null value, but you will loose other advantages, which has dateTime.

    My final word: do as I told you. Use so called "workAround" option. When you will get used to it, you will see its not that bad. Just instead of assigning to null or coparing it to null ( xx = null, or x == null, you will do: xx = new DateTme(1.1.1900) or xx == new DateTime(1.1.1900).

    Thats all. Simple

    Mitja

    Sunday, February 27, 2011 7:01 PM
  • Well, it seems I'll have to get used to it :-)

    But I do not agree with you regarding the explanation of the null value of DateTime. Imagine the following : there is an incident and this incident has at least two datetime properties, its recording and finishing time. Now, tell me, what is the value of an incident's finishing time before it is finished ? In sql it is simply null. That is, its value is not yet defined. Should this not be reflected somehow in a .Net DateTime variable ? I think it really should. And a non-existing value is really not equal to something very samall :-)

    Anyway, thanks for your answers.

    Regards,

    Lac

    P.S.: After all, what is then the value originally set to a DateTime datacolumn which is equal to DBNull.Value ???

     

     

    Sunday, February 27, 2011 7:48 PM
  • Thats why C# has fileds like MaxValue (and MinValue). Which you can still use. So the same as for the min, you can set for some time which is still about to come, set the MaxValue (this in C# is set to 31.12.9999 23:59:59 (btw min is  1.1.1001 0:00:00).

    Mitja

    • Proposed as answer by Mitja Bonca Sunday, February 27, 2011 9:28 PM
    • Unproposed as answer by Mitja Bonca Sunday, February 27, 2011 9:28 PM
    Sunday, February 27, 2011 8:09 PM
  • DBNull.Value is used to set the value of a SQL column, and CAN be used in DateTime columns.  The problem is that you have a misconception here.  Usually, what you get from database readers are objects, or better said, values of type object, or better said, the values returned by the database are presented through the object datatype.  This means that a value for a DateTime columns is sometimes a .Net DateTime and sometimes it is a .Net DBNull .  Your mistake is assuming that DBNull.Value is of DateTime datatype and therefore conversible and storable in a DateTime variable.  No.  You can, however, use an object variable to store either a DateTime value or a DBNull value.
    MCP
    Sunday, February 27, 2011 8:20 PM
  • That is a great answer and is really close to my imagination. But said that, please tell me, how can I set from my code a given row's given column object to represent a DBNull ? That is, how can I (re)initialize that DataColumn object to DBNull ? Cosider the following code snipet, which is a member method of a class derived from a TableAdapter : in a certain condition, I must return a DBNull instead of the datetime stored in the database :

     

     

    void dataTable_RowChanged(object sender, DataRowChangeEventArgs e)

    {

     

     

    if ((mReferenceDate.Date < DateTime.Now.Date) & (e.Action == DataRowAction.Add))

    {

     

     

    if (((PPDataSet.GetPPEventsRow)e.Row).TroubleshootingEndingTime.Date > mReferenceDate.Date) ((PPDataSet.GetPPEventsRow)e.Row).TroubleshootingEndingTime = DBNull.Value

    }

    }

    This code is wrong and does not compile. But I want to have the "TroubleshootingEndingTime" column to represent a DBNull in this case. How could I achieve it ?

     

    Thanks,

    Lac

    Sunday, February 27, 2011 10:19 PM
  • Hi Lac,

    ·         Base on your description,  I think it is little about winform.

    This forum is dedicated to Windows Forms technology. I'm moving this thread from Base "Windows Forms" to "ADO.Net DateSet" forum, since this issue is related to data. There are more data experts in that forum.

    Thank you for understanding and supporting.

     

    Best Regards,

    Vin Jin


    Vin Jin [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, March 1, 2011 2:52 AM
  • Hello Lac,

     

    Thank you for posting here.

    According to your description, I think you could use the  DateTime.TryParse method to get the datetime value of a string into a Nullable. If you have any time could you please try this as below?

    DateTime? d=null; 
    DateTime d2; 
    bool success
    = DateTime.TryParse("some date text", out d2); 
    if (success) d=d2; 

     

    If this doesn't what you want, please see also:

    http://social.msdn.microsoft.com/forums/en-us/winformsdatacontrols/thread/1E5E882B-8A0D-4F63-A360-45F770F697E3

    http://social.msdn.microsoft.com/Forums/en-US/csharpgeneral/thread/c6d7bfdf-26d8-4414-9ec0-d71abaebc428/

    I hope this can help you.

     

    Have a nice day,

     

     


    Jackie Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

     

    • Proposed as answer by Jackie-SunModerator Wednesday, March 2, 2011 9:20 AM
    • Unproposed as answer by Lac Thursday, March 3, 2011 12:22 PM
    Wednesday, March 2, 2011 9:11 AM
    Moderator
  •  

    Thanks for your answer. Honestly, I do not really uderstand the way it would assign a null value to a DateTime, but in the meantime I found the ultimate solution to my problem. Generally, it is :

     

    void dataTable_RowChanged(object sender, DataRowChangeEventArgs e) 
    
    {
     if (...)
     {
       e.Row["aDateTimeFieldName"] = DBNull.Value;
      }
    }
    
    
    • Marked as answer by Lac Thursday, March 3, 2011 12:39 PM
    Thursday, March 3, 2011 12:36 PM
  • Happy to see that, Lac! Thank you for sharing your solution to us.

     

    Nice weekend!


    Jackie Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, March 4, 2011 1:37 AM
    Moderator
  • Yes, Lac ... the way you were trying to set the DateTime column earlier, you were attempting to use the typed property:

    ((PPDataSet.GetPPEventsRow)e.Row).TroubleshootingEndingTime = DBNull.Value

    ... which obviously cannot work, because the DateTime datatype cannot be set to DBNull.Value. But, an object can, which is why it works when you use the untyped object instead:

    e.Row["TroubleshootingEndingTime"] = DBNull.Value

    You discovered the correct way to set this, but I thought I would explain *why* that will work when the other syntax does not (for lurkers).


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Sunday, March 6, 2011 2:12 AM