-
Jul 26th, 2021, 01:52 PM
#1
Thread Starter
Fanatic Member
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.
How can I make this work?
- A 'Hyperactive Member' trying to make a difference in a hyperactive world! And recently, I've been promoted to a 'Finatic Member,' whatever that means!
-
Jul 26th, 2021, 07:55 PM
#2
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:
MessageBox.Show(row_JobInfo_read.ShipDate.GetType().ToString())
-
Jul 27th, 2021, 07:56 AM
#3
Thread Starter
Fanatic Member
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! And recently, I've been promoted to a 'Finatic Member,' whatever that means!
-
Jul 27th, 2021, 09:26 AM
#4
Thread Starter
Fanatic Member
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! And recently, I've been promoted to a 'Finatic Member,' whatever that means!
-
Jul 27th, 2021, 09:40 AM
#5
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
-
Jul 27th, 2021, 09:40 AM
#6
Re: Correctly Sort Date Values in DataGridView
Originally Posted by ssabc
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...
-
Jul 27th, 2021, 09:58 AM
#7
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:
myBindingSource.DataSource = myDataTable
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.
-
Jul 27th, 2021, 04:42 PM
#8
Thread Starter
Fanatic Member
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! And recently, I've been promoted to a 'Finatic Member,' whatever that means!
-
Jul 27th, 2021, 07:16 PM
#9
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.
-
Jul 27th, 2021, 07:29 PM
#10
Re: Correctly Sort Date Values in DataGridView
Originally Posted by ssabc
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:
Option Strict Off
Public Class Form1
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
DataGridView1.Rows.Add(New String() {1, #1/1/2000#})
DataGridView1.Rows.Add(New String() {2, #1/2/2000#})
DataGridView1.Rows.Add(New String() {3, #1/10/2000#})
End Sub
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:
Public Class Form1
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
DataGridView1.Rows.Add(New Object() {1, #1/1/2000#})
DataGridView1.Rows.Add(New Object() {2, #1/2/2000#})
DataGridView1.Rows.Add(New Object() {3, #1/10/2000#})
End Sub
End Class
and run the project again and you'll see that the data is order chronologically.
-
Jul 28th, 2021, 12:17 PM
#11
Thread Starter
Fanatic Member
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! And recently, I've been promoted to a 'Finatic Member,' whatever that means!
-
Jul 28th, 2021, 07:31 PM
#12
Re: Correctly Sort Date Values in DataGridView
Originally Posted by ssabc
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.
-
Jul 28th, 2021, 07:48 PM
#13
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
-
Jul 28th, 2021, 08:15 PM
#14
Re: Correctly Sort Date Values in DataGridView
Originally Posted by techgnome
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|