dateTime in bound grids

Posted by: petevick on 8 September 2017, 12:59 pm EST

  • Posted 8 September 2017, 12:59 pm EST

    Hi,


    I have a bound grid with a lot of datetime types,  which all display as dd/mm/yyyy and miss off the time portion - how do I get the time to display?


    Thanks

  • Replied 8 September 2017, 12:59 pm EST

    petevick -

    Are the values in the database true datetime values?  If so, what type of format are they using?  When you load your page you could query the celltype for the cells to make sure they are being defined as datetime cells.  If they aren't you can use the DataAutoCellTypes property and set it to false and then set the cells up as DateTimeCellTypes and set the DateTimeFormat to include the time.
  • Replied 8 September 2017, 12:59 pm EST

    Hi,


    just checked on the database, and in SqlManagement Studio it displays 2008-01-24 14:13:23.233 and using spread it displays 24/01/2008


    The info for the cell is:-


    ?grdData.ActiveSheet.Columns(14)
    ...
    CellType: {FarPoint.Win.Spread.CellType.DateTimeCellType}
    DataField: "DOWNLOADED"
    Editor: {FarPoint.Win.Spread.CellType.DateTimeCellType}
    Formatter: {FarPoint.Win.Spread.CellType.DateTimeCellType}
    Label: "DOWNLOADED"
    ...


    Thanks


    Pete

  • Replied 8 September 2017, 12:59 pm EST

    Pete  -

    It may be that the spread isn't recognizing the formatting of the time portion in the database.  Try formatting the cells as DateTime cells after you are bound and set the formatting up yourself...

    Dim conStr As String = "Provider=Microsoft.JET.OLEDB.4.0;data source= c:\forums6\date.mdb"
    Dim sqlStr As String = "SELECT * FROM Table1"

    Dim conn As New System.Data.OleDb.OleDbConnection(conStr)
    Dim ds As New System.Data.DataSet()

    Dim daCust As New System.Data.OleDb.OleDbDataAdapter(sqlStr, conn)
    daCust.Fill(ds)
    FpSpread1.DataSource = ds

    Dim dt As New FarPoint.Web.Spread.DateTimeCellType

    dt.FormatString = "MM/dd/yyyy hh:mm:ss tt"

    FpSpread1.ActiveSheetView.Columns(0).CellType = dt
    End Sub
  • Replied 8 September 2017, 12:59 pm EST

    Hi,


    if I do


    Dim dt As New FarPoint.Win.Spread.CellType.DateTimeCellType
    dt.formatstring .....


    I get the error formatstring is not a member etc.


    Also, how do I check my columns celltype -


    If grdData.ActiveSheet.Columns(i).CellType Is FarPoint.Win.Spread.CellType.DateTimeCellType


    doesn't do it


     


    Thanks

  • Replied 8 September 2017, 12:59 pm EST

    Pete -

    You can check the cell type in the following manner...

    Dim ct As FarPoint.Win.Spread.CellType.ICellType

    Dim dt As FarPoint.Win.Spread.CellType.DateTimeCellType

    ct = FpSpread1.ActiveSheet.GetCellType(r, c)
    'r and c are variables indicating the row and column to use
    in a For/Next loop

    If TypeOf ct Is FarPoint.Win.Spread.CellType.DateTimeCellType Then

    dt = CType(dt, FarPoint.Win.Spread.CellType.DateTimeCellType)

    End If

    Does your declaration of a DateTimeCellType work?  If so then you shouldn't be getting an error on the FormatString property.


  • Replied 8 September 2017, 12:59 pm EST

    HI,


    Dim dt As FarPoint.Win.Spread.CellType.DateTimeCellType


    the only options i then get for dt. is format, not formatstring - should I be using dt.datetimeformat


    Thanks


     


    Pete


     

  • Replied 8 September 2017, 12:59 pm EST

    Pete -

    Minor error.  You have to include the 'New' keyword on your declaration...

    Dim dt As New FarPoint.Win.Spread.CellType.DateTimeCellType


  • Replied 8 September 2017, 12:59 pm EST

    Hi Bob,


    thanks - will give it a try as soon as I get back


     


    Pete

  • Replied 8 September 2017, 12:59 pm EST

    Pete -

    Some clarification.  By default we set the formatter of the datetime cell to only show the date.  If you were to query the Value of the cell it would return both the date and the time.  You can use the code I sent or you can also set the FormatString = "G" or "g".
  • Replied 8 September 2017, 12:59 pm EST

    Pete -

    We seem to be talking about two different controls here.  Are you using the Spread for Windows Forms or the Spread for WebForms?  If it's the WindowsForms then use the DateTimeFormat property...

    Dim dt As New FarPoint.Win.Spread.CellType.DateTimeCellType
    dt.DateTimeFormat = FarPoint.Win.Spread.CellType.DateTimeFormat.UserDefined
    dt.UserDefinedFormat = "your string here"

    If you are using the WebForms then it would be...

    Dim dt As New FarPoint.Web.Spread.DateTimeCellType
    dt.FormatString = "your string here"


  • Replied 8 September 2017, 12:59 pm EST

    Hopefully the final question on this...


    When I do


    If grdData.ActiveSheet.Columns(i).CellType Is FarPoint.Win.Spread.CellType.DateTimeCellType Then...


    I get the error "Error 6 'DateTimeCellType' is a type in 'CellType' and cannot be used as an expression." - I have looked in the forums, and the syntax appears to be correct, but I am obviously missing something


     


    thanks

  • Replied 8 September 2017, 12:59 pm EST

    Pete -

    Try this...

    If TypeOf FpSpread1.ActiveSheet.Columns(0).CellType Is FarPoint.Win.Spread.CellType.DateTimeCellType Then
    MsgBox("It's a date cell")
    End If
  • Replied 8 September 2017, 12:59 pm EST

    Hi,


    sorry - just realised I have posted in the wrong forum Embarrassed [:$] - I am using spread for windows forms


    Pete

  • Replied 8 September 2017, 12:59 pm EST

    Pete -

    Then this code should work for you...

    Dim dt As New FarPoint.Win.Spread.CellType.DateTimeCellType
    dt.DateTimeFormat = FarPoint.Win.Spread.CellType.DateTimeFormat.UserDefined
    dt.UserDefinedFormat = "your string here"
  • Replied 8 September 2017, 12:59 pm EST

    Hi,


    this still gives me the same error


    Pete

  • Replied 8 September 2017, 12:59 pm EST

    Sorted - thanks for all your help
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels