DataTable.Select method does not return correct sort order?!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi! I have a strange problem with the results returned by Datatable's select
method that I pass the columns by which I want the results sorted. When I
run the same query in ISQLW with thos order by fields, it returns the data in
one order, when I perform the select programmatically there are discrepancies
in the result. If someone can enlighten me as to what I'm doing wrong or how
I can go about achieving the same result sets in both cases I'll greatly
appreciate it.

Alex
 
Alex,

Strange, how do you want to do a sort using the datatable select.

I don't know any Sort posibility in the expression and therefore you make me
curious.

Cor
 
Hi! Thanks for replying.
Here's the sample code.

Dim oRows As DataRow() = oDataTable.Select("", "[Receive Method], [Order
Receive Date], [Order Identifier], [Msg Id], [Order Status]")
Dim oRow As DataRow
Dim sbRptData As New StringBuilder
Dim oItem As Object
Dim iCol As Integer

For Each oRow In oRows
For Each oItem In oRow.ItemArray
If Not (iCol = UBound(oRow.ItemArray)) Then
sbRptData.Append(oItem.ToString & ",")
Else
sbRptData.Append(Trim(oItem.ToString) & vbCrLf)
End If
iCol += 1
Next
Next

Where oDataTable is DataTable which contains data. Datatype for all columns
in DataTable is "String".
 
No, Cor ... the third parameter is a string (you must've missed the quotes
around it).

Still, Alex, I'm not sure what you're trying to do or show with that code?

~~Bonnie
 
Guys, I though it was fairly simple. I'm just trying to get an array of rows
from source datatable sorted by columns that I specify.
Here's the definition of one of the overloads for Select method of DataTable
from MSDN site:

Gets an array of all DataRow objects that match the filter criteria, in the
specified sort order.

Supported by the .NET Compact Framework.

[Visual Basic] Overloads Public Function Select(String, String) As DataRow()


First string being the customized expression (or emty string to return all
rows), second is the sort criterias, which is basically the column names I
need to sort by.
If you have some other way to get data in a specific order from an existing
datatable object I'm open to it. My concerns are a) to get the sort order
exactly as it is returned when I do similar statement in Query analyser, b)
to have good performance since I'm dealing with large amount of data.

Thanks again!
 
Bonnie,

You are right about the quotes, it was more to point Alex on that string,
because in my opinion has it to be a filterextression and a sort argument.

Returned is in my opinion in this case nothing, while when it is selected it
will returns datarows confirm the originals.

However I can be wrong of course.

Cor
 
Alex,

Wouldn't a DataView be more what you're looking for? Much easier to mess
with than an array of DataRows, IMO.

As to why the order may be different than it is in Query Analyzer, it may
have to do with DataTypes. I notice you have some dates in your data. Are
they strings or DateTime?

~~Bonnie




Alex said:
Guys, I though it was fairly simple. I'm just trying to get an array of rows
from source datatable sorted by columns that I specify.
Here's the definition of one of the overloads for Select method of DataTable
from MSDN site:

Gets an array of all DataRow objects that match the filter criteria, in the
specified sort order.

Supported by the .NET Compact Framework.

[Visual Basic] Overloads Public Function Select(String, String) As DataRow()


First string being the customized expression (or emty string to return all
rows), second is the sort criterias, which is basically the column names I
need to sort by.
If you have some other way to get data in a specific order from an existing
datatable object I'm open to it. My concerns are a) to get the sort order
exactly as it is returned when I do similar statement in Query analyser, b)
to have good performance since I'm dealing with large amount of data.

Thanks again!

Bonnie Berent said:
No, Cor ... the third parameter is a string (you must've missed the quotes
around it).

Still, Alex, I'm not sure what you're trying to do or show with that code?

~~Bonnie
Click to expand...
Click to expand...
 
Alex,

It seems that I was blind, you are probably up to something as this.

\\\
Module main
Public Sub main()
Dim oDataTable As New DataTable
oDataTable.Columns.Add("a")
oDataTable.Columns.Add("b")
oDataTable.Columns.Add("c")
oDataTable.Columns.Add("d")
oDataTable.LoadDataRow(New Object() {"Hello", "how", "is", "this"},
True)
oDataTable.LoadDataRow(New Object() {"sollution", "fiting", "you",
"?"}, True)
Dim oRows As DataRow() = oDataTable.Select("", "a, b, c, d Asc")
Dim oRow As DataRow
Dim sbRptData As New System.text.StringBuilder
Dim oItem As Object
Dim iCol As Integer
For Each oRow In oRows
For Each oItem In oRow.ItemArray
sbRptData.Append(oItem.ToString)
sbRptData.Append(" ")
Next
Next
MessageBox.Show(sbRptData.ToString)
End Sub
End Module
///

I hope this helps,

Cor
 
Bonnie, I've tried DataView, but the results were not sorted at all when I
tried to use its Sort method. I tried several syntaxes as far as passing
items in square brackets, or trying double quotes, etc... the only one it was
allowing is passing them in square brackets but the results weren't getting
sorted at all.
The datatypes for all columns as I indicated before of type String (or
varchar) whatever you prefer.

so, as it stands I'm still unable to sort the data the same way using .net
methods.

Cor, not sure what is the purpose of your code and how it is different in
the sense of what I do. The getting data into datatable is not the
problematic part, the only issue I have at this point is getting the data in
the source DataTable to sort correctly, so I can output the values in proper
order to a CSV file.

Thanks!

Bonnie Berent said:
Alex,

Wouldn't a DataView be more what you're looking for? Much easier to mess
with than an array of DataRows, IMO.

As to why the order may be different than it is in Query Analyzer, it may
have to do with DataTypes. I notice you have some dates in your data. Are
they strings or DateTime?

~~Bonnie




Alex said:
Guys, I though it was fairly simple. I'm just trying to get an array of rows
from source datatable sorted by columns that I specify.
Here's the definition of one of the overloads for Select method of DataTable
from MSDN site:

Gets an array of all DataRow objects that match the filter criteria, in the
specified sort order.

Supported by the .NET Compact Framework.

[Visual Basic] Overloads Public Function Select(String, String) As DataRow()


First string being the customized expression (or emty string to return all
rows), second is the sort criterias, which is basically the column names I
need to sort by.
If you have some other way to get data in a specific order from an existing
datatable object I'm open to it. My concerns are a) to get the sort order
exactly as it is returned when I do similar statement in Query analyser, b)
to have good performance since I'm dealing with large amount of data.

Thanks again!

Bonnie Berent said:
No, Cor ... the third parameter is a string (you must've missed the quotes
around it).

Still, Alex, I'm not sure what you're trying to do or show with that code?

~~Bonnie



:

Alex,

I don't know a datatable.Select with that overloaded method, the most
parameters you can give are 3 and you are suplying 6. Do you not get a build
error.

http://msdn.microsoft.com/library/d.../frlrfsystemdatadatatableclassselecttopic.asp

Cor
Click to expand...
Click to expand...
Click to expand...
 
Alex
Cor, not sure what is the purpose of your code and how it is different in
the sense of what I do. The getting data into datatable is not the
problematic part, the only issue I have at this point is getting the data
in
the source DataTable to sort correctly, so I can output the values in
proper
order to a CSV file.
Click to expand...

That was to test your code. I tried it with this and now even complete,
something you could have done yourself as well of course and when it not was
working give us that as sample.

\\\
Public Sub main()
Dim oDataTable As New DataTable
oDataTable.Columns.Add("a")
oDataTable.Columns.Add("b")
Dim a As String = "1"
Dim b As String = "2"
Dim c As String = "3"
oDataTable.LoadDataRow(New Object() {b, b}, True)
oDataTable.LoadDataRow(New Object() {b, a}, True)
oDataTable.LoadDataRow(New Object() {a, c}, True)
Dim oRows As DataRow() = oDataTable.Select("", "[a], Asc")
Dim oRow As DataRow
Dim sbRptData As New System.text.StringBuilder
Dim oItem As Object
Dim iCol As Integer
For Each oRow In oRows
For Each oItem In oRow.ItemArray
If Not (iCol = UBound(oRow.ItemArray)) Then
'If Not iCol = oRow.ItemArray.Length - 1 Then
'is in my opinion much nicer
sbRptData.Append(oItem.ToString & ",")
Else
sbRptData.Append(Trim(oItem.ToString) & vbCrLf)
End If
iCol += 1
Next
Next
MessageBox.Show(sbRptData.ToString)
End Sub
End Module
///

What means in my opinion that probably than (one or more) of the datanames
you use are wrong. I don't see another difference.

To thest this it is in my opinion better to make your sample more simple and
just remove some sorts fields, that makes it as well more easy to show what
you want to do, I have to admit that I completly was not aware what you was
doing when I was looking at your program. (Mostly because of the brackets
which are useless in this sample)

I hope this helps,

Cor
 
Back
Top