DataRelation vs DataView.RowFilter property

[Rich]

Hello,

I am pulling master data from one master table and detail data from two
detail tables. The rows from the master data are displayed in textboxes on
my form which are bound to the data with databinding. The data from the 2
detail tables are displayed in 2 datagridviews. I filter each datagridview
with a Dataview object using the dataview.Rowfilter property. This is sort
of working except that I am getting an except on one of my detail tables
(used to be no exceptions until I converted the detail tables into views on
the sql server - to simplify select command sql). So I was reading up on the
DataRelations object.

Before I knock myself out changing all my client code, can I achieve the
same row filtering functionality by using DataRelations instead of
Dataview.Rowfilter for the datagridviews? According to the reading, I
interpreted the reading to suggest that DataRelations are more efficient -
but the only specified that it was more efficient than using a join statement
in the select command. Any suggestions appreciated on if I should use a
DataRelation vs Dataview.Rowfilter.

Thanks,
Rich

[Rich]

I decided to go ahead an experiment with DataRelations. Anyway, I am not
getting any row filtering for the datagridviews. However, I do get rowfilter
when I use the Dataview.Rowfilter property. So, does anyone have an
understandable way to use DataRelations?

"Rich" wrote:

Hello,

I am pulling master data from one master table and detail data from two
detail tables. The rows from the master data are displayed in textboxes on
my form which are bound to the data with databinding. The data from the 2
detail tables are displayed in 2 datagridviews. I filter each datagridview
with a Dataview object using the dataview.Rowfilter property. This is sort
of working except that I am getting an except on one of my detail tables
(used to be no exceptions until I converted the detail tables into views on
the sql server - to simplify select command sql). So I was reading up on the
DataRelations object.

Before I knock myself out changing all my client code, can I achieve the
same row filtering functionality by using DataRelations instead of
Dataview.Rowfilter for the datagridviews? According to the reading, I
interpreted the reading to suggest that DataRelations are more efficient -
but the only specified that it was more efficient than using a join statement
in the select command. Any suggestions appreciated on if I should use a
DataRelation vs Dataview.Rowfilter.

Thanks,
Rich

[Cor Ligthert [MVP]]

Rich,

Are you sure that you are not able to read very few data by using the Where
clause in your selects.

I mean,
One table with one datarow for your mainrow
Two tables with more datarows for your datagrids,

In my opinion is that the most easy to handle.

(Maybe you need beside that a listbox or combobox to select the mainrow that
you want, which needs an extra table). Be aware that normally the user takes
a natural short pause if he selects a main part of data.

The join will not help you, a joined table is using the normal methods for
that not updatable

A problem with the datarelations (at least in version 1.x) is AFAIK that
they are not simple bindable.

Just my idea,

Cor
"Rich" <Ri**@discussions.microsoft.com> schreef in bericht
news:11**********************************@microsof t.com...

Hello,

I am pulling master data from one master table and detail data from two
detail tables. The rows from the master data are displayed in textboxes
on
my form which are bound to the data with databinding. The data from the 2
detail tables are displayed in 2 datagridviews. I filter each
datagridview
with a Dataview object using the dataview.Rowfilter property. This is
sort
of working except that I am getting an except on one of my detail tables
(used to be no exceptions until I converted the detail tables into views
on
the sql server - to simplify select command sql). So I was reading up on
the
DataRelations object.

Before I knock myself out changing all my client code, can I achieve the
same row filtering functionality by using DataRelations instead of
Dataview.Rowfilter for the datagridviews? According to the reading, I
interpreted the reading to suggest that DataRelations are more efficient -
but the only specified that it was more efficient than using a join
statement
in the select command. Any suggestions appreciated on if I should use a
DataRelation vs Dataview.Rowfilter.

Thanks,
Rich

[Cor Ligthert [MVP]]

Rich,

I remember me that the databinding I am talking about is with simple
controls in the reverse order as you want.

Here is almost exactly your problem although it goes 3 instead as you need 2
deep.

http://www.vb-tips.com/dbpages.aspx?IA=DG

I hope this helps,

Cor
"Rich" <Ri**@discussions.microsoft.com> schreef in bericht
news:6A**********************************@microsof t.com...

I decided to go ahead an experiment with DataRelations. Anyway, I am not
getting any row filtering for the datagridviews. However, I do get
rowfilter
when I use the Dataview.Rowfilter property. So, does anyone have an
understandable way to use DataRelations?

"Rich" wrote:

Hello,

I am pulling master data from one master table and detail data from two
detail tables. The rows from the master data are displayed in textboxes
on
my form which are bound to the data with databinding. The data from the
2
detail tables are displayed in 2 datagridviews. I filter each
datagridview
with a Dataview object using the dataview.Rowfilter property. This is
sort
of working except that I am getting an except on one of my detail tables
(used to be no exceptions until I converted the detail tables into views
on
the sql server - to simplify select command sql). So I was reading up on
the
DataRelations object.

Before I knock myself out changing all my client code, can I achieve the
same row filtering functionality by using DataRelations instead of
Dataview.Rowfilter for the datagridviews? According to the reading, I
interpreted the reading to suggest that DataRelations are more
efficient -
but the only specified that it was more efficient than using a join
statement
in the select command. Any suggestions appreciated on if I should use a
DataRelation vs Dataview.Rowfilter.

Thanks,
Rich

[Rich]

Hi Cor,

Thank you for your reply to my question. I went to your website. I read
your article on Datagridview and datarelations. It looks like a great
article. I tried the example, but I was not able to get it to work. Here is
what I tried (using VB2005):

....

'--note: I am only using 1 dataAdapter to fill my dataset
....
da.Fill(ds, "maintbl")
da.Fill(ds, "detail1")
da.Fill(ds, "detail2")

ds.Relations.Add("relDetail1", ds.tables("maintbl").columns("ID1"),
ds.Tables("detail1").columns("ID1"))
ds.Relations.Add("relDetail2", ds.Tables("maintbl").columns("ID2"),
ds.Tables("detail2").columns("ID2"))

'--note: maintable data is displayed in textboxes on the form. I iterate
through
'--maintbl and display 1 row of data at a time. I want to display the
related rows
'--in the datagridviews

datagridview1.Datasource = ds
datagridview2.datasource = ds

datagridview1.Datamember = "maintbl.relDetail1"
datagridview2.Datamember = "maintbl.relDetail2"

When I bring up the form, I see the data from maintbl in the textboxes on
the form, but there is no data in either of the datagridviews. May I request
if you could see/explain what I am doing incorrectly?

Thanks,
Rich

"Cor Ligthert [MVP]" wrote:

Rich,

I remember me that the databinding I am talking about is with simple
controls in the reverse order as you want.

Here is almost exactly your problem although it goes 3 instead as you need 2
deep.

http://www.vb-tips.com/dbpages.aspx?IA=DG

I hope this helps,

Cor
"Rich" <Ri**@discussions.microsoft.com> schreef in bericht
news:6A**********************************@microsof t.com...

I decided to go ahead an experiment with DataRelations. Anyway, I am not
getting any row filtering for the datagridviews. However, I do get
rowfilter
when I use the Dataview.Rowfilter property. So, does anyone have an
understandable way to use DataRelations?

"Rich" wrote:

Hello,

I am pulling master data from one master table and detail data from two
detail tables. The rows from the master data are displayed in textboxes
on
my form which are bound to the data with databinding. The data from the
2
detail tables are displayed in 2 datagridviews. I filter each
datagridview
with a Dataview object using the dataview.Rowfilter property. This is
sort
of working except that I am getting an except on one of my detail tables
(used to be no exceptions until I converted the detail tables into views
on
the sql server - to simplify select command sql). So I was reading up on
the
DataRelations object.

Before I knock myself out changing all my client code, can I achieve the
same row filtering functionality by using DataRelations instead of
Dataview.Rowfilter for the datagridviews? According to the reading, I
interpreted the reading to suggest that DataRelations are more
efficient -
but the only specified that it was more efficient than using a join
statement
in the select command. Any suggestions appreciated on if I should use a
DataRelation vs Dataview.Rowfilter.

Thanks,
Rich

[Rich]

Hello again Cor,

I tried your example from your webside exactly as you had it. Your example
worked perfectly against the Northwind database. When I click on a row in
Datagridview1 the corresponding rows show up in the other 2 datagrids. I was
just not able to make it work for my situation. I will have to keep studying
your example.

Rich

"Cor Ligthert [MVP]" wrote:

Rich,

I remember me that the databinding I am talking about is with simple
controls in the reverse order as you want.

Here is almost exactly your problem although it goes 3 instead as you need 2
deep.

http://www.vb-tips.com/dbpages.aspx?IA=DG

I hope this helps,

Cor
"Rich" <Ri**@discussions.microsoft.com> schreef in bericht
news:6A**********************************@microsof t.com...

I decided to go ahead an experiment with DataRelations. Anyway, I am not
getting any row filtering for the datagridviews. However, I do get
rowfilter
when I use the Dataview.Rowfilter property. So, does anyone have an
understandable way to use DataRelations?

"Rich" wrote:

Hello,

I am pulling master data from one master table and detail data from two
detail tables. The rows from the master data are displayed in textboxes
on
my form which are bound to the data with databinding. The data from the
2
detail tables are displayed in 2 datagridviews. I filter each
datagridview
with a Dataview object using the dataview.Rowfilter property. This is
sort
of working except that I am getting an except on one of my detail tables
(used to be no exceptions until I converted the detail tables into views
on
the sql server - to simplify select command sql). So I was reading up on
the
DataRelations object.

Before I knock myself out changing all my client code, can I achieve the
same row filtering functionality by using DataRelations instead of
Dataview.Rowfilter for the datagridviews? According to the reading, I
interpreted the reading to suggest that DataRelations are more
efficient -
but the only specified that it was more efficient than using a join
statement
in the select command. Any suggestions appreciated on if I should use a
DataRelation vs Dataview.Rowfilter.

Thanks,
Rich

[Cor Ligthert [MVP]]

Rich,

You are using 1 dataadapter, however between the fills I see no change of
the SelectCommand, *I* don't not know a method performing this. You need at
least three daatatables (or with one, seperate SelectCommands for that) and
not a joined resultset.

I don't know if you use a command with wich you get 3 times 3 tables, but
that for probably understandable reasons I never tried.

Would you not first try it with 3 dataadapters, which consumes nothing
extra.

Cor

"Rich" <Ri**@discussions.microsoft.com> schreef in bericht
news:34**********************************@microsof t.com...

Hi Cor,

Thank you for your reply to my question. I went to your website. I read
your article on Datagridview and datarelations. It looks like a great
article. I tried the example, but I was not able to get it to work. Here
is
what I tried (using VB2005):

...

'--note: I am only using 1 dataAdapter to fill my dataset
...
da.Fill(ds, "maintbl")
da.Fill(ds, "detail1")
da.Fill(ds, "detail2")

ds.Relations.Add("relDetail1", ds.tables("maintbl").columns("ID1"),
ds.Tables("detail1").columns("ID1"))
ds.Relations.Add("relDetail2", ds.Tables("maintbl").columns("ID2"),
ds.Tables("detail2").columns("ID2"))

'--note: maintable data is displayed in textboxes on the form. I iterate
through
'--maintbl and display 1 row of data at a time. I want to display the
related rows
'--in the datagridviews

datagridview1.Datasource = ds
datagridview2.datasource = ds

datagridview1.Datamember = "maintbl.relDetail1"
datagridview2.Datamember = "maintbl.relDetail2"

When I bring up the form, I see the data from maintbl in the textboxes on
the form, but there is no data in either of the datagridviews. May I
request
if you could see/explain what I am doing incorrectly?

Thanks,
Rich

"Cor Ligthert [MVP]" wrote:

Rich,

I remember me that the databinding I am talking about is with simple
controls in the reverse order as you want.

Here is almost exactly your problem although it goes 3 instead as you
need 2
deep.

http://www.vb-tips.com/dbpages.aspx?IA=DG

I hope this helps,

Cor
"Rich" <Ri**@discussions.microsoft.com> schreef in bericht
news:6A**********************************@microsof t.com...

>I decided to go ahead an experiment with DataRelations. Anyway, I am
>not
> getting any row filtering for the datagridviews. However, I do get
> rowfilter
> when I use the Dataview.Rowfilter property. So, does anyone have an
> understandable way to use DataRelations?
>
> "Rich" wrote:
>
>> Hello,
>>
>> I am pulling master data from one master table and detail data from
>> two
>> detail tables. The rows from the master data are displayed in
>> textboxes
>> on
>> my form which are bound to the data with databinding. The data from
>> the
>> 2
>> detail tables are displayed in 2 datagridviews. I filter each
>> datagridview
>> with a Dataview object using the dataview.Rowfilter property. This is
>> sort
>> of working except that I am getting an except on one of my detail
>> tables
>> (used to be no exceptions until I converted the detail tables into
>> views
>> on
>> the sql server - to simplify select command sql). So I was reading up
>> on
>> the
>> DataRelations object.
>>
>> Before I knock myself out changing all my client code, can I achieve
>> the
>> same row filtering functionality by using DataRelations instead of
>> Dataview.Rowfilter for the datagridviews? According to the reading, I
>> interpreted the reading to suggest that DataRelations are more
>> efficient -
>> but the only specified that it was more efficient than using a join
>> statement
>> in the select command. Any suggestions appreciated on if I should use
>> a
>> DataRelation vs Dataview.Rowfilter.
>>
>> Thanks,
>> Rich

[Rich]

Hi Cor,

I made a simpler version of my form that only pulled the data as in your
example - with separate dataAdapters. It worked perfectly, just like your
example. I will still need to practice a little bit to really understand
datarelations. But now I have a start.

I thank you so much for your help and for sharing that great article about
dataRelations.

Thanks very much for your help.

Rich

"Cor Ligthert [MVP]" wrote:

Rich,

You are using 1 dataadapter, however between the fills I see no change of
the SelectCommand, *I* don't not know a method performing this. You need at
least three daatatables (or with one, seperate SelectCommands for that) and
not a joined resultset.

I don't know if you use a command with wich you get 3 times 3 tables, but
that for probably understandable reasons I never tried.

Would you not first try it with 3 dataadapters, which consumes nothing
extra.

Cor

"Rich" <Ri**@discussions.microsoft.com> schreef in bericht
news:34**********************************@microsof t.com...

Hi Cor,

Thank you for your reply to my question. I went to your website. I read
your article on Datagridview and datarelations. It looks like a great
article. I tried the example, but I was not able to get it to work. Here
is
what I tried (using VB2005):

...

'--note: I am only using 1 dataAdapter to fill my dataset
...
da.Fill(ds, "maintbl")
da.Fill(ds, "detail1")
da.Fill(ds, "detail2")

ds.Relations.Add("relDetail1", ds.tables("maintbl").columns("ID1"),
ds.Tables("detail1").columns("ID1"))
ds.Relations.Add("relDetail2", ds.Tables("maintbl").columns("ID2"),
ds.Tables("detail2").columns("ID2"))

'--note: maintable data is displayed in textboxes on the form. I iterate
through
'--maintbl and display 1 row of data at a time. I want to display the
related rows
'--in the datagridviews

datagridview1.Datasource = ds
datagridview2.datasource = ds

datagridview1.Datamember = "maintbl.relDetail1"
datagridview2.Datamember = "maintbl.relDetail2"

When I bring up the form, I see the data from maintbl in the textboxes on
the form, but there is no data in either of the datagridviews. May I
request
if you could see/explain what I am doing incorrectly?

Thanks,
Rich

"Cor Ligthert [MVP]" wrote:

Rich,

I remember me that the databinding I am talking about is with simple
controls in the reverse order as you want.

Here is almost exactly your problem although it goes 3 instead as you
need 2
deep.

http://www.vb-tips.com/dbpages.aspx?IA=DG

I hope this helps,

Cor
"Rich" <Ri**@discussions.microsoft.com> schreef in bericht
news:6A**********************************@microsof t.com...
>I decided to go ahead an experiment with DataRelations. Anyway, I am
>not
> getting any row filtering for the datagridviews. However, I do get
> rowfilter
> when I use the Dataview.Rowfilter property. So, does anyone have an
> understandable way to use DataRelations?
>
> "Rich" wrote:
>
>> Hello,
>>
>> I am pulling master data from one master table and detail data from
>> two
>> detail tables. The rows from the master data are displayed in
>> textboxes
>> on
>> my form which are bound to the data with databinding. The data from
>> the
>> 2
>> detail tables are displayed in 2 datagridviews. I filter each
>> datagridview
>> with a Dataview object using the dataview.Rowfilter property. This is
>> sort
>> of working except that I am getting an except on one of my detail
>> tables
>> (used to be no exceptions until I converted the detail tables into
>> views
>> on
>> the sql server - to simplify select command sql). So I was reading up
>> on
>> the
>> DataRelations object.
>>
>> Before I knock myself out changing all my client code, can I achieve
>> the
>> same row filtering functionality by using DataRelations instead of
>> Dataview.Rowfilter for the datagridviews? According to the reading, I
>> interpreted the reading to suggest that DataRelations are more
>> efficient -
>> but the only specified that it was more efficient than using a join
>> statement
>> in the select command. Any suggestions appreciated on if I should use
>> a
>> DataRelation vs Dataview.Rowfilter.
>>
>> Thanks,
>> Rich