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

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
LVL 1
amaru96Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
amaru96Connect With a Mentor Author Commented:
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
 
AndyAinscowFreelance programmer / ConsultantCommented:
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
 
amaru96Author Commented:
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

 
AndyAinscowFreelance programmer / ConsultantCommented:
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
 
Mike EghtebasDatabase and Application DeveloperCommented:
You can also use template fields in your gv and control its sort property.
0
 
amaru96Author Commented:
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
 
AndyAinscowFreelance programmer / ConsultantCommented:
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
 
amaru96Author Commented:
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
 
AndyAinscowFreelance programmer / ConsultantCommented:
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
 
amaru96Author Commented:
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
 
AndyAinscowFreelance programmer / ConsultantCommented:
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
 
amaru96Author Commented:
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
 
amaru96Author Commented:
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
 
AndyAinscowConnect With a Mentor Freelance programmer / ConsultantCommented:
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
 
amaru96Author Commented:
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
 
AndyAinscowFreelance programmer / ConsultantCommented:
>>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
 
amaru96Author Commented:
The offered solutions didn't resolve my direct problem.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.