Solved

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

Posted on 2015-02-07
17
648 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
  • 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
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 
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 500 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Welcome my friends to the second instalment and follow-up to our Minify and Concatenate Your Scripts and Stylesheets (http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/A_4334-Minify-and-Concatenate-Your-Scripts-and-Stylesheets.html)…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

735 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