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
datagridtablest yle defined. I use the datagridtablest yle 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.HitTes tInfo 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 BindingManagerB ase like this:

bm = Me.dg.BindingCo ntext(Me.dg.Dat aSource)

However, it seems that the datarow accessed by ctype(bm.curren t,
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 = ConvertToDataTa bleRow(RowInDat aGrid).

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 datagridtablest yle).

John

"Cor Ligthert" <no************ @planet.nl> wrote in message
news:%2******** ********@TK2MSF TNGP15.phx.gbl. ..

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

[Cor Ligthert]

John,

Just have a look at the table.defaultvi ew.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/de...ssorttopic.asp

I hope this helps,

Cor

[JohnR]

Hi Cor,

I looked at the defaultview.sor t 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.sor t 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 programmaticall y?

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" <no************ @planet.nl> wrote in message
news:u3******** ******@TK2MSFTN GP10.phx.gbl...

John,

Just have a look at the table.defaultvi ew.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/de...ssorttopic.asp

I hope this helps,

Cor

[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/de...classtopic.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



"Cor Ligthert" <no************ @planet.nl> wrote in message
news:%2******** *******@TK2MSFT NGP15.phx.gbl.. .

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/de...classtopic.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

[Ken Tucker [MVP]]

Hi,

Use the datatables default view to return right datarow. Quick
example.
Dim ds As New DataSet

Private Sub Form1_Load(ByVa l sender As System.Object, ByVal e As
System.EventArg s) Handles MyBase.Load

Dim strConn As String

Dim strSQL As String

Dim da As OleDbDataAdapte r

Dim conn As OleDbConnection

strConn = "Provider = Microsoft.Jet.O LEDB.4.0;"

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

conn = New OleDbConnection (strConn)

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

da.Fill(ds, "Categories ")

DataGrid1.DataS ource = ds.Tables("Cate gories")

End Sub

Private Sub Form1_DoubleCli ck(ByVal sender As Object, ByVal e As
System.EventArg s) Handles MyBase.DoubleCl ick

Dim cm As CurrencyManager = CType(Me.Bindin gContext(DataGr id1.DataSource) ,
CurrencyManager )

Dim drv As DataRowView

drv = ds.Tables("Cate gories").Defaul tView.Item(cm.P osition)

MessageBox.Show (drv.Item("Cate goryName").ToSt ring)

End Sub

Ken
--------------------
"JohnR" <Jo******@hotma il.com> wrote in message
news:WnTke.391$ Qd3.256@trndny0 1...
I have a datatable as the datasource to a datagrid. The datagrid has a
datagridtablest yle defined. I use the datagridtablest yle 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.HitTes tInfo 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 BindingManagerB ase like this:

bm = Me.dg.BindingCo ntext(Me.dg.Dat aSource)

However, it seems that the datarow accessed by ctype(bm.curren t,
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 = ConvertToDataTa bleRow(RowInDat aGrid).

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/de...tml/vs05a9.asp
Cor



"JohnR" <Jo******@hotma il.com> schreef in bericht
news:RH4le.346$ Fb.106@trndny07 ...

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



"Cor Ligthert" <no************ @planet.nl> wrote in message
news:%2******** *******@TK2MSFT NGP15.phx.gbl.. .

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/de...classtopic.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]

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 GetDataTableCol Num(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.Inde xOf(dg.TableSty les.Item(0).Gri dColumnStyles.I tem(GridCol).Ma ppingName)

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