Solved

Incomplete datetime column on datagrid view

Posted on 2004-04-22
7
797 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 4

Accepted Solution

by:
Jarodtweiss earned 500 total points
ID: 10888469
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
ID: 10888509
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
ID: 10896761
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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
LVL 4

Expert Comment

by:Jarodtweiss
ID: 10896944
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
ID: 10897720
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
ID: 10898750
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
ID: 10899012
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 Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
how to make search between pages 17 60
VB.net Filesystem watcher not working 5 58
reading an excel file vb.net 2 47
SQL Time out only from one specific computer client 15 53
Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

710 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