• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 805
  • Last Modified:

Incomplete datetime column on datagrid view

Hi there, I'm new to VB.Net and I'm having problems viewing a dataset on a datagrid control with a datetime column.  Basically, I'm want to display a query with a datetime in the Select SQL.  The date is there but the time part is missing.  I saw a solution where you declare a  DataGridTextBoxColumn, format it with the correct datetime format and attach it to the table style of the datagrid.  The thing is it only displays that particular column.  To view all the table, you have to declare all the columns the same was as you did with the datetime column (see this link: http://www.dotnet247.com/247reference/msgs/12/62176.aspx).  This is just a complete waste of time!  How can I view the full datetime format of the column with this type of query? How come they modified this part of the datagrid when it is doing just fine with the VB6.0 version?

ReDgie :p
1 Solution
Well if you do not want to create your own table style, you can simply modify your SQL query to get a string instead of a date with the correct formatting like
    TO_CHAR(myDateCol, 'DD/MM/YYYY HH24:MI:SS')

Otherwise you will have to do what is explained in the link. But you'll havet to trick if you do not want to create your own table style.
Indeed if you do not provide any table style, .NET will automatically create one by default, with DataGridTextBoxColumn for all columns that are not holding booleans.

So you can work on that one and modify the given column style.
You must know that the default table style is created after setting the datasource.
So you can set the datasource and then modify the column (I'm not sure that this setting, set after the datasource will be taken into account...)
then you just do (I assume your date column is the 4th one)
myDataGrid.TableStyles(0).GridColumnStyle(3).Format = "MM'/'dd'/'yyyy' 'hh':'mm':'ss"
The following example uses a Datagrid that has an empty DataGridTableStyle added to it at design time through the IDE. Using the example you highlighted from .Net247, the code fills the datagrid from the Employees table on the SQL Server. Once this has completed, the DataGridTableStyle will be set according to the data types on the Employees table. The code then processes each ColumnStyle in the DataGridTableStyle and looks for a format of 'd' (which only shows the date) and updates it to "dd/MM/yyyy hh:mm:ss":

Dim oCn As New SqlConnection("data source=(local);user
Dim oDa As New SqlDataAdapter("SELECT * FROM Employees", oCn)
Dim oDs As New DataSet()

'Set Mapping Name for Empty TableStyle
Me.DataGridTableStyle1.MappingName = "Employees"

'Fill Data Grid
oDa.Fill(oDs, "Employees")
DataGrid1.DataSource = oDs.Tables("Employees")

'Get Grid Column Styles
al = New ArrayList(Me.DataGridTableStyle1.GridColumnStyles)

'Check each Column Style for a column that has a 'd' format
For nCount = 0 To (al.Count - 1)
     If al(nCount).GetType() Is GetType(DataGridTextBoxColumn) Then
          If al(nCount).format = "d" Then
               al(nCount).Format = "dd/MM/yyyy hh:mm:ss"
               'Amend any other properties here
          End If
    End If

Hope this is of help.

dtValue = System.DateTime.Parse(oDataReader(nColIdx).ToString())
dtValue = System.DateTime.Parse(oDataReader(nColIdx).ToString())

So yours oListItem.SubItems.Add(dtValue.ToString("dd-MMM-yyyy hh:mm:ss"))
Good Luck
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

To complete the post of tgannetts :

do not create an ArrayList if you don't need it. Just use an enumerator or the count of objects.
I also prefer using the typeof keyword instead of GetType. I find it clearer but I don't know if there is any drawback or improvement for performance.
Another advice is to always add the escape characters when you use formatting. .NET won't parse anything put between ' '. This is a good habit I think as it will be usefull if you want to add some chanaracter that are already used for formatting.

'Check each Column Style for a column that has a 'd' format
With Me.DataGridTableStyle1.GridColumnStyles
  For nCount = 0 To (.Count - 1)
       If typeof .Item(nCount) Is DataGridTextBoxColumn Then
            If .Item(nCount).format = "d" Then
                 .Item(nCount).Format = "dd'/'MM'/'yyyy' 'hh':'mm':'ss"
                 'Amend any other properties here
            End If
      End If
End With
RedGieAuthor Commented:
Thanks guys for the help, but I guess I only need to display the data on the datagrid.  So I guess I'll go with Jarodtweiss but his solution is a little way off.  Instead of the TO_CHAR (???-I got an error) function, I used the CONVERT function in my SQL statement.  Anyways that pointed me to the right solution.

I agree that IsType is a much clearer method.

The reason I used an Arraylist was that the Enumerator for the GridColumnStyles collection returns a DataGridColumnStyle object, which does not expose the Format property. So the line
'If .Item(nCount).format = "d" Then' in your code will not work. Using the Arraylist was just a means of late-binding to the objects in the GridColumnStyles collection, and so to access the Format properties.  

Oops... right... missed that...
By the way all code should always been compiled with Option Strict On. So both code won't work.
We may use a DirectCast operation to do the job.

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now