Solved

Incomplete datetime column on datagrid view

Posted on 2004-04-22
7
791 Views
Last Modified: 2010-04-24
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?

Thanks
ReDgie :p
0
Comment
Question by:RedGie
7 Comments
 
LVL 4

Accepted Solution

by:
Jarodtweiss earned 500 total points
Comment Utility
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"
0
 
LVL 5

Expert Comment

by:tgannetts
Comment Utility
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
id=me;database=northwind;")
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
Next

Hope this is of help.

Tom.
0
 
LVL 8

Expert Comment

by:rajaloysious
Comment Utility
dtValue = System.DateTime.Parse(oDataReader(nColIdx).ToString())
oListItem.SubItems.Add(dtValue.ToString("dd-MMM-yyyy"))
dtValue = System.DateTime.Parse(oDataReader(nColIdx).ToString())
oListItem.SubItems.Add(dtValue.ToString("hh:mm:ss"))


So yours oListItem.SubItems.Add(dtValue.ToString("dd-MMM-yyyy hh:mm:ss"))
Good Luck
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 4

Expert Comment

by:Jarodtweiss
Comment Utility
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
  Next
End With
0
 

Author Comment

by:RedGie
Comment Utility
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.
Thanks
0
 
LVL 5

Expert Comment

by:tgannetts
Comment Utility
Jarodtweiss:

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.  

Tom
0
 
LVL 4

Expert Comment

by:Jarodtweiss
Comment Utility
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.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now