Results 1 to 14 of 14

Thread: Correctly Sort Date Values in DataGridView

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2013
    Location
    Minneapolis, MN
    Posts
    488

    Correctly Sort Date Values in DataGridView

    Hello:

    I have a DataGridView with columns, that if double clicked can be sorted.

    I am using Typed DataSets.

    Below is a partial example of code:
    Code:
            Dim ta_JobInfo As JobSheetDataSetTableAdapters.JobInfoTableAdapter = New JobSheetDataSetTableAdapters.JobInfoTableAdapter
            ta_JobInfo.Fill(JobSheetDataSet1.JobInfo)
    
            Dim row_JobInfo_read As JobSheetDataSet.JobInfoRow = JobSheetDataSet1.JobInfo.FindByJobNo(_JobNo)
            With row_JobInfo_read
                JobNo = .JobNo
                Product = .Product
                Detail = .Detail
                DRAWINGNO = .DrawingNo
                Started = .Started ' Date
                Completed = .Completed ' Date
                Engineer = .Engineer
                Designer = .Designer
                Checker = .Checker
                EstHrs = CStr(.EstHrs) ' Decimal
                ShipTo = .ShipTo
                ShipDate = .ShipDate ' Date
                FMLDrawingNo = .FMLDrawingNo
    
            End With
    The result is some date columns. Yet when I click to sort them, they are sorted not by the date, but by their value. Both the database and the .NET variables are Date fields.

    ie:
    Code:
    Dim Started As Date
            Dim Completed As Date
            Dim ShipDate As Date
    The illustration below is what happens when I double click the ShipDate column in the DataGridView. You can see that it is not sorted by date but by the string value.

    Name:  2021-07-26 13_42_17-Form2.bmp
Views: 157
Size:  92.1 KB


    How can I make this work?
    - A 'Hyperactive Member' trying to make a difference in a hyperactive world!

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    107,473

    Re: Correctly Sort Date Values in DataGridView

    How EXACTLY are you populating that the grid? Is your DataTable bound to the grid or are you manually moving the data? If bound, is it done in code or in the designer? Via a BindingSource or directly? Do you have Option Strict set to On? If not, do so and see whether any issues are flagged. What do you see if you add this code to the first snippet:
    vb.net Code:
    1. MessageBox.Show(row_JobInfo_read.ShipDate.GetType().ToString())

  3. #3

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2013
    Location
    Minneapolis, MN
    Posts
    488

    Re: Correctly Sort Date Values in DataGridView

    MessageBox.Show(row_JobInfo_read.ShipDate.GetType().ToString()) = System.DateTime
    - A 'Hyperactive Member' trying to make a difference in a hyperactive world!

  4. #4

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2013
    Location
    Minneapolis, MN
    Posts
    488

    Re: Correctly Sort Date Values in DataGridView

    jcm:

    All date fields are System.DateTime.

    This post seems to address the issue: https://www.vbforums.com/showthread....by-date-column

    To which I can only conclude that Typed Datasets are not functioning well in this case.

    Partial Typed DataSet Code:

    Code:
            Dim ta_JobInfo As JobSheetDataSetTableAdapters.JobInfoTableAdapter = New JobSheetDataSetTableAdapters.JobInfoTableAdapter
            ta_JobInfo.Fill(JobSheetDataSet1.JobInfo)
    
            Dim row_JobInfo_read As JobSheetDataSet.JobInfoRow = JobSheetDataSet1.JobInfo.FindByJobNo(_JobNo)
            With row_JobInfo_read
                JobNo = .JobNo
                Product = .Product
                Detail = .Detail
                DRAWINGNO = .DrawingNo
                Started = .Started ' Date
                Completed = .Completed ' Date
                Engineer = .Engineer
                Designer = .Designer
                Checker = .Checker
                EstHrs = CStr(.EstHrs) ' Decimal
                ShipTo = .ShipTo
                ShipDate = .ShipDate ' Date
                FMLDrawingNo = .FMLDrawingNo
    
            End With
    
    .
    .
    .
    
    
            Dim x1 As String() = {JobLocation, JobNo, Detail, Product, DRAWINGNO, Started, Completed, ToProd, Engineer, Designer, Checker, EstHrs, ActualHrs, ReworkHrs, ShipTo, ShipDate, FMLDrawingNo, PrintPickSheet_new, CheckJobLetter_new, ObtainSerialNumber_new, CheckIssueTracker_new, CheckInterference_new, ProcessingComplete_new, CheckDescQtyAndRev_new, CheckReleasedState_new, CreateCSV_new, AddItems_NotModeledCSV_new, CheckPrintAndCSV_Peer_new, SendPrintAndCSV_ToAdmin_new, CompleteChanges_AfterPE_new, UpdateFML_new, UpdateCustomerCD_new, UpdatePrintAndCSV_Shop_new, UpdateSalesOrder_new}
    
            _dgv.Rows.Add(x1)
    - A 'Hyperactive Member' trying to make a difference in a hyperactive world!

  5. #5
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    33,471

    Re: Correctly Sort Date Values in DataGridView

    Code:
            Dim x1 As String() = {JobLocation, JobNo, Detail, Product, DRAWINGNO, Started, Completed, ToProd, Engineer, Designer, Checker, EstHrs, ActualHrs, ReworkHrs, ShipTo, ShipDate, FMLDrawingNo, PrintPickSheet_new, CheckJobLetter_new, ObtainSerialNumber_new, CheckIssueTracker_new, CheckInterference_new, ProcessingComplete_new, CheckDescQtyAndRev_new, CheckReleasedState_new, CreateCSV_new, AddItems_NotModeledCSV_new, CheckPrintAndCSV_Peer_new, SendPrintAndCSV_ToAdmin_new, CompleteChanges_AfterPE_new, UpdateFML_new, UpdateCustomerCD_new, UpdatePrintAndCSV_Shop_new, UpdateSalesOrder_new}
    
            _dgv.Rows.Add(x1)
    And there it is folks... the conversion to string...

    So 1) It is NOT bound to the grid, you're loading them manually, and 2) they are NOT a date or datetime, they are a string...

    The typed Dataset it working just fine... it's your code that isn't playing well with others.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  6. #6
    Frenzied Member
    Join Date
    Nov 2017
    Posts
    1,657

    Re: Correctly Sort Date Values in DataGridView

    Quote Originally Posted by ssabc View Post
    Code:
    .
    .
    .
    
            Dim x1 As String() = {JobLocation, JobNo, Detail, Product, DRAWINGNO, Started, Completed, ToProd, Engineer, Designer, Checker, EstHrs, ActualHrs, ReworkHrs, ShipTo, ShipDate, FMLDrawingNo, PrintPickSheet_new, CheckJobLetter_new, ObtainSerialNumber_new, CheckIssueTracker_new, CheckInterference_new, ProcessingComplete_new, CheckDescQtyAndRev_new, CheckReleasedState_new, CreateCSV_new, AddItems_NotModeledCSV_new, CheckPrintAndCSV_Peer_new, SendPrintAndCSV_ToAdmin_new, CompleteChanges_AfterPE_new, UpdateFML_new, UpdateCustomerCD_new, UpdatePrintAndCSV_Shop_new, UpdateSalesOrder_new}
    
            _dgv.Rows.Add(x1)
    ??????

    Is this how you are actually populating the DataGrid? Or is that some sort of test code you added for some reason?

    If it is the actual DataGrid population code, then there shouldn't be any confusion why this is happening, because look at what x1 is...

  7. #7
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    107,473

    Re: Correctly Sort Date Values in DataGridView

    As suggested, typed DataSets work just fine. The problem is that you're not using one. You should need to be transferring data from the DataTable to the DataGridView at all, so there should be a chance for you to break it. Just bind the DataTable to the grid, which is exactly why I asked you those questions that you didn't bother answering. Add a BindingSource to your form in the designer and bind your DataTable to the grid through that:
    vb.net Code:
    1. myBindingSource.DataSource = myDataTable
    2. myDataGridView.DataSource = myBindingSource
    Now the grid and table are bound, which means that changes in one affect the other and vice versa. That means that you can add a row to the DataTable and it will automatically be reflected in the grid. Most importantly, the cell values in the grid will be the actual data in the DataTable, so dates will be Dates, rather than the Strings that you are creating.

    The silly thing is that, even though what you're doing is bad, at least you wouldn't have broken the data if you had just used an Object array instead of a String array.

    You really need to set Option Strict On, which would have let you get away with doing that nonsense without at least being warned. Set it On in the project properties and also in the Vs options, so it will be On by default in future projects.

  8. #8

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2013
    Location
    Minneapolis, MN
    Posts
    488

    Re: Correctly Sort Date Values in DataGridView

    jcm:

    Changing the array to an object array does not do it.

    The reason why I am doing this is because there are three tables being populated into the datagridview.

    I have created this query in my dataset:
    Code:
    SELECT JobInfo.JobNo, JobInfo.Product, JobInfo.Detail, JobInfo.Feet, JobInfo.DrawingNo, JobInfo.Started, JobInfo.Completed, JobInfo.Engineer, JobInfo.Designer, JobInfo.Checker, JobInfo.EngineeringJobNotes, JobInfo.EstHrs, JobInfo.ShipTo, 
                      JobInfo.ShipDate, JobInfo.FMLDrawingNo, JobInfo.EstHrsLock, JobHrs.ActualHrs, JobHrs.ReworkHrs, JobHrs.ToProd, JobDetail.PrintPickSheet_new, JobDetail.CheckJobLetter_new, JobDetail.CheckJobLetter_rw, 
                      JobDetail.ObtainSerialNumber_new, JobDetail.CheckIssueTracker_new, JobDetail.CheckInterference_new, JobDetail.CheckInterference_rw, JobDetail.ProcessingComplete_new, JobDetail.ProcessingComplete_rw, 
                      JobDetail.CheckDescQtyAndRev_new, JobDetail.CheckDescQtyAndRev_rw, JobDetail.CheckReleasedState_new, JobDetail.CheckReleasedState_rw, JobDetail.CreateCSV_new, JobDetail.CreateCSV_rw, 
                      JobDetail.AddItems_NotModeledCSV_new, JobDetail.AddItems_NotModeledCSV_rw, JobDetail.CheckPrints_Peer_new, JobDetail.CheckPrints_Peer_rw, JobDetail.SendPrintAndCSV_ToAdmin_new, 
                      JobDetail.SendPrintAndCSV_ToAdmin_rw, JobDetail.CompleteChanges_AfterPE_new, JobDetail.CompleteChanges_AfterPE_rw, JobDetail.UpdateFML_new, JobDetail.UpdateFML_rw, JobDetail.UpdateCustomerCD_new, 
                      JobDetail.UpdateCustomerCD_rw, JobDetail.UpdatePrintAndCSV_Shop_new, JobDetail.UpdatePrintAndCSV_Shop_rw, JobDetail.UpdateSalesOrder_new
    FROM     JobInfo INNER JOIN
                      JobDetail ON JobInfo.JobNo = JobDetail.JobNo INNER JOIN
                      JobHrs ON JobInfo.JobNo = JobHrs.JobNo
    WHERE JobInfo.JobNo = @JobNo
    At this point, I'm unsure how to reference the query with code:
    Code:
            Dim ta_JobInfo As JobSheetDataSetTableAdapters.JobInfoTableAdapter = New JobSheetDataSetTableAdapters.JobInfoTableAdapter
            ta_JobInfo.FillBy_LookupJobNo2(JobSheetDataSet.JobNo, _JobNo)
    
            Dim row_JobInfo_read As JobSheetDataSet.JobInfoRow = JobSheetDataSet.JobInfo.FindByJobNo(_JobNo)
            With row_JobInfo_read
                JobNo = .JobNo
                Product = .Product
                Detail = .Detail
                DRAWINGNO = .DrawingNo
                Started = .Started ' Date
                Completed = .Completed ' Date
                
                .
                .
                .
    Apparently I'm missing something. Just following logic here that works elsewhere...

    I also alternatively tried making a second dataset, but it doesn't even show up as being available.
    - A 'Hyperactive Member' trying to make a difference in a hyperactive world!

  9. #9
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    107,473

    Re: Correctly Sort Date Values in DataGridView

    I've already told you what to do. You just need to do it. Bind your DataTable to your grid. JobSheetDataSet.JobNo is your DataTable.

  10. #10
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    107,473

    Re: Correctly Sort Date Values in DataGridView

    Quote Originally Posted by ssabc View Post
    Changing the array to an object array does not do it.
    Except it does, so if what you did didn't work then you obviously didn't do what I told you. Of course, you didn't bother to show what you did do, so we can't tell you what's wrong with it. Why make it easy for us to help you, right? You can prove that I'm right for yourself very easily. Just create a new project, add a DataGridView and then add this code:
    vb.net Code:
    1. Option Strict Off
    2.  
    3. Public Class Form1
    4.  
    5.     Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    6.         DataGridView1.Rows.Add(New String() {1, #1/1/2000#})
    7.         DataGridView1.Rows.Add(New String() {2, #1/2/2000#})
    8.         DataGridView1.Rows.Add(New String() {3, #1/10/2000#})
    9.     End Sub
    10.  
    11. End Class
    Run the project and click the second column header and you'll see that the data is order as text. Change the code to this, as I told you to:
    vb.net Code:
    1. Public Class Form1
    2.  
    3.     Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    4.         DataGridView1.Rows.Add(New Object() {1, #1/1/2000#})
    5.         DataGridView1.Rows.Add(New Object() {2, #1/2/2000#})
    6.         DataGridView1.Rows.Add(New Object() {3, #1/10/2000#})
    7.     End Sub
    8.  
    9. End Class
    and run the project again and you'll see that the data is order chronologically.

  11. #11

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2013
    Location
    Minneapolis, MN
    Posts
    488

    Re: Correctly Sort Date Values in DataGridView

    jcm:

    Apologies if what I documented was unclear.

    Does this not do the same thing?

    Code:
            Dim x1 As Object() = {JobLocation, JobNo, Detail, Product, DRAWINGNO, Started, Completed, ToProd, Engineer, Designer, Checker, EstHrs, ActualHrs, ReworkHrs, ShipTo, ShipDate, FMLDrawingNo, PrintPickSheet_new, CheckJobLetter_new, ObtainSerialNumber_new, CheckIssueTracker_new, CheckInterference_new, ProcessingComplete_new, CheckDescQtyAndRev_new, CheckReleasedState_new, CreateCSV_new, AddItems_NotModeledCSV_new, CheckPrintAndCSV_Peer_new, SendPrintAndCSV_ToAdmin_new, CompleteChanges_AfterPE_new, UpdateFML_new, UpdateCustomerCD_new, UpdatePrintAndCSV_Shop_new, UpdateSalesOrder_new}
    
            _dgv.Rows.Add(x1)

    The rest of the post was about pulling three tables into one query and letting the query populate the datagridview directly.
    - A 'Hyperactive Member' trying to make a difference in a hyperactive world!

  12. #12
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    107,473

    Re: Correctly Sort Date Values in DataGridView

    Quote Originally Posted by ssabc View Post
    jcm:

    Apologies if what I documented was unclear.

    Does this not do the same thing?

    Code:
            Dim x1 As Object() = {JobLocation, JobNo, Detail, Product, DRAWINGNO, Started, Completed, ToProd, Engineer, Designer, Checker, EstHrs, ActualHrs, ReworkHrs, ShipTo, ShipDate, FMLDrawingNo, PrintPickSheet_new, CheckJobLetter_new, ObtainSerialNumber_new, CheckIssueTracker_new, CheckInterference_new, ProcessingComplete_new, CheckDescQtyAndRev_new, CheckReleasedState_new, CreateCSV_new, AddItems_NotModeledCSV_new, CheckPrintAndCSV_Peer_new, SendPrintAndCSV_ToAdmin_new, CompleteChanges_AfterPE_new, UpdateFML_new, UpdateCustomerCD_new, UpdatePrintAndCSV_Shop_new, UpdateSalesOrder_new}
    
            _dgv.Rows.Add(x1)

    The rest of the post was about pulling three tables into one query and letting the query populate the datagridview directly.
    Yes it does do the same thing so, given that what I posted demonstrably works (did you test it to see for yourself), what you did must work too. That means that if you're not seeing what you expect then either there's an issue with your expectations or else you're doing something else wrong.

  13. #13
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    33,471

    Re: Correctly Sort Date Values in DataGridView

    I'm still struggling as to why you're not just biding the datatable to teh grid directly - or through a BindingSource as previously suggested.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  14. #14
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    107,473

    Re: Correctly Sort Date Values in DataGridView

    Quote Originally Posted by techgnome View Post
    I'm still struggling as to why you're not just biding the datatable to teh grid directly - or through a BindingSource as previously suggested.

    -tg
    Indeed. The bit about the Object array was really just an aside - an indication that this was a problem of the OP's own making because there was no reason to use a String array there in the first place. Based on the information provided, there's simply no reason not to be using data-binding. Binding should ALWAYS be your first option and not used only if there is a specific reason not to use it. I don't see any such reason here. In fact, if the DataSet has been added to the form in the designer, the data-binding could also be done in the designer rather than in code.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width