IllegalCastException: _VariantString cannot be cast to Date

  1. 6 days ago

    Horst J

    Nov 7 Pre-Release Testers, Xojo Pro Europe, Germany, Stuttgart

    I would like to convert my app to API 2.0, but I got an error message, when I try to get a value from a SQLite DateTime database field.

    Error: IllegalCastException: _VariantString cannot be cast to Date

    // API 2.0

    var sql as string
    sql = "SELECT pur_date, pur_price, pur_currency, pur_ordernumber, " +_
    "categories.catname, arch.pname FROM license " +_
    "LEFT JOIN categories ON catid = categories.serial " +_
    "LEFT JOIN arch ON platform = arch.serial "

    var KDate as DateTime
    Var rowsFound As RowSet

    Try
    rowsFound = MyDB.SelectSQL(sql)
    If rowsFound <> Nil Then

    For Each row As DatabaseRow in rowsFound
    KDate = row.DateTimeColumn("pur_date")
    Next
    rowsFound.Close

    End if

    Catch error as DatabaseException
    MessageBox("Error: " + error.Message)
    End Try

    This is now working:

    var rowsFound As RowSet
    var KDate as DateTime
    var sql as string

    sql = "SELECT appname, pur_date FROM license"

    Try
    rowsFound = MyDB.SelectSQL(sql)

    If rowsFound <> Nil Then

    While Not rowsFound.AfterLastRow

    if rowsFound.Column("pur_date").StringValue.Length > 9 then
    KDate = rowsFound.Column("pur_date").DateTimeValue
    end if
    rowsFound.MoveToNextRow

    Wend
    system.DebugLog "Date: " + KDate.SQLDateTime
    End if

    Catch error as DatabaseException
    MessageDialog.Show( CurrentMethodName + " - Error: " + error.Message )
    End Try

  2. Emile S

    Nov 7 Europe (France, Strasbourg)

    Are-you sure for this line syntax:

    KDate = row.DateTimeColumn("pur_date

    How do you write data in "pur_date" ?

    Maybe: KDate.SQLDateTime = … ?

  3. Horst J

    Nov 7 Pre-Release Testers, Xojo Pro Europe, Germany, Stuttgart

    Yes. When I change it to Date.SQLDateTime, I got this error:

    wList.FilterEntries, line 130
    Type mismatch error. Expected String, but got class DateTime
    KDate.SQLDateTime = row.DateTimeColumn("pur_date")

  4. Horst J

    Nov 7 Pre-Release Testers, Xojo Pro Europe, Germany, Stuttgart

    When I change it to "KDate.SQLDateTime = row.Column("pur_date")", the error is also IllegalCastException.

  5. Sascha S

    Nov 7 Pre-Release Testers, Xojo Pro Germany, Lower Saxonary
    Edited 6 days ago

    Shouldn't it be KDate = row.Column("pur_date").DateTime ?

  6. Sascha S

    Nov 7 Pre-Release Testers, Xojo Pro Germany, Lower Saxonary
    Edited 6 days ago
    Try
      rowsFound = MyDB.SelectSQL(sql)
    
      If rowsFound <> Nil Then
    
        While Not rowsFound.AfterLastRow
    
          KDate = row.Column("pur_date").DateTime
          rowsFound.MoveToNextRow
    
        Wend
      
        rowsFound.Close // You can leave that one out. rowsFound will be trashed after you have left the Method/Event ;)
    
      End if
    
    Catch error as DatabaseException
      MessageDialog.Show( CurrentMethodName + " - Error: " + error.Message )
     // System.Log( System.LogLevelError, CurrentMethodName + " - Error: " + error.Message )
    End Try

    I am not at my Dev.Desk. Code written without Xojo IDE... :)

  7. Horst J

    Nov 7 Pre-Release Testers, Xojo Pro Europe, Germany, Stuttgart
    Edited 6 days ago

    row.Column is a string and it doesn't accept .DateTime.

    wList.FilterEntries, line 131
    Type "String" has no member named "DateTime"
    KDate = row.Column("pur_date").DateTime

    I can only use:
    KDate = row.DateTimeColumn("pur_date") or KDate = row.DateColumn("pur_date"), if Kate is as Date.

    With the old API1 recordSet there is now problem with DateTime value from a SQLite field.

  8. Horst J

    Nov 7 Pre-Release Testers, Xojo Pro Answer Europe, Germany, Stuttgart

    This is now working:

    var rowsFound As RowSet
    var KDate as DateTime
    var sql as string

    sql = "SELECT appname, pur_date FROM license"

    Try
    rowsFound = MyDB.SelectSQL(sql)

    If rowsFound <> Nil Then

    While Not rowsFound.AfterLastRow

    if rowsFound.Column("pur_date").StringValue.Length > 9 then
    KDate = rowsFound.Column("pur_date").DateTimeValue
    end if
    rowsFound.MoveToNextRow

    Wend
    system.DebugLog "Date: " + KDate.SQLDateTime
    End if

    Catch error as DatabaseException
    MessageDialog.Show( CurrentMethodName + " - Error: " + error.Message )
    End Try

  9. Sascha S

    Nov 7 Pre-Release Testers, Xojo Pro Germany, Lower Saxonary
    Edited 6 days ago

    @Horst J problem with DateTime value from a SQLite field.

    No, it works just fine if the Column is really a DateTime Column.

    But, if the Column is just a VarChar/String Column which contains DateTime Strings, why not just do a KDate = DateTime.FromString( rowsFound.Column("pur_date").StringValue ) ?

    As long as you are catching Exceptions and handling them accordingly, you should be fine.

    Try
    
    rowsFound = MyDB.SelectSQL(sql)
    
    If rowsFound <> Nil Then
    
    While Not rowsFound.AfterLastRow
    
    KDate = DateTime.FromString( rowsFound.Column("pur_date").StringValue )
    rowsFound.MoveToNextRow
    
    Wend
    system.DebugLog "Date: " + KDate.SQLDateTime
    
    End if
    
    Catch error as DatabaseException
    // KDate = DateTime.Now // or whatever makes sense here :)
    system.DebugLog "Date (Calculated): " + KDate.SQLDateTime
    MessageDialog.Show( CurrentMethodName + " - Error: " + error.Message )
    End Try
  10. Emile S

    Nov 7 Europe (France, Strasbourg)

    Did you checked in the debugger the object types ?

  11. Sascha S

    Nov 7 Pre-Release Testers, Xojo Pro Germany, Lower Saxonary

    @Emile S Did you checked in the debugger the object types ?

    He knows the Types. I think he expected that the autoconversion from String to DateTime was a valid one, but It's not. But in this case, the FromString Method can be helpfull.

  12. Horst J

    Nov 7 Pre-Release Testers, Xojo Pro Europe, Germany, Stuttgart

    If the first example we have these items:
    rowsFound As RowSet
    row as DatabaseRow

    A DatabaseRow has these values
    BlobColumn
    BooleanColumn
    Column
    CurrencyColumn
    DateColumn
    DateTimeColumn
    DoubleColumn
    Int64Column
    IntegerColumn
    PictureColumn

    All these value a working except DateColumn and DateTimeColumn. My field in the database is a DateTime field.

    In the second example we have these items:
    rowsFound As RowSet
    rowsFound.Column as DatabaseColumn
    rowsFound.Column has the same values like row in the first example, because it is the same type.

    The difference is this:
    1. Example
    KDate = row.DateTimeColumn("pur_date")
    2. Example
    KDate = rowsFound.Column("pur_date").DateTimeValue

    I don't know why I can use .DateTimeValue in rowsFound.Column, but not in row.DateTimeColumn or row.Column. Why row.Column("pur_date"). has only the values?
    CLong
    Encoding
    Split
    ToArray
    ToInt64
    TotText

  13. Sascha S

    Nov 7 Pre-Release Testers, Xojo Pro Germany, Lower Saxonary

    To be honest, i also do not have an answer to that. Maybe worth a FR? ;)

or Sign Up to reply!