custom sorting datatables
-
Please note that I am a beginner VB.net programmer. Any help you can offer would be appreciated. I am trying to sort a datatable loaded from an SQL db table. There are 2 sort keys, both ascending, and 9 other columns. The primary key, "PurchaseOrderID", can contain nulls, although it is usually a string of format "999999_999". After loading the ds, I use the following code to try to sort the records: Dim myDataView As DataView myDataView = ds.Tables("tPIA").DefaultView myDataView.Sort = ("PurchaseOrderID, TimeNeeded") The problem is I need the rows with null values in PurchaseOrderID to all sort to the high end. I know one can write a custom comparer to modify sort order but I haven't seen the syntax of how one can use a comparer with a dataview. Can this be done? If so, what's the syntax? If not, do you have any other suggestions? I originally loaded the SQL table into a Collection but there didn't seem to be a sort method available there. So I loaded the Collection into an array and wrote a custom comparer, but then I couldn't seem to sort on the 2 fields, as required. Thank you, mif mif
-
Please note that I am a beginner VB.net programmer. Any help you can offer would be appreciated. I am trying to sort a datatable loaded from an SQL db table. There are 2 sort keys, both ascending, and 9 other columns. The primary key, "PurchaseOrderID", can contain nulls, although it is usually a string of format "999999_999". After loading the ds, I use the following code to try to sort the records: Dim myDataView As DataView myDataView = ds.Tables("tPIA").DefaultView myDataView.Sort = ("PurchaseOrderID, TimeNeeded") The problem is I need the rows with null values in PurchaseOrderID to all sort to the high end. I know one can write a custom comparer to modify sort order but I haven't seen the syntax of how one can use a comparer with a dataview. Can this be done? If so, what's the syntax? If not, do you have any other suggestions? I originally loaded the SQL table into a Collection but there didn't seem to be a sort method available there. So I loaded the Collection into an array and wrote a custom comparer, but then I couldn't seem to sort on the 2 fields, as required. Thank you, mif mif
Try this MyDataView.Sort = "PurchaseOrderID, TimeNeeded ASC" -or- MyDataView.Sort = "PurchaseOrderID ASC, TimeNeeded ASC" Use DESC for Descending *EDIT* I just tried it with a dataview containing null values and it sorts the null values to the top of list in my datagrid. I really don't see a problem with your code. I thought maybe the parentheses in your .sort code was the problem but it works either way.
-
Try this MyDataView.Sort = "PurchaseOrderID, TimeNeeded ASC" -or- MyDataView.Sort = "PurchaseOrderID ASC, TimeNeeded ASC" Use DESC for Descending *EDIT* I just tried it with a dataview containing null values and it sorts the null values to the top of list in my datagrid. I really don't see a problem with your code. I thought maybe the parentheses in your .sort code was the problem but it works either way.
Thank you for the help - however I am not getting the results expected. Although ASC is the deafult, I did add ASC to both sort keys: myDataView.Sort = ("PurchaseOrderID ASC, TimeNeeded ASC") Here is a sample of the output, where PurchaseOrderID is column(2) and TimeNeeded is column(9) on a 0 to 11 basis: Row: 119 34028_1_1, 7/25/2005 12:00:00 AM, {unspecified}, , 12345-0003, 1, xxx, 0, , 7/25/2005 12:00:00 AM, M, ------------------------ Row: 120 33734_1_1, 9/11/2005 12:00:00 AM, {unspecified}, , 12346-0002, 1, xxx, 0, , 9/11/2005 12:00:00 AM, M, ------------------------ Row: 121 33597_1_1, 12/12/2012 12:00:00 AM, {unspecified}, , 12345-0001, 1, xxx, 0, , 12/12/2012 12:00:00 AM, M, ------------------------ Row: 122 33596_1_1, 12/12/2012 12:00:00 AM, {unspecified}, , 12345-0001, 1, xxx, 0, , 12/12/2012 12:00:00 AM, M, ------------------------ Row: 123 33596_1_2, 12/12/2012 12:00:00 AM, {unspecified}, , 12345-0001, 2, xxx, 0, , 12/12/2012 12:00:00 AM, M, ------------------------ Row: 124 33597_1_2, 12/12/2012 12:00:00 AM, {unspecified}, , 12344-0001, 2, xxx, 0, , 12/12/2012 12:00:00 AM, M, ------------------------ Row: 125 32798_1_1, 12/31/2029 12:00:00 AM, {unspecified}, , 1234-0003, 2, xxx, 0, , 12/31/2029 12:00:00 AM, M, ------------------------ Row: 126 32412_1_5, 12/31/2029 12:00:00 AM, {unspecified}, , 12345-0002, 3, xxx, 0, , 12/31/2029 12:00:00 AM, M, ------------------------ Row: 127 118121-000, 1/28/2005 12:00:00 AM, {unspecified}, 25, 12345-005, 9, xxx, 0, , 1/28/2005 12:00:00 AM, M, ------------------------ Row: 128 117604-101, 1/13/2005 12:00:00 AM, 147347_020, 540, 12345-1234-006, 1, {unspecified}, 0, 12/22/04, 1/18/2005 12:00:00 AM, P, ------------------------ Row: 129 117749-101, 2/3/2005 12:00:00 AM, 147347_021, 540, 12345-1234-006, 1, {unspecified}, 0, 12/22/04, 2/8/2005 12:00:00 AM, P, ------------------------ Row: 130 117941-101, 3/9/2005 12:00:00 AM, 150236_005, 105, 12345-001, 10500, {unspecified}, 0, 03/07/05, 1/19/2005 12:00:00 AM, M, ------------------------ Row: 131 118122-000, 1/26/2005 12:00:00 AM, {unspecified}, 20, 12345-004, 12, xxxx, 0, , 1/26/2005 12:00:00 AM, M, ------------------------ Row: 132 118110-000, 12/20/2004 12:00:00 AM, {unspecified}, 20, 12345-003, 5, xxxx, 0, , 12/9/2004 12:00:00 AM, M, ------------------------ Take a look at rows 126 and 127 - although PurchaseOrderID has been {unspecified} through this point, TimeNeeded has jumped backwards from 12/31/2029 to 1/28/2005. The
-
Thank you for the help - however I am not getting the results expected. Although ASC is the deafult, I did add ASC to both sort keys: myDataView.Sort = ("PurchaseOrderID ASC, TimeNeeded ASC") Here is a sample of the output, where PurchaseOrderID is column(2) and TimeNeeded is column(9) on a 0 to 11 basis: Row: 119 34028_1_1, 7/25/2005 12:00:00 AM, {unspecified}, , 12345-0003, 1, xxx, 0, , 7/25/2005 12:00:00 AM, M, ------------------------ Row: 120 33734_1_1, 9/11/2005 12:00:00 AM, {unspecified}, , 12346-0002, 1, xxx, 0, , 9/11/2005 12:00:00 AM, M, ------------------------ Row: 121 33597_1_1, 12/12/2012 12:00:00 AM, {unspecified}, , 12345-0001, 1, xxx, 0, , 12/12/2012 12:00:00 AM, M, ------------------------ Row: 122 33596_1_1, 12/12/2012 12:00:00 AM, {unspecified}, , 12345-0001, 1, xxx, 0, , 12/12/2012 12:00:00 AM, M, ------------------------ Row: 123 33596_1_2, 12/12/2012 12:00:00 AM, {unspecified}, , 12345-0001, 2, xxx, 0, , 12/12/2012 12:00:00 AM, M, ------------------------ Row: 124 33597_1_2, 12/12/2012 12:00:00 AM, {unspecified}, , 12344-0001, 2, xxx, 0, , 12/12/2012 12:00:00 AM, M, ------------------------ Row: 125 32798_1_1, 12/31/2029 12:00:00 AM, {unspecified}, , 1234-0003, 2, xxx, 0, , 12/31/2029 12:00:00 AM, M, ------------------------ Row: 126 32412_1_5, 12/31/2029 12:00:00 AM, {unspecified}, , 12345-0002, 3, xxx, 0, , 12/31/2029 12:00:00 AM, M, ------------------------ Row: 127 118121-000, 1/28/2005 12:00:00 AM, {unspecified}, 25, 12345-005, 9, xxx, 0, , 1/28/2005 12:00:00 AM, M, ------------------------ Row: 128 117604-101, 1/13/2005 12:00:00 AM, 147347_020, 540, 12345-1234-006, 1, {unspecified}, 0, 12/22/04, 1/18/2005 12:00:00 AM, P, ------------------------ Row: 129 117749-101, 2/3/2005 12:00:00 AM, 147347_021, 540, 12345-1234-006, 1, {unspecified}, 0, 12/22/04, 2/8/2005 12:00:00 AM, P, ------------------------ Row: 130 117941-101, 3/9/2005 12:00:00 AM, 150236_005, 105, 12345-001, 10500, {unspecified}, 0, 03/07/05, 1/19/2005 12:00:00 AM, M, ------------------------ Row: 131 118122-000, 1/26/2005 12:00:00 AM, {unspecified}, 20, 12345-004, 12, xxxx, 0, , 1/26/2005 12:00:00 AM, M, ------------------------ Row: 132 118110-000, 12/20/2004 12:00:00 AM, {unspecified}, 20, 12345-003, 5, xxxx, 0, , 12/9/2004 12:00:00 AM, M, ------------------------ Take a look at rows 126 and 127 - although PurchaseOrderID has been {unspecified} through this point, TimeNeeded has jumped backwards from 12/31/2029 to 1/28/2005. The
mfriedenthal wrote: The watch has these colums delimited by "#"s so I have reason to believe that .net is indeed seeing the fields as dates and not strings. Are you sure the data isn't being handled as a string? I made a quick table in ms access and it sorted it fine with the "TimeNeeded" column as a DATE/TIME but when i changed it to a string it basically returned results like you show. Double check the column data type:
Debug.WriteLine(MyDataSet.Tables("MyTable").Columns("TimeNeeded").DataType.ToString)Do the same for your PurchaseOrderID column. Other than being the wrong datatype I can't understand why it doesn't work for you. -
mfriedenthal wrote: The watch has these colums delimited by "#"s so I have reason to believe that .net is indeed seeing the fields as dates and not strings. Are you sure the data isn't being handled as a string? I made a quick table in ms access and it sorted it fine with the "TimeNeeded" column as a DATE/TIME but when i changed it to a string it basically returned results like you show. Double check the column data type:
Debug.WriteLine(MyDataSet.Tables("MyTable").Columns("TimeNeeded").DataType.ToString)Do the same for your PurchaseOrderID column. Other than being the wrong datatype I can't understand why it doesn't work for you.Thanks for the advice, but I did as you suggested and I got back: System.DateTime System.String for TimeNeeded and PurchaseOrderID, respectively. Which is what I expected. Still researching... Any help would be appreciated. Thanks!