translate current cell (row, column) in sorted datagrid to corresponding row/col in datatable

[JohnR]

I have a datatable as the datasource to a datagrid. The datagrid has a
datagridtablestyle defined. I use the datagridtablestyle to change the
order of the columns (so they can be different than the column order of the
datatable). I also allow the user to click on a column header to sort the
datagrid by that column.

I need to identify the row and column in the datatable when the user clicks
on a cell in the datagrid.

Using the datagrid.HitTestInfo I can easily find out the row and column of
the datagrid (ie: hti.row and hti.column). Now I need to find the
corresponding row and column for the cell the user clicked on in the actual
datatable . I need to do this in the MOUSEDOWN event.

As an initial thought, I used a Dim bm as BindingManagerBase like this:

bm = Me.dg.BindingContext(Me.dg.DataSource)

However, it seems that the datarow accessed by ctype(bm.current,
datarowview) points to the row just before the user clicked the mouse on
the new cell. The mousedown event seems to take control before the
bm.current row is updated.

Anyway, all this means that I am now totally confused. Clearly there must
be a procedure to translate the datagrid row/column to the datatable
row/column because you can rearrange columns and sort them to your hearts
content, and when you then change the value of a cell in the datagrid, it
updates the proper row/column in the datatable. SO HOW DOES IT DO IT???

Is there some sort of function or formula that will return the datatable
row/column given the datagrid row/column??? It would be great to have
something like: RowInDatatable = ConvertToDataTableRow(RowInDataGrid).

I would appreciate any guidance on this.
Thanks, John

 

[Cor Ligthert]

John,

Every datatable has a defaultview. (A dataview). In that is the Sort
property. When there is no extra dataview used to show the datatable, than
that defaultview is used.

Therefore you can than see in the sort property which column and in what
order it is sorted.

The datatable it self is not sorted by the way.

I hope this helps,

Cor

 

[JohnR]

Hi Cor,

So how would that help me to translate datagrid cell coordinates to the
datatable coordinates? Remember, the columns in the datagrid may be
switched also (via the datagridtablestyle).

John

 

[Cor Ligthert]

John,

Just have a look at the table.defaultview.sort using debuging and quick
view, than you see exactly how it happens. Assuming that that is the
datasource from your datagrid.

http://msdn.microsoft.com/library/d...tml/frlrfsystemdatadataviewclasssorttopic.asp

I hope this helps,

Cor

 

[JohnR]

Hi Cor,

I looked at the defaultview.sort in my datatable. I see that the value
for the sort is "" if I have not yet sorted by any column, and once I click
on a column to sort, the defaultview.sort becomes the columnname that I
sorted on.

Sorry if I'm a little slow on this, but how does that help me with my
problem? I just don't see the connection. Even if I know what column is the
sort column, how do I use that information to map a datagrid cell to its
corresponding datatable row/col??? So the user clicks in the datagrid on
column 3 and row 4, and I, by examining the datatable I know that it
corresponds to column 1, row 2 in the datatable. So how do I make that
translation programmatically?

This seems like such a simple thing to want to do, and after hours of
searching for a solution I found plenty of users asking the same question.
All of the suggested solutions seemed very indirect and may not work in
every situation (like using the bm.current method I referred to in the orig
post).

I can't help but believe there has to be an easier, more direct way to do
this mapping... Any other help graciously accepted.

John

 

[Cor Ligthert]

John,

You were asking in my opinon two questions, how is the sort order done and
how you can get from your datagrid to your cel in the datatable. The first I
could answer the second is for me a kind of rediculous, it is the same for
me as asking how you can use a plane to sail over the oceans. A datagrid is
not build for that, it is not a stand alone grid.

You cannot translate the current row/cell in a datagrid to a current/cell in
a datatable.

A datagrid is not a classic grid. It is a table of rows which has shows
items. Using the column mapping the right cell is found. However changes in
data goes row by row after that the user has commited by a row change that
he has done that row. (Or a button which has an endcurrentedit behind it).

In other words a datagrid uses rows and items from a datasource, which is
placed using the mapping on the screen.

You can not see in the datasource what the UI is doing. Like that you cannot
see in a datagrid what the datasource is doing. Until that both have done
what they have to do.

You can however see what is the currentrow in the datagrid. For that is the
currencymanager.

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

You told that you have seen a lot of questions from people who wants to use
a Grid in dotNet on the classic way. Mostly that people are after a while
glad that there is now a better datagrid.

However just my thought,

Cor

 

[JohnR]

Hi Cor,

Well, actually, I have a very good reason (I believe) to be able to track
the datagrid row/col back to the datatable and here it is...

I actually have two datatables that mirror each other. Let's say they both
have columns of "FEATURE" and "VALUE". In the first datatable the VALUE
column is of type "string". In the second datatable the VALUE is of type
"object". I use the first datatable as a datasource for the datagrid, and
the grid displays strings in each of the columns. Some sample rows might be
something like:

FEATURE VALUE
Security Yes
Combinations <click for more info>
Last Access 5/4/2005

Notice the <click for more info> text in the 2nd row, 2nd col. The
corresponding row/col in the 2nd datatable (the one that stores the VALUE
column as type 'object') is an instance of a custom class.

Now, here's the fun part. When the user clicks on the cell that has the
string <click for more info> I then go to the corresponding row/col in the
2nd datatable and get the object that has the instance of the custom class
and then process it.

When the datagrid is not sorted and the columns and rows correspond exactly
to the datatable this process works perfectly.

BUT, if the datagrid is sorted and/or column are rearranged, then I need the
row/col of the cell that maps back into the datatable so that I can fetch
the corresponding OBJECT in the 2nd datatable. This is why I need to know
the mapping of grid row/col to datatable row/col.

So, I hope you can see that the requirement is actually not ridiculous...
there was some serious thought behind it.

Regards,
John

 

[Ken Tucker [MVP]]

Hi,

Use the datatables default view to return right datarow. Quick
example.


Dim ds As New DataSet

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load

Dim strConn As String

Dim strSQL As String

Dim da As OleDbDataAdapter

Dim conn As OleDbConnection

strConn = "Provider = Microsoft.Jet.OLEDB.4.0;"

strConn &= "Data Source = Northwind.mdb;"

conn = New OleDbConnection(strConn)

da = New OleDbDataAdapter("Select * From Categories", conn)

da.Fill(ds, "Categories")

DataGrid1.DataSource = ds.Tables("Categories")

End Sub



Private Sub Form1_DoubleClick(ByVal sender As Object, ByVal e As
System.EventArgs) Handles MyBase.DoubleClick

Dim cm As CurrencyManager = CType(Me.BindingContext(DataGrid1.DataSource),
CurrencyManager)

Dim drv As DataRowView

drv = ds.Tables("Categories").DefaultView.Item(cm.Position)

MessageBox.Show(drv.Item("CategoryName").ToString)

End Sub



Ken
--------------------
I have a datatable as the datasource to a datagrid. The datagrid has a
datagridtablestyle defined. I use the datagridtablestyle to change the
order of the columns (so they can be different than the column order of the
datatable). I also allow the user to click on a column header to sort the
datagrid by that column.

I need to identify the row and column in the datatable when the user clicks
on a cell in the datagrid.

Using the datagrid.HitTestInfo I can easily find out the row and column of
the datagrid (ie: hti.row and hti.column). Now I need to find the
corresponding row and column for the cell the user clicked on in the actual
datatable . I need to do this in the MOUSEDOWN event.

As an initial thought, I used a Dim bm as BindingManagerBase like this:

bm = Me.dg.BindingContext(Me.dg.DataSource)

However, it seems that the datarow accessed by ctype(bm.current,
datarowview) points to the row just before the user clicked the mouse on
the new cell. The mousedown event seems to take control before the
bm.current row is updated.

Anyway, all this means that I am now totally confused. Clearly there must
be a procedure to translate the datagrid row/column to the datatable
row/column because you can rearrange columns and sort them to your hearts
content, and when you then change the value of a cell in the datagrid, it
updates the proper row/column in the datatable. SO HOW DOES IT DO IT???

Is there some sort of function or formula that will return the datatable
row/column given the datagrid row/column??? It would be great to have
something like: RowInDatatable = ConvertToDataTableRow(RowInDataGrid).

I would appreciate any guidance on this.
Thanks, John

 

[Cor Ligthert]

JohnR,

Maybe can this helps you in future, I do really know nothing from it.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvsdev05/html/vs05a9.asp


Cor

 

[JohnR]

OK, here's the solution I implemented. Remember, the problem was that in a
datagrid with a datatable as a datasource, I needed to find the row/col in
the datatable given the row/col in the datagrid. This needed to work even
if the grid columns were rearranged, and if the columns were sorted.



The first problem was the easiest. how to find the column. This is how I
did it:



Public Function GetDataTableColNum(ByVal GridCol As Integer, ByVal dg As
DataGrid, ByVal dt As DataTable) As Integer



If dg.TableStyles.Count = 0 Then

Return GridCol

Else

Return
dt.Columns.IndexOf(dg.TableStyles.Item(0).GridColumnStyles.Item(GridCol).MappingName)

End If

End Function



This assumes that the datagrid has either 0 or 1 associated tablestyle.



The problem of a sorted column was a little more complicated. Using a
DataView (either a user defined one, or the DefaultView) you can return a
DATAROW object from the datatable (not the row number, but the actual row)
this way:



Public Function GetDataTableRow(ByVal GridRow As Integer, ByVal dg As
DataGrid, ByVal dt As DataTable, Optional ByVal dv As DataView = Nothing) As
DataRow

Dim drv As DataRowView

If dv Is Nothing Then

drv = dt.DefaultView(GridRow)

Else

drv = dv(GridRow)

End If

Return drv.Row



End Function



For most users this would suffice. However, since I was generating 2
datatables at runtime I actually needed a row number so I could fetch info
from the datatable that was not currently linked to the grid. The data in
the 2 datatables had 'related' information for each row/col cell. So row 1,
col 1 in datatable 1 was related to row 1, col1 in datatable 2.



Anyway, since the ROW object has no reference to the rownumber, I simply
added a "rownumber" column to the datatable that was linked to the grid. As
I built the datatable, I incremented the rownumber for each row.



Therefore, when I used the above code to retrieve the DATAROW object from
the datatable, I simply obtained the actual row number from the rownumber
column. I then used that number, plus the column number obtained
previously, to get the proper row/col in the second datatable.



Thanks to all that offered advice.



John

 

[JohnR]

Hi Cor,

Thanks for the link... The new grid looks great. Can't wait for the
..NET 2005 release.

John