Solved

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

Posted on 2015-02-07
17
546 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
 
LVL 33

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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

747 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now