 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
.NET Forum / .NET Framework / General / August 2005Sorting a dataview Numerically
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.. 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 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. 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 [quoted text clipped - 7 lines] > 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 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? 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 - 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(myDataTable) . . . Error occurs when filling the data table to the data adapter. Error is: IErrorInfo.GetDescription failed with E_FAIL(0*80004005). 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: > [quoted text clipped - 6 lines] > MyDataTable = Me.IssueDataSet1.Tables(0) dbAdapter.Fill(MyDataTable) dv > = Me.DataSet1.DefaultViewManager.CreateDataView(myDataTable) . . . 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 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. Free MagazinesGet these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...
|
Reply to this Message
|
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.