How to change the format from DataTable and Show in DataGridView in vb.net

Question

How to change the format of DataTable and Show in DataGridView in vb.net?. I want to change the format of the DataTable without going through the DataGridView format and displaying in the DataGriView. Please recommend the best solution. I tried but the result is still in the datagridview has not changed as I attached in the screenshot below.

Thanks

        Dim dt As New DataTable("tbl")
        Dim dtColumn As DataColumn
        dtColumn = New DataColumn()
        dtColumn.DataType = GetType(DateTime)
        dtColumn.ColumnName = "CreatedDate"
        dt.Columns.Add(dtColumn)
        Dim dr As DataRow = dt.NewRow()
        dr("CreatedDate") = DateTime.Now
        Dim dr1 As DataRow = dt.NewRow()
        dr1("CreatedDate") = DateTime.Now.AddDays(1)
        dt.Rows.Add(dr)
        dt.Rows.Add(dr1)
Dim query = dt.AsEnumerable().Select(Function(r) r.Field(Of DateTime)("CreatedDate").ToString("yyyy/MM/dd"))
DataGridView1.DataSource = dt
'if I run the console then the output results change this should be possible without formatting the datagridview
        'For Each r In query
        '    Console.WriteLine(r.ToString())
        '    Console.WriteLine("Press any key to continue")
        '    Console.ReadKey()

        'Next r

IFrank · Accepted Answer · 2023-01-13T16:31:39.513

1

You can remove .ToString("yyyy/MM/dd").

In your form designer, select your DataGridView and browse to events. Add an eventhandler for ColumnAdded.

This will generate the following code:

Private Sub DataGridView1_ColumnAdded(sender As Object, e As DataGridViewColumnEventArgs) Handles dataGridView1.ColumnAdded

End Sub

Now you can define what the event handler has to do whenever a columns is being added to your datagridview, so:

Private Sub DataGridView1_ColumnAdded(sender As Object, e As DataGridViewColumnEventArgs) Handles dataGridView1.ColumnAdded

    ' Cast the argument to DataGridViewTextBoxColumn
    Dim c As DataGridViewTextBoxColumn = TryCast(e.Column, DataGridViewTextBoxColumn)

    ' Test if conversion has been successful
    If c IsNot Nothing Then

        Select Case c.ValueType

            Case GetType(Date) ' If ValueType of column being added is Date

                Dim cellStype As New DataGridViewCellStyle With {.Format = "dd/MM/yyyy"} ' Set here the format for this DataGridViewTextBoxColumn
                c.DefaultCellStyle = cellStype ' Apply the style

                ' Add here other ValueType you want to format differently, if any

        End Select

    End If

End Sub

Note that you could define the format for others ValueType simply adding the proper case in the same event handler.

Result:

I highly recommend you to enable Option Strict On for your VB.Net projects. You can read more about it here.

--- Update ---

As per your comment, what you really need is simply changing from DateTime (Date) type to DateOnly.

Here's your code updated accordingly, please note I simplified object initializations and changed the way you refer to the specified DataColumn - if you can, it's preferrable using the DataColumn object itself instead of its name (a string).

Private Sub ImprovedCode()

    Dim dt As New DataTable("tbl")

    ' Change DateTime to DateOnly
    Dim dtColumn As New DataColumn With {.DataType = GetType(DateOnly), .ColumnName = "CreatedDate"}
    dt.Columns.Add(dtColumn)

    Dim dr As DataRow = dt.NewRow()
    dr(dtColumn) = DateOnly.FromDateTime(Date.Now) ' This is instead of dr("CreatedDate")

    Dim dr1 As DataRow = dt.NewRow()
    dr1(dtColumn) = DateOnly.FromDateTime(Date.Now) ' This is instead of dr("CreatedDate")
    dt.Rows.Add(dr)
    dt.Rows.Add(dr1)
    dataGridView1.DataSource = dt

End Sub

Result from Visual Studio DataTable visualizer:

Please note: DateOnly and TimeOnly are available only in .Net 6 and higher - if your project targets .Net Framework, you can not use these types. By the way, if your project is targeting .Net Framework, I suggest you to read this answer on SO about which .Net version choose.

Update As the question owner commented below, there's a library which enables DateOnly in unsupported frameworks: Portable.System.DateTimeOnly

edited Jan 13 '23 at 16:31

answered Jan 11 '23 at 10:25

IFrank

419
1
5
12

Thank you for your reply. if I run the console then the output results change this should be possible without formatting the datagridview. – roy Jan 12 '23 at 02:55
Do you mean that you would like to format the DataTable even if it is not displayed in a DataGridView? So directly formatting the DataTable itself? Please confirm I understood correctly so that I can update my answer. – IFrank Jan 12 '23 at 06:56
`So directly formatting the DataTable itself?` yes right I want to directly format datatable and display in datagridview. like this code `Dim query = dt.AsEnumerable().Select(Function(r) r.Field(Of DateTime)("CreatedDate").ToString("yyyy/MM/dd"))` like the code above I display in the console – roy Jan 12 '23 at 07:05
@And I updated my answer accordingly to your needs. Please let me know if this solves your problem. – IFrank Jan 12 '23 at 09:45
thanks for the answer but my project targets .Net Framework – roy Jan 12 '23 at 14:14
Sorry but I'm not understanding your question now. You said you need to have this datatable formatted in order to show it in a DataGridView, so I proposed the DataGridView_ColumnAdded method. If you need to load the DataTable in the DataGridView, why would you format it directly? Consider that since you are using "classic" Net Framework, you are declaring variables as Date, but this is the short name for DateTime - as the name says, this datatype will always contains a date and a time, no matter if you declare the time or not. Did you tried the solution I proposed with DataGridView formatting? – IFrank Jan 13 '23 at 10:08
1

first with the last answer you have provided a solution with the net framework but must use the Portable.System.DateTimeOnly library so that it can use "dateonly". secondly why I didn't format the datagridview because I used the report library namely Kimtoo.Reports 1.0.0 on the add datagridview format then the results were not appropriate and so I did the add datatable solution. l – roy Jan 13 '23 at 12:54
if I do the datagridview format solution then the output report results still use datetime whereas if I use dateonly then the output report results are appropriate. [link](https://stackoverflow.com/questions/75028737/how-to-format-or-convert-numbers-in-datagridview-and-report-so-that-the-output-r). This is what that means maybe you can help me – roy Jan 13 '23 at 12:56
@And +1 for quoting that library, I didn't know it and maybe it can be useful to someone - I'm updating my answer. So DateOnly solved completely your problem or there's something else I can help with? – IFrank Jan 13 '23 at 16:29
[link](https://stackoverflow.com/questions/75110087/how-to-change-the-format-n2-from-datatable-and-show-in-datagridview-in-vb-net?noredirect=1#comment132547958_75110087) ,I have a new post almost the same as this post just different formats maybe you can help me – roy Jan 14 '23 at 02:51

How to change the format from DataTable and Show in DataGridView in vb.net

1 Answers1