Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Incomplete datetime column on datagrid view

Posted on 2004-04-22
7
Medium Priority
?
803 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 1000 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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: 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.

Question has a verified solution.

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

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

972 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