Sort a string column in dataview numerically

Posted By: qwert231 | Sep 17th @ 11:07 AM
page 1 of 1
Comments: 5 | Views: 421
#Sep 17th @ 11:07 AM
qwert231
qwert231
M Kenyon

Working on an app where the LogDate column in the dataset is string. We load it into a DataView, filter, and pass it to a SQL report. If I sort by LogDate I get an ordering of 10,11,12,6,7,8,9...

 

Here's our DataView declaration:

Dim dailyTimeLogDV As New DataView(Me._timeActivityReportsDataSet.DailyLogShiftTimes, _dateExpr, "LogDate", DataViewRowState.CurrentRows)

#Sep 17th @ 12:34 PM
spivonious
spivonious

Hmm...can you change the dataset to add a new column that contains the LogDate converted to a number?

 

If not you could always play around with string formatting in the view, so you get 01, 02, 03, ..., 10, 11, 12, etc..

 

I don't think there's a way to pass a compare function into the view for sorting.  It would be nice if there was.

#Sep 17th @ 6:05 PM
AdamSpeight2008
AdamSpeight2008
The Bandito Coder

Is possible to you could sort the dataset with LINQ, something like.

[code]

Dim s=From d As String From NumberStrings Select d OrderBy Double.Parse(s)

[/code]

#Sep 19th @ 4:11 AM
ManipUni
ManipUni
Proving QQ for 5 years!

Asking to sort a DataView is just asking the wrong question...

The question you should be asking is how do you sort a DataSet. Since the DataView is just a visual representation of whatever you have in your DataSet.

 

If you are putting the data into the DataSet using SQL then obviously the easiest way would be to use an "order by." The DataView's sort events are just generic events for YOU to handle. You have to re-read the data, re-sort, and output a new DataView.

 

PS - I actually have no idea what I'm talking about. Smiley

#Sep 22nd @ 10:47 AM
Sabot
Sabot
My name is Dave Oliver. I'm a Technical Architect.

Manip is right, it is easier to sort the data before it gets into a dataset and even better if the SQL is in a stored proc or view so the SQL optimiser can make a query plan.

 

P.S. I do have an idea cos databases are my thing. Smiley

 

... and just to add some value ... these guys are the ones to read if you want to know about how to optimise queries

 

http://blogs.msdn.com/sqlqueryprocessing/default.aspx

#Sep 23rd @ 12:35 AM
Dr Herbie
Dr Herbie
Horses for courses

Hmm.

 

My take on this is that for a report, get the database to sort as Sabot suggests.

 

On the other hand, if it was for UI display, then the sorting of the data would be the UI's responsability, so I would use Spivonious' suggestion of an additional column for the 'sortableLogDate' numberic value and letting the UI sort on that.

 

Herbie

 

page 1 of 1
Comments: 5 | Views: 421
Microsoft Communities