Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

VB.NET Sort datagridview column by UK date (dd/mm/yyyy)

Posted on 2015-02-07
17
Medium Priority
?
778 Views
Last Modified: 2015-02-18
Hi guys, I have a datagrid which is bound to an access database with 4 columns. The third column is a date & time.

I'd like to be able to sort by column 3 to show the oldest and newest entries, however I think the values are just being read as plain text. I have tried formatting the columns defaultcellstyle as "Date Time" but it didn't make any difference.

How can I get this to sort correctly?

Currently when I sort by that column I get the below result:
7/02/2015 10:03:23 PM
6/02/2015 6:14:01 PM
4/08/2015 3:09:44 PM
19/10/2014 9:07:33 PM
19/10/2014 9:02:12 PM
11/08/2015 3:12:42 PM
1/02/2015 6:38:31 PM

As you can see, it's sort of all over the place.

The sort should look like this:
11/08/2015 15:12
4/08/2015 15:09
7/02/2015 22:03
6/02/2015 18:14
1/02/2015 18:38
19/10/2014 21:07
19/10/2014 21:02
0
Comment
Question by:amaru96
[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
  • 9
  • 7
17 Comments
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 40596598
If you sorted by date in the underlying SQL query then it would be in the desired order.  (Dates are NOT stored as formatted strings in access)
0
 
LVL 1

Author Comment

by:amaru96
ID: 40596698
I'm not running a SQL query. The datagrid is bound directly to the access database. The column in the database is of data type "Date/Time".

How can I get it so that the value within the dgv is read as a date?
0
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 40596714
Instead of basing it on a table put a query into place.
SELECT * FROM tbl ORDR BY dte

Where tbl is the table name and dte is the name of the field with the date in it.
0
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 40597181
You can also use template fields in your gv and control its sort property.
0
 
LVL 1

Author Comment

by:amaru96
ID: 40597190
The datagrid allows you to sort by any column, so if I was to run a query and order by date, that might work when the dgv is first populate, but what will happen when they sort by column1, and then they try to sort again by the date column? I presume it will end up the same as it is now?

eghtebas, how do I use template fields?
0
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 40597732
If you are allowing the datagrid to sort then you need to supply a custom sort function and in that Parse the string to a datetime object which you sort on.
0
 
LVL 1

Author Comment

by:amaru96
ID: 40597783
I did some reading on the Parse function, but couldn't really work it out.

Could you provide an example on how I would do this?
0
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 40597871
Dim dateString As String = "19/10/2014 21:02"
Dim convertedDate As Date = Date.Parse(dateString)

The convertedDate is now a DateTime not a formatted string
0
 
LVL 1

Author Comment

by:amaru96
ID: 40597909
Andy, I tried the below but it didn't seem to help. The sorting is still no good. Not sure if I'm doing something wrong?

Dim dateString As String
        For c = 0 To HistoryDataGridView.Rows.Count - 1
            dateString = HistoryDataGridView.Rows(c).Cells(3).Value
            Dim convertedDate As Date = Date.Parse(dateString)
            HistoryDataGridView.Rows(c).Cells(3).Value = convertedDate
        Next

Open in new window


I can see a change in the values of the column, but as I said, the sorting still doesnt work correctly.
0
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 40597995
Have a look at this:
https://msdn.microsoft.com/en-us/library/wstxtkxs%28v=vs.110%29.aspx
That is instructing the gridview to use a function you supply to determine the sorting.  In that you can convert the date (as formatted string) to a datetime value and compare to let the gridview know which one is to appear before the other one.   It is called multiple times each time you click the header to sort and it compare two items on each call.
0
 
LVL 1

Author Comment

by:amaru96
ID: 40599611
Andrew, sorry mate, I can't seem to do much with that. It also appears to be quite intensive process wise - this will be an issue as the datagrid is refreshed (re-bound) every 5 seconds.

Is there a way to determine what data type the column or even a call is currently set to?
0
 
LVL 1

Author Comment

by:amaru96
ID: 40599621
Actually, I tried the below code and it looks like the values are still being read as strings.

 For Each Row As DataGridViewRow In HistoryDataGridView.Rows
            If TypeOf (Row.Cells(3).Value) Is String Then
                MsgBox("String again!")
            End If
        Next

Open in new window


So unless I'm mistaken, until they are considered as dates, the sorting will not work?!
0
 
LVL 1

Accepted Solution

by:
amaru96 earned 0 total points
ID: 40599751
OK, so finally got it fixed.  Opened the DataSet in the designer and checked the data type for that column. It was set to system.string, changed it to system.datetime and now it sorts correctly.

Thanks for everyone's help.
0
 
LVL 44

Assisted Solution

by:AndyAinscow
AndyAinscow earned 2000 total points
ID: 40600111
From the question:
The third column is a date & time.

As I sort of mentioned in my very first comment a date would be sorted correctly at the point (access table or query) of origin of the data.  You have now confirmed that it is sorted as you wanted when it is a date.
0
 
LVL 1

Author Comment

by:amaru96
ID: 40609030
Hi Andy, not exactly sure what you mean. The data within the database was set as type: date\time. If I opened the database within Access I could happily sort it by date, but it was not sorting by date within VB. The dataset was reading it as a string, not as a date. I had to change it in there, not at the SQL query. Appreciate the time and effort you put in to help me though.

Happy to allocate you the points, however none of the solutions offered resolved my problem - but it did teach me quite a bit, so thanks.
0
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 40611739
>>The dataset was reading it as a string, not as a date.

That is precisely the problem I have been attempting to tell you about.  A DateTime is NOT a string.  A string is sorted alphabetically.  A datetime is sorted according to the date and time it contains.
eg.
Sunday 15 Feb
Monday 16 Feb
Tuesday 17 Feb

When handled and sorted as a datetime one has:
Sunday 15 Feb
Monday 16 Feb
Tuesday 17 Feb

but when sorted as strings one has:
Monday 16 Feb
Sunday 15 Feb
Tuesday 17 Feb

(As to how and why this functions you might want to look at how a datetime is stored in a database.)
0
 
LVL 1

Author Closing Comment

by:amaru96
ID: 40616141
The offered solutions didn't resolve my direct problem.
0

Featured Post

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.

Question has a verified solution.

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

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

705 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