How to keep a DataGrid in sync with a DataSet when sorting

[prichards14]

I am working on a project developed by another programmer. Currently,
there is no support for DataGrid sorting. If I set AllowSorting to
True, I can sort by clicking the column headers. However, the sort
order of the data in the underlying DataSet is not altered.

As an example, I have a DataGrid with one column containg data rows B,
C, A. If I click the column header, the data is sorted A, B, C. If I
select the second row and click Edit, the Edit form displays the data
for C, not B.

I have been reading articles and postings for hours on some
workarounds, but I have not found a solution. I want to bind the data
to the grid in such a way that if I sort the grid by clicking a column
header, the underlying data is sorted as well.

One post I read said that using a DataSet with a DataGrid creates a
default DataView that is not bound to the grid, as far as sorting is
concerned. The poster said to explicitly create a DataView for the
DataSet and bind it to the DataGrid. This is what I want to do, but I
have no idea how.

A simple code example would help me the most, or a link to web site
that explains this. I am very new to .Net and I am really struggling
with this.

Thanks!
-Pat

 

[Travis Murray]

Hi Pat,

Welcome to the world of .Net.

There are several reasons this could be happening, and several ways that you
can fix it. The most likely reason for this behavior centers around how you
bind the data to your DataGrid. For simplicity, let's say you have this SQL
statement as the SelectCommand.CommandText property of your DataAdapter.

Select * from Customers

In your Page_Load event handler, you use the Fill method of your DataAdapter
to populate your DataSet, and it puts all of your customers in, unsorted.
Then, in your DataGrid_SortCommand event handler, you append an Order By
statement to this SQL Statement before you call the Fill method. Your data
is now sorted, and displays correctly in your DataGrid.
Then, in your DataGrid_EditCommand event handler, you use the Fill method to
populate the DataSet before binding it to the grid. However, it reverts
back to the original SQL statement - without the Order By clause.

This happens because by default, web applications are stateless. Every time
your page does a round trip to the server, new DataSets and DataAdapters are
being created. Unless you use some form of state management, everything
reverts back to the default values.

One way to fix this is to store you SortExpression in a Session variable.
Then, you can append it to the CommandText before every fill of the DataSet.
Another way would be to store the DataSet itself in a Session variable.

Using a DataView can make sorting a bit easier, but it would not alleviate
the problem that you are having.

If you post the code in your Page_Load, DataGrid_SortCommand, and
DataGrid_EditCommand event handlers, we can probably pinpoint the problem
pretty quickly.

--
Travis Murray
MCSD, MCT
Artiem Consulting, Inc.
http://www.artiem.com
Learn ASP.Net In Jamaica in 2005!
Visit http://www.artiem.com/jamaica.aspx for more details.

 

[prichards14]

Somehow I got a second thread with the same topic going. Anyway, I am
posting my solution here as well.

Here is the line of code that has been giving me all of the trouble:

Me.LabelDSIndex.Text =
myDS.Tables("TableInfo").Rows(BindingContext(myDS,
"TableInfo").Position).Item("cconsumernodid")

If I sort the datagrid by clicking a column heading, the key value
returned is what was at that position before the grid was sorted.

This is my new new code which is working:

Dim cm As CurrencyManager =
CType(Me.BindingContext(DataGridNOD.DataSource,
DataGridNOD.DataMember), CurrencyManager)
Dim dv As DataView = CType(cm.List, DataView)
Dim dr As DataRow
dr = dv.Item(cm.Position).Row
Me.LabelDSIndex.Text = dr(0).ToString

Thank you for your help!

-Pat