Solved

Incomplete datetime column on datagrid view

Posted on 2004-04-22
7
795 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
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

 
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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Disable extension 8 49
VB.Net. Reading xml value 6 30
Import MySQL data into MS Access using VB.Net interface 5 31
Crystal reports login after closing form 3 19
This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

820 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