none
How to control if a DBNULL for Date column in DataTable RRS feed

  • Question

  • hi all,

    I have a stupid problem but i cannot find the solution

    I have a typed DataSet with a Data Table

    The DataTable Row has a field of DATE type. (row.ClientiDataCreazione)

    The Table is filled from DB and some rows have NULL Value for Column ClienteDataCreazione

    I use this code to control DBNull and convert the row field in a textbox.text

    If row.ClienteDataCreazione IsNot DBNull.Value Then
                TxtClienteDataCreazione.Text = row.ClienteDataCreazione.ToString
            Else
                TxtClienteDataCreazione.Text = ""
            End If

    But at Editor time I get this message

    'IsNot' requires operands that have reference type but this operand has the value of Datee

    So, how to control if 

    row.ClienteDataCreazione

    is Null ?

    Tuesday, June 12, 2018 9:32 PM

Answers

  • Check if the next expression is available: ‘If Not row.IsClienteDataCreazioneNull Then …’.


    • Marked as answer by Claudio111 Wednesday, June 13, 2018 11:08 AM
    Wednesday, June 13, 2018 7:43 AM
  • Cherry your suggestion is ok

    I want to add the following for people that will have the same problem using TYPED DATASET

    To convert a TEXBOX used for DATE and  textbox.text = StringEmpty you have to use the method to save the null value into row 

    row.SetClienteDataCreazioneNull()

    When save data to Database, if you want to check Null value for typed row you must use

    If row.IsClienteDataCreazioneNull() Then CMD.Parameters.AddWithValue("@DataCreazione", DBNull.Value)

    Else CMD.Parameters.AddWithValue("@DataCreazione", row.ClienteDataCreazione)

    End If

    in other way you get a lot of problems
    • Marked as answer by Claudio111 Wednesday, June 13, 2018 11:08 AM
    Wednesday, June 13, 2018 11:07 AM

All replies

  • Here is how to check for a null value on a date.

    Dim dt As New DataTable
    dt.Columns.Add(New DataColumn With {.ColumnName = "D1", .DataType = GetType(Date)})
    
    dt.Rows.Add(New Object() {})
    dt.Rows.Add(New Object() {Now})
    dt.Rows.Add(New Object() {})
    dt.Rows.Add(New Object() {Now.AddDays(1)})
    
    If dt.Rows(0).Item("D1") Is DBNull.Value Then
        Console.WriteLine("Yes")
    End If
    
    
    For Each row As DataRow In dt.Rows
        Console.WriteLine(row.Item("D1") Is DBNull.Value)
    Next


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Tuesday, June 12, 2018 9:49 PM
    Moderator
  • It is like my code , the difference is that my DataTable is a Table of TYPED DATASET created by Designer

    The code is still incorrect 


    Wednesday, June 13, 2018 5:32 AM
  • Since the Dataset is a TYPED Dataset if I use the code

     If row.Item("ClienteDataCreazione") IsNot DBNull.Value Then .....

    the code works

    Instead If I use the row property 

    If row.ClienteDataCreazione IsNot DBNull.Value Then .... 

    I get Editor message 

    'IsNot' requires operands that have reference type but this operand has the value of Date


    Wednesday, June 13, 2018 5:41 AM
  • Hi Claudio111,

    As far as know, if you want to loop DataTable rows, and check one column's value is null or not, you can use this:

    Dim dt As New DataTable
            dt.Columns.Add("Id", GetType(Int32))
            dt.Columns.Add("time", GetType(Date))
    
            dt.Rows.Add(1, "2018-01-01")
            dt.Rows.Add(2, "2018-01-02")
            dt.Rows.Add(3, "2018-01-03")
            dt.Rows.Add(4, "2018-01-04")
    
            For Each row As DataRow In dt.Rows
                If row("time") IsNot DBNull.Value Then
                    MessageBox.Show(row("time"))
                End If
                If row.Item("time") IsNot DBNull.Value Then
                    MessageBox.Show(row.Item("time"))
                End If
            Next

    But you said that you use row.property, I don't know how to use row.property at my side, can you provide more detailed code here, let me see how to do this.

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, June 13, 2018 6:55 AM
    Moderator
  • Check if the next expression is available: ‘If Not row.IsClienteDataCreazioneNull Then …’.


    • Marked as answer by Claudio111 Wednesday, June 13, 2018 11:08 AM
    Wednesday, June 13, 2018 7:43 AM
  • Hi Cherry

    No , i don't want to loop thru rows.

    I want to read a sql table with a DATE TYPE column using a Dataset Created by designer to have a TYPED DATASET. So I can use row property (i.e. row.ClientiDataCreazione , not row.item("ClientiDataCreazione")

    I want to be able to store DBNULL value in Database for the DATE TYPE Column and read the Database to transfer the DBNULL value to textbox.text

    Wednesday, June 13, 2018 10:26 AM
  • Cherry your suggestion is ok

    I want to add the following for people that will have the same problem using TYPED DATASET

    To convert a TEXBOX used for DATE and  textbox.text = StringEmpty you have to use the method to save the null value into row 

    row.SetClienteDataCreazioneNull()

    When save data to Database, if you want to check Null value for typed row you must use

    If row.IsClienteDataCreazioneNull() Then CMD.Parameters.AddWithValue("@DataCreazione", DBNull.Value)

    Else CMD.Parameters.AddWithValue("@DataCreazione", row.ClienteDataCreazione)

    End If

    in other way you get a lot of problems
    • Marked as answer by Claudio111 Wednesday, June 13, 2018 11:08 AM
    Wednesday, June 13, 2018 11:07 AM