Dataviews and constrained datasources?

[Jerry]

Hi,

I have a couple of datagrids that are based on dataviews.
If I filter dataview1 I would like to automatically filter
dataview2. I posted something about this before but I
think I didn't specifically focus about that I was using
dataviews and just datagrids. Ken Tucker was nice enough
to share an example using datagrids and
relations/constraints from code (thanks ken). I was able
to duplicate this example using components and physical
datasets/tables, physical connection, etc. I kind of have
the idea now about using this like

datagrid2.SetDatabinding(dataset1, "tbl1, tbl1tbl2")

which restricts the 2nd datagrid (based on tbl2) from a
selection from the first datagrid (based on tbl1). I
guess in the dataviews you use Rowfilter to restric rows.
So if I filter dataview1 on some field, I get x number of
rows. This gives me some unique row IDS. I then want to
filter dataview2 based on this list of ID's. This is not
the problem:

dataview2.RowFilter = "ID In (" & strCriteria & ")"

where strCriteria contains the list of IDs which I get
from a loop from the rows in dataview1. It just seems a
little kludgy. The question is if I can restrict the
datatable of dataview2 at the source rather than using
RowFilter. Can this be done the same way as a datagrid
binding to the relation? Is the dataview limited to the
RowFilter or is there a way to constrain the dataview
datasource at the source table level?

Thanks,
Jerry

 

[Cor Ligthert]

Hi Jerry,

It is funny, I was checking something on a question from Bill and now I see
I can maybe use that message 3 times in questions in this newsgroup. I give
you a part of the text what seems for me intresting for you.

I do not know if it answers your question, however I see a lot of
connections to it. (All in a real environment needed Try's are deleted)

\\\
Dim Sql As String = "SELECT * from A, B Where " & _
"A.n = B.n AND A.n = 10"
Dim Conn As New OleDbConnection(connString)
Dim da As New OleDbDataAdapter(Sql, Conn)
da.Fill(ds, "A")
da.Fill(ds, "B")
Conn.Close()
'In this sample the datarelation is in my opinion not needed
'However without that A.n = 10 than it is needed
'------------------------------
Dim drlA As New DataRelation _
("AA", ds.Tables("A").Columns("A.n"), _
ds.Tables("B").Columns("B.n"))
ds.Relations.Add(drlA)
Dim dv As New DataView(ds.Tables("A"))
DataGrid1.DataSource = dv
DataGrid1.Expand(-1)
////
'Without datarelation just change dv in ds

See also this to show why the Where and not the Join

http://support.microsoft.com/default.aspx?scid=kb;en-us;318646

I hope this helps?

Cor

 

[Jerry]

Hi Cor,

Maybe it's a problem for me, but I am using components (I
should probably starting doing it in code). Maybe my
approach of restricting records using the
dataview.RowFilter method isn't so bad. But one more
question if I may:

So I have these 2 tables in my dataset. If I were to
declare a dataview in code

Dim dv2 As New DataView(ds.Tables("B"))

Could I do something like this:

Dim dv As New DataView(ds.Tables("Select t1.* From B t1
Join A t2 On t1.ID = t2.ID"))

or something like that?

In the meantime, one other approach was to use a currency
manager object on the first dataview and do this:

Dim drView As DataRowView
drView = DirectCast(curMgr.Current, DataRowView)
dv2.RowFilter = "Id = " & drView("Id").ToString()

This seems less kludgy than looping for ID's but it also
only returns 1 ID. My actual goal is to steer away from
kludgy .

Thanks,
Jerry

 

[Cor Ligthert]

Hi Jerry,

Dim dv As New DataView(ds.Tables("Select t1.* From B t1
Join A t2 On t1.ID = t2.ID"))

No, there is a table.select which does something almost the same as the
rowfilter and people think often that it is the same as an SQL select. There
is also a table.compute.
(I find the rowfilter for the select much nicer and it is faster)

In the meantime, one other approach was to use a currency
manager object on the first dataview and do this:

Dim drView As DataRowView
drView = DirectCast(curMgr.Current, DataRowView)
dv2.RowFilter = "Id = " & drView("Id").ToString()

This seems less kludgy than looping for ID's but it also
only returns 1 ID. My actual goal is to steer away from
kludgy .

Some find it, I do not, when you put this completly in a loop it is very
clear and it can be a very small loop. Do not think that one compound
instruction is always faster. (Do not forget to set the datarow.sort before
that makes it very fast).

For a loop is not so much needed.
The spending time in your system is mostly the drawing on screen.

Cor