Problem with date NIL

Hi Group, I’m trying to display some date data on a table, some fields may be empty… so when it gets to the null date field, I get error. The problem is that I also have to format the date first … if I enter the data read directly in the ListBox with rows.Column(“DataInvoice”).DateTimeValue it works, but if I first format the data in the date variable DataPrimaScadenzaFormattata and insert this in the ListBox and I read a null field I get an error … how can I solve the problem ?

Dim DataPrimaScadenzaFormattata as new date
DataPrimaScadenzaFormattata=rows.Column(“Rata1”).DateTimeValue

ListBoxIngressi.AddRow(DataPrimaScadenzaFormattataShortDate) 'ERROR WITH VALUE=NIL

ListBoxIngressi.AddRow(rows.Column(“Rata1”).DateTimeValue) 'OK WITH VALUE = NIL

I need to display a formatted date

You can simply use a try catch
And if you got an error display a placeholder value

Excuse my ignorance, what do you mean? I know it’s due to NIL/NULL value in database field… now I would like to handle it but I don’t know how. If I insert the database field directly I have no problems and if there is no data value it does not insert anything, if instead I format by declaring a date variable, I have problems with the data variable which returns the error on NIL values

Try to format the date
since it’s nil you get an exception
in the catch use the placeholder value (empty, “N/D”)

// This should work

Var dateFromDB As DateTime = rows.Column(“Rata1”).DateTimeValue

ListBoxIngressi.AddRow( If(dateFromDB Is Nil, "", dateFromDB.ToString(DateTime.FormatStyles.Short, DateTime.FormatStyles.None)) )

The following format is a bit easier to read:

// We assume that the Column "Rata1" is a Date or DateTime Column!

If rows.Column(“Rata1”).Value <> Nil Then

  Var dateFromDB As DateTime = rows.Column(“Rata1”).DateTimeValue
  ListBoxIngressi.AddRow dateFromDB.ToString(DateTime.FormatStyles.Short, DateTime.FormatStyles.None)

Else

  ListBoxIngressi.AddRow ""

End If

// The above Code is untested, written without the Xojo IDE.

First we make sure the Field contains a Value and is not NIL/NULL. Then we add the Date to the ListBox or we add an empty Field to the Listbox.

@Rick_A: Your code is perfectly correct, but too compressed for a lesson. My opinion… :wink:

1 Like

Yep, my style usually don’t focus only on a lesson, sometimes around 3 at once, as I’m very oriented towards good memory use, good speed, and terse readable code. :laughing:

1 Like

Thanks Rick A
I tried the code and it’s perfect, I didn’t know you could nest so many instructions… great.

Sasha S
I also understood your code and it works, the problem is that I have other lines of code where this possibility repeats and I should insert too many conditions.

Thanks to both of you anyway, I learned something new today :slight_smile: See you next time.

2 Likes

I believe this is an opportunity to use an extension method to add functionality to the Database Column Class. Something like:

Public Function FormattedDate(Extends value As DatabaseColumn) As String
  If value.Value Is Nil Then
    Return ""
  End If
  
  Return value.DateTimeValue.ToString(DateTime.FormatStyles.Long, DateTime.FormatStyles.None)
  
End Function

Drop this into a module and you can then use:

FormatedDateListBoxIngressi.AddRow(rows.Column(“Rata1”).FormattedDate)

Everywhere you need a formatted date from a database column with Null columns being handled.

4 Likes