asked on

my rows are not sorting correctly in datatable

Hello experts i have one datatable called newtable the first field contains a date time stamp that i converted to a string in military time. I am trying to fill another table called dt2 from the new table, but when i loop through the data in dt2 the rows are not sorted correctly any ideas?
The reason for this is that i want to convert the time back to normal once it is sorted then bind to datagrid, and i cant not sort a string field correctly by date time unless i use military time. The date field has to remain a string because i also insert characters into it. Currently this sort works but its military time:
"Datagrid3.DataSource = New DataView(newtable, "", "DatePLC", DataViewRowState.CurrentRows)". I cant figure out why my other sort is not working.

Dim dv As New DataView
Dim ds As New DataSet
ds.Tables.Add(newtable)
With dv
.Table = ds.Tables(0)
.Sort = "DatePLC"
.RowStateFilter = DataViewRowState.CurrentRows
End With
Dim dt2 As New DataTable
dt2 = dv.Table
Dim value2a As String
x = 0
For x = 0 To dt2.Rows.Count - 1
value2a = dt2.Rows(x).Item(0) ' i am using this to check the value
' when i get the sort working covert to normal time
x += 1
Next

'Currently i am using this and it works except that it displays the military time.
Datagrid3.DataSource = New DataView(newtable, "", "DatePLC", DataViewRowState.CurrentRows)
Datagrid3.DataBind()

tentavarious

ASKER

Shouldnt this return the same results as above? Because it doesnt.

Dim dv As New DataView
Dim ds As New DataSet
ds.Tables.Add(newtable)
With dv
.Table = ds.Tables(0)
.Sort = "DatePLC"
.RowStateFilter = DataViewRowState.CurrentRows
End With
Dim dt2 As New DataTable
dt2 = dv.Table

Datagrid3.DataSource = dt2
Datagrid3.DataBind()

Bob Learned

The dv.Table returns the underlying table, which is not what you think it is. It is the source table for the DataView.

Try binding to the DataView instead:

Datagrid3.DataSource = dv
Datagrid3.DataBind()

Bob

tentavarious

ASKER

Yeah that works but like i said i need to convert the dataview to a datatable so that i can loop through and make the changes.

Bob Learned

Proposed solution:

Dim ds As New DataSet
Dim dt1 As DataTable = ds.Tables(0)

' Clone the source table's structure.
Dim dt2 As DataTable = dt1.Clone

' Add all the rows from the source to the target.
For Each row As DataRow In dt1.Rows
dt2.ImportRow(row)
Next row

dt2.DefaultView.Sort = "DatePLC"
dt2.DefaultView.RowStateFilter = DataViewRowState.CurrentRows

Datagrid3.DataSource = dt2.DefaultView
Datagrid3.DataBind()

Bob

tentavarious

ASKER

Newtable contains a string field with military time and dates. I want to sort newtable by the date field using dataview then convert dataview to another datatable, this way all the records are sorted correctly and i can convert the military time back to normal time (hh:mm:tt). The reason that date field is a string is because it contains characters like "to" and "from" for example 8/16/2005 23:00 to 23:59 can be sorted, but they want it to look like 6/16/2005 11:00 PM to 11:59 PM.

ASKER CERTIFIED SOLUTION

Bob Learned

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

graye

Another solution is to skip the DataView and sort the rows in the underlying DataTable

Sub DataTableSort(ByVal dt As DataTable, ByVal colname As String)
Dim dt_temp As DataTable
Dim dr, dr2 As DataRow
Dim drs As DataRow()

If dt.Rows.Count > 1 Then
' make a copy, clear the original
dt_temp = dt.Copy
dt.Clear()

' sort and import the data back
drs = dt_temp.Select(Nothing, colname & " ASC")
For Each dr In drs
dt.ImportRow(dr)
Next
End If
End Sub