custom sort for access table in datagrid

[TM]

I am using an access database in my vb.net application and it is tied to a
datagrid.

My problem is that the field I want to sort on is a text field, 5 characters
long, and it contains not only numbers but some fields are text.

When I sort the table in access, or use the "order by" sql statement, it
seems to want to put the numbers first, then the alpha after.

I realize this is probably the proper behavior, but is there any way I can
override this ?

The data is for a shopping list and here is a sample:
Field1=Isle
sample data:
1,2,3,4,PHRM,PROD,ENT

I would like to sort the data in this order:
ENT, PROD, PHRM, 1,2,3,...

Sorry for the confusion, this is bard to explain. Is there any way I can do
this ?
Thanks

 

[Miha Markic]

Hi TM,

You might add a (unbind) sort column to your datatable, put sort order there
(numbers perhaps) and sort by it.

 

[TM]

Interesting idea. Not sure how to go about that but I will look into it.
Thanks

 

[Miha Markic]

Hi TM,

It is fairly easy.
// First add a column to DataTable.
table.Columns.Add("MySort", typeof(int));
// then just go through all rows and put a number representing sort position
into
foreach (DataRow row in table.Rows)
row["MySort"] = *position*

For easier position calculation you might put copy the relevant data into an
ArrayList and do the sort there or some other way.

HTH,

 

[TM]

I am a little confused. I am not using a datatable, per se. I am opening
the access database from a dataadapter. Sample code is as follows:

objShoppingListDataSet.Clear()
objShoppingListDA.FillSchema(objShoppingListDataSet, SchemaType.Source,
"ShoppingList")

objShoppingListDA.Fill(objShoppingListDataSet, "ShoppingList")

grdShoppingList.DataSource = objShoppingListDataSet

grdShoppingList.DataMember = "ShoppingList"

I am fairly new to the .net thing, so sorry if this sounds like a stupid
question
--
Tony

Hi TM,

It is fairly easy.
// First add a column to DataTable.
table.Columns.Add("MySort", typeof(int));
// then just go through all rows and put a number representing sort position
into
foreach (DataRow row in table.Rows)
row["MySort"] = *position*

For easier position calculation you might put copy the relevant data into an
ArrayList and do the sort there or some other way.

HTH,
--
Miha Markic - RightHand .NET consulting & development
miha at rthand com

Interesting idea. Not sure how to go about that but I will look into it.
Thanks

tied

to

statement,

it I
can I
can

 

[Miha Markic]

Hi TM,

Acutally, with Fill method you are filling a table inside dataset (second
parameter is table name).
If you do:
Dim dt as DataTable = objShoppingListDataSet("ShoppingList")
you will have reference to that table.

Then do the sorting suff I've told you.
After that call dt .AcceptChanges() so the modified rows won't appear as
modified (they shouldn't just because of sorting).

HTH,

--
Miha Markic - RightHand .NET consulting & development
miha at rthand com

I am a little confused. I am not using a datatable, per se. I am opening
the access database from a dataadapter. Sample code is as follows:

objShoppingListDataSet.Clear()
objShoppingListDA.FillSchema(objShoppingListDataSet, SchemaType.Source,
"ShoppingList")

objShoppingListDA.Fill(objShoppingListDataSet, "ShoppingList")

grdShoppingList.DataSource = objShoppingListDataSet

grdShoppingList.DataMember = "ShoppingList"

I am fairly new to the .net thing, so sorry if this sounds like a stupid
question
--
Tony

Hi TM,

It is fairly easy.
// First add a column to DataTable.
table.Columns.Add("MySort", typeof(int));
// then just go through all rows and put a number representing sort position
into
foreach (DataRow row in table.Rows)
row["MySort"] = *position*

For easier position calculation you might put copy the relevant data

into

an
ArrayList and do the sort there or some other way.

HTH,
--
Miha Markic - RightHand .NET consulting & development
miha at rthand com

tied statement,

way

I

way

I

 

[TM]

Something is strange here. If I insert the line you mentioned, I get the
following error:
class system.data.dataset can not be indexed because it has no default
property.

It doesn't seem to matter where I insert that line, I still get that error

--
Tony

Hi TM,

Acutally, with Fill method you are filling a table inside dataset (second
parameter is table name).
If you do:
Dim dt as DataTable = objShoppingListDataSet("ShoppingList")
you will have reference to that table.

Then do the sorting suff I've told you.
After that call dt .AcceptChanges() so the modified rows won't appear as
modified (they shouldn't just because of sorting).

HTH,

--
Miha Markic - RightHand .NET consulting & development
miha at rthand com

I am a little confused. I am not using a datatable, per se. I am opening
the access database from a dataadapter. Sample code is as follows:

objShoppingListDataSet.Clear()
objShoppingListDA.FillSchema(objShoppingListDataSet, SchemaType.Source,
"ShoppingList")

objShoppingListDA.Fill(objShoppingListDataSet, "ShoppingList")

grdShoppingList.DataSource = objShoppingListDataSet

grdShoppingList.DataMember = "ShoppingList"

I am fairly new to the .net thing, so sorry if this sounds like a stupid
question
--
Tony

Hi TM,

It is fairly easy.
// First add a column to DataTable.
table.Columns.Add("MySort", typeof(int));
// then just go through all rows and put a number representing sort position
into
foreach (DataRow row in table.Rows)
row["MySort"] = *position*

For easier position calculation you might put copy the relevant data

into

an
ArrayList and do the sort there or some other way.

HTH,
--
Miha Markic - RightHand .NET consulting & development
miha at rthand com

Interesting idea. Not sure how to go about that but I will look

into
it.

Thanks

--
Tony



"Miha Markic" <miha at rthand com> wrote in message
Hi TM,

You might add a (unbind) sort column to your datatable, put sort order
there
(numbers perhaps) and sort by it.

--
Miha Markic - RightHand .NET consulting & software development
miha at rthand com

I am using an access database in my vb.net application and it is tied
to
a
datagrid.

My problem is that the field I want to sort on is a text field, 5
characters
long, and it contains not only numbers but some fields are text.

When I sort the table in access, or use the "order by" sql statement,
it
seems to want to put the numbers first, then the alpha after.

I realize this is probably the proper behavior, but is there any

way

I
can
override this ?

The data is for a shopping list and here is a sample:
Field1=Isle
sample data:
1,2,3,4,PHRM,PROD,ENT

I would like to sort the data in this order:
ENT, PROD, PHRM, 1,2,3,...

Sorry for the confusion, this is bard to explain. Is there any

way

I
can
do
this ?
Thanks

 

[Miha Markic]

Ah, sorry,
Dim dt as DataTable = objShoppingListDataSet.Tables("ShoppingList") should
be better

--
Miha Markic - RightHand .NET consulting & development
miha at rthand com

Something is strange here. If I insert the line you mentioned, I get the
following error:
class system.data.dataset can not be indexed because it has no default
property.

It doesn't seem to matter where I insert that line, I still get that error

--
Tony

Hi TM,

Acutally, with Fill method you are filling a table inside dataset (second
parameter is table name).
If you do:
Dim dt as DataTable = objShoppingListDataSet("ShoppingList")
you will have reference to that table.

Then do the sorting suff I've told you.
After that call dt .AcceptChanges() so the modified rows won't appear as
modified (they shouldn't just because of sorting).

HTH,

--
Miha Markic - RightHand .NET consulting & development
miha at rthand com

I am a little confused. I am not using a datatable, per se. I am opening
the access database from a dataadapter. Sample code is as follows:

objShoppingListDataSet.Clear()
objShoppingListDA.FillSchema(objShoppingListDataSet, SchemaType.Source,
"ShoppingList")

objShoppingListDA.Fill(objShoppingListDataSet, "ShoppingList")

grdShoppingList.DataSource = objShoppingListDataSet

grdShoppingList.DataMember = "ShoppingList"

I am fairly new to the .net thing, so sorry if this sounds like a stupid
question
--
Tony



"Miha Markic" <miha at rthand com> wrote in message
Hi TM,

It is fairly easy.
// First add a column to DataTable.
table.Columns.Add("MySort", typeof(int));
// then just go through all rows and put a number representing sort
position
into
foreach (DataRow row in table.Rows)
row["MySort"] = *position*

For easier position calculation you might put copy the relevant data into
an
ArrayList and do the sort there or some other way.

HTH,
--
Miha Markic - RightHand .NET consulting & development
miha at rthand com

Interesting idea. Not sure how to go about that but I will look into
it.
Thanks

--
Tony



"Miha Markic" <miha at rthand com> wrote in message
Hi TM,

You might add a (unbind) sort column to your datatable, put sort order
there
(numbers perhaps) and sort by it.

--
Miha Markic - RightHand .NET consulting & software development
miha at rthand com

I am using an access database in my vb.net application and it is
tied
to
a
datagrid.

My problem is that the field I want to sort on is a text

field,

5 any
way any
way

 

[TM]

Ok that works better. The only problems is that your sample code to add the
sort column was in C but I am working on VB.net.

Can you help ?
Thanks for all the assistance

--
Tony

Ah, sorry,
Dim dt as DataTable = objShoppingListDataSet.Tables("ShoppingList") should
be better

--
Miha Markic - RightHand .NET consulting & development
miha at rthand com

Something is strange here. If I insert the line you mentioned, I get the
following error:
class system.data.dataset can not be indexed because it has no default
property.

It doesn't seem to matter where I insert that line, I still get that error

--
Tony

Hi TM,

Acutally, with Fill method you are filling a table inside dataset (second
parameter is table name).
If you do:
Dim dt as DataTable = objShoppingListDataSet("ShoppingList")
you will have reference to that table.

Then do the sorting suff I've told you.
After that call dt .AcceptChanges() so the modified rows won't appear as
modified (they shouldn't just because of sorting).

HTH,

--
Miha Markic - RightHand .NET consulting & development
miha at rthand com

I am a little confused. I am not using a datatable, per se. I am opening
the access database from a dataadapter. Sample code is as follows:

objShoppingListDataSet.Clear()
objShoppingListDA.FillSchema(objShoppingListDataSet, SchemaType.Source,
"ShoppingList")

objShoppingListDA.Fill(objShoppingListDataSet, "ShoppingList")

grdShoppingList.DataSource = objShoppingListDataSet

grdShoppingList.DataMember = "ShoppingList"

I am fairly new to the .net thing, so sorry if this sounds like a stupid
question
--
Tony



"Miha Markic" <miha at rthand com> wrote in message
Hi TM,

It is fairly easy.
// First add a column to DataTable.
table.Columns.Add("MySort", typeof(int));
// then just go through all rows and put a number representing sort
position
into
foreach (DataRow row in table.Rows)
row["MySort"] = *position*

For easier position calculation you might put copy the relevant data
into
an
ArrayList and do the sort there or some other way.

HTH,
--
Miha Markic - RightHand .NET consulting & development
miha at rthand com

Interesting idea. Not sure how to go about that but I will look into
it.
Thanks

--
Tony



"Miha Markic" <miha at rthand com> wrote in message
Hi TM,

You might add a (unbind) sort column to your datatable, put sort
order
there
(numbers perhaps) and sort by it.

--
Miha Markic - RightHand .NET consulting & software development
miha at rthand com

I am using an access database in my vb.net application and

it

is field,

 

[TM]

I have some idea what is going on, and here is the sample code I created.
It doesn't create any errors, I don't know if it works though.

After I am done, how can I bind this to the datagrid on my form ? Also,
when changes are made to the datagrid, how can I write them back to the
database file ?
Dim dt As DataTable = objShoppingListDataSet.Tables("ShoppingList")
'Add a column to DataTable
dt.Columns.Add("MySort", System.Type.GetType("System.Int16"))
'go through all rows and put a number representing sort position
Dim i As Integer
For i = 1 To dt.Rows.Count
Select Case dt.Rows(i - 1).Item("Isle")
Case "ENT"
dt.Rows(i - 1).Item("MySort") = 1
Case "PROD"
dt.Rows(i - 1).Item("MySort") = 2
Case "PHRM"
dt.Rows(i - 1).Item("MySort") = 3
Case Else
'would like to check and make sure is numeric
then add 3 to it so that
'all numeric isles are at the end and sorted
properly
dt.Rows(i - 1).Item("MySort") = 4
End Select
Next
'accept changes to datatable
dt.AcceptChanges()


--
Tony

Ah, sorry,
Dim dt as DataTable = objShoppingListDataSet.Tables("ShoppingList") should
be better

--
Miha Markic - RightHand .NET consulting & development
miha at rthand com

Something is strange here. If I insert the line you mentioned, I get the
following error:
class system.data.dataset can not be indexed because it has no default
property.

It doesn't seem to matter where I insert that line, I still get that error

--
Tony

Hi TM,

Acutally, with Fill method you are filling a table inside dataset (second
parameter is table name).
If you do:
Dim dt as DataTable = objShoppingListDataSet("ShoppingList")
you will have reference to that table.

Then do the sorting suff I've told you.
After that call dt .AcceptChanges() so the modified rows won't appear as
modified (they shouldn't just because of sorting).

HTH,

--
Miha Markic - RightHand .NET consulting & development
miha at rthand com

I am a little confused. I am not using a datatable, per se. I am opening
the access database from a dataadapter. Sample code is as follows:

objShoppingListDataSet.Clear()
objShoppingListDA.FillSchema(objShoppingListDataSet, SchemaType.Source,
"ShoppingList")

objShoppingListDA.Fill(objShoppingListDataSet, "ShoppingList")

grdShoppingList.DataSource = objShoppingListDataSet

grdShoppingList.DataMember = "ShoppingList"

I am fairly new to the .net thing, so sorry if this sounds like a stupid
question
--
Tony



"Miha Markic" <miha at rthand com> wrote in message
Hi TM,

It is fairly easy.
// First add a column to DataTable.
table.Columns.Add("MySort", typeof(int));
// then just go through all rows and put a number representing sort
position
into
foreach (DataRow row in table.Rows)
row["MySort"] = *position*

For easier position calculation you might put copy the relevant data
into
an
ArrayList and do the sort there or some other way.

HTH,
--
Miha Markic - RightHand .NET consulting & development
miha at rthand com

Interesting idea. Not sure how to go about that but I will look into
it.
Thanks

--
Tony



"Miha Markic" <miha at rthand com> wrote in message
Hi TM,

You might add a (unbind) sort column to your datatable, put sort
order
there
(numbers perhaps) and sort by it.

--
Miha Markic - RightHand .NET consulting & software development
miha at rthand com

I am using an access database in my vb.net application and

it

is field,

 

[Miha Markic]

Hi TM,

I have some idea what is going on, and here is the sample code I created.
It doesn't create any errors, I don't know if it works though.

Seems ok.

After I am done, how can I bind this to the datagrid on my form ?

Set DataSource = dt


Also,

when changes are made to the datagrid, how can I write them back to the
database file ?

You'll have to call adapter.Update method.
Read the
Accessing Data with ADO.NET
..net help chapter.

 

[TM]

I will give that a shot. Thanks for all the help

 

[TM]

I went through all that and it doesn't seem to be doing any sorting. I
double-checked the code, but am not sure what is going on.

Do I need to do anything after I assign the sortID field to have it resort
the table by this field ?

 

[Miha Markic]

Hi Tony,

To be able to use sort you will have to use DataView.
For example, you can use table.DefaultView.Sort property (example: =
"columnnam ASC").
For more info check out
Sorting and Filtering Data Using a DataView
..net help topic.

HTH,

 

[TM]

I tried the steps you mentioned but it doesn't seem to sort anything. Is
there anything extra that I need to do in order for it to actually perform
the sort ?

I am assigning the sortorder but it is not doing the actual sorting

 

[Miha Markic]

Hi Tony,

Try assigning sort in DataView's constructor.
Other than that - pay attention that DataView doesn't sort DataTable it just
represents the data from DataTable is sorted and/or filtered mode.

 

[TM]

I am confused. Can you please explain ?

 

[Miha Markic]

DataView doesn't sort data in datatable - it doesn't even touch the
datatable.
The sort is done inside DataView.
So, for example, if you create a sorted DataView over DataTable, the
DataTable will have its row order unsorted as before.
While the DataView will have rows sorted.
IOW only if you bind to DataView you'll see rows sorted.