Sorting a dataview Numerically

[Saputra]

Does anyone know how to sort a data view numerically? By default, when
you sort a field from a table in a database, it sorts it in
alpha-numerical order. In MS Access, sort is by alpha-numeric, that
is, numbers sort from 1, 10 ,11, 1X, 2, 21, 2X, etc. I want VB.NET to
sort a column in data view numerically, so it goes 1 - 9, 10 - 19, 20 -
29, etc..

[Piotr Szukalski]

Hi!

Does anyone know how to sort a data view numerically? By default, when
you sort a field from a table in a database, it sorts it in
alpha-numerical order. In MS Access, sort is by alpha-numeric, that
is, numbers sort from 1, 10 ,11, 1X, 2, 21, 2X, etc. I want VB.NET to
sort a column in data view numerically, so it goes 1 - 9, 10 - 19, 20 -
29, etc..

When you put data into DataTable/DataSet put objects implementing
IComparable interface and set 'DataType' property of 'DataColumn' class.
I supose you store 'string' values in the columns instead of simply 'int'
or you have created columns just giving them names and forgot to set
'DataType'.

Cheers,
Piotrek

[Saputra]

I found a way of sorting data numerically in VB.NET. One way that
worked was by storing values into an array first and then sort it by
using a sorting algorithm, e.g. QuickSort method.

The thing that I would like to do, if it is possible is using a sorting
algorithm to sort rows from a table in database. That is, write to a
databse and compare the rows and swap the position around in ascending
order.

One other issue that makes it more complicated is that the primary key
field that I would like to base the sort from is that the values are
alpha-numeric, e.g. P-1, P-10, P-2, ..., and I would like the sort base
it on the numbers that are part of the values so that it sorts the rows
in numerical order. That is, the sort should be like as follows from
example above; P-1, P-2, ..., P-10, etc.

*** Sent via Developersdex http://www.developersdex.com ***

[Piotr Szukalski]

Hi!

The thing that I would like to do, if it is possible is using a sorting
algorithm to sort rows from a table in database. That is, write to a
databse and compare the rows and swap the position around in ascending
order.

One other issue that makes it more complicated is that the primary key
field that I would like to base the sort from is that the values are
alpha-numeric, e.g. P-1, P-10, P-2, ..., and I would like the sort base
it on the numbers that are part of the values so that it sorts the rows
in numerical order. That is, the sort should be like as follows from
example above; P-1, P-2, ..., P-10, etc.

OK, I assume you are looking for a tricky 'order by' clause to sort your
data. I guess you use SQL Server or MSDE, so the 'order by' clasuse could
look like this ('id' is the primary key column):

.... order by cast(substring(id, 3, len(id) - 2) as int) desc

Another way is to add 'atrifical' column in your select clause:

select id, cast(substring(id, 3, len(id) - 2) as int) as artif_id, ...
from TableName

Now you can order by 'artif_id' any way you want. Only thing you have to
do is to sort by 'artif_id' when user clicks 'id' column ('artif_id'
column should be invisible in the DataGrid) - see 'Sort' property of
DataView class.

Cheers,
Piotrek

[Saputra]

Hi there!

I tried declaring a new instance of a data adapter with an SQL statement
inside it to capture the table fields and the use of cast(substring(..))
statement and I get a system.oledbException error after filling the data
table into the new instance of data adapter.

In other words, the new instance of data adapter would execute fine if I
left out the cast(substring) statement in the ORDER BY clause, but when
I add it in, error is generated.

It seems that it is generating an error due to the mix match of SQL and
VB.NET into the SQL statement. What do you think?

*** Sent via Developersdex http://www.developersdex.com ***

[Piotr Szukalski]

Hi!

In other words, the new instance of data adapter would execute fine if I
left out the cast(substring) statement in the ORDER BY clause, but when
I add it in, error is generated.
Well... I've a simple project and created an OleDbDataAdapter and
connected to SQL 2000 Server, Sql statement was:

SELECT Id, Name FROM dbo.Login ORDER BY CAST(SUBSTRING(Name, 3, LEN(Name) - 2) AS varchar)

Note that 'Id' is my _real_ primary key! Well, it works for me...

Ok, now I ask questions:
1. what database you use
2. what is the stacktrace and message of the exception you 've mentioned?
It seems that it is generating an error due to the mix match of SQL and
VB.NET into the SQL statement. What do you think?

Well, there's no .NET in the query I gave you... just simple Transact-SQL.

Cheers,
Piotrek

[Saputra]

- Which database I use?
I use Microsoft.Jet.OleDB.4.0 to connect with Microsoft Access 2003.
I'm not using SQL Server. Does this make a difference with entering
Transact SQL in .NET?

This is the query I use in .Net is:

Dim dbAdapter as New OleDb.OledbAdapter("SELECT * FROM tblISSUE_LIST
ORDER BY
CAST(SUBSTRING(tblISSUE_LIST.[Issue_No],2,Len(tblISSUE_LIST.[Issue
No])-1)) AS INT);",
Me.oledbConnection1)
Me.IssueDataSet1
MyDataTable = Me.IssueDataSet1.Tables(0)
dbAdapter.Fill(MyDataTable)
dv = Me.DataSet1.DefaultViewManager.CreateDataView(myDa taTable)
..
..

[Piotr Szukalski]

Hi!

I use Microsoft.Jet.OleDB.4.0 to connect with Microsoft Access 2003.
Well, I don't use Access, so I can't check your query... Please try to
execute the query directly under Access.
I'm not using SQL Server. Does this make a difference with entering
Transact SQL in .NET?
No, it should not... but you never know with M$.
BTW: you don't have to buy SQL Server to use it: see MSDE - it's SQL
Server engine you can downlaod and use for _free_. There may be some
license restrictions (number of simultaneous connections to database) and
there's no administration tool (as far I know, you can download some free
solutions or 'administrate' the DB from Visual Studio).
This is the query I use in .Net is:

Dim dbAdapter as New OleDb.OledbAdapter("SELECT * FROM tblISSUE_LIST
ORDER BY
CAST(SUBSTRING(tblISSUE_LIST.[Issue_No],2,Len(tblISSUE_LIST.[Issue
No])-1)) AS INT);",
Me.oledbConnection1)
Me.IssueDataSet1
MyDataTable = Me.IssueDataSet1.Tables(0) dbAdapter.Fill(MyDataTable) dv
= Me.DataSet1.DefaultViewManager.CreateDataView(myDa taTable) . . .
You can try to give columns explicite, not 'select * ...'. It's just a
thought.
Error occurs when filling the data table to the data adapter. Error is:
IErrorInfo.GetDescription failed with E_FAIL(0*80004005).

Tricky one, MS SDK says nothing about this one, it seems like it's 'vendor
specific' - I guess it's because Access fails to undrestand the query.

Cheers,
Piotrek

[Saputra]

I just tried input the SQL query in MS Access 2003 and it does not like
CAST and SUBSTRING syntax. There you go. That will be the problem.
That's why it had generated the error previously. That is one
limitation of using MS Access, that is, you can't implicitly define
these type of syntax in the query. I am assuming that you can do this
is SQL Server.

As you mentioned earlier, you can get SQL Server engine and I may have
to examine getting that.
*** Sent via Developersdex http://www.developersdex.com ***