Binding a child table to a datagrid control and setting the parent's ID

[Richard L Rosenheim]

I have a dataset containing a parent table related to a child table. The
child table contains an ID field (which is configured as autonumber in the
datatable), the ID of the parent, plus some addition fields.

I'm able to get the datagrid to be properly populate with only the child
records relating to the specified parent by setting the
..DefaultView.RowFilter property.

Currently, to properly add a new child record, I need to manually enter in
the value of the parent's ID. As I'm planning on hiding this column (since
the end user won't know or care about this value), I'm looking for a way of
getting the datagrid to assign the parent's ID automatically. I'm pretty
sure there's a way, but I haven't been able to find any documentation that
explains how to do it. Nor do I see any obvious events in the datagrid
control to tap into. I've tried checking my .Net programming books, and
searching Google, MSDN, etc. to no avail. Can someone please point me to
the appropriate documentation/tutorial/example/etc.?

TIA,

Richard Rosenheim

 

[Cor Ligthert]

Richard,

I saw our question so often that I decided to make a little sample today,

You can try it as first.

I do not know if it completly answers your question however it should give
it a big go.
(There is as well a method using the rowfilter, however that will maybe look
weird in this sample, so I have to decide if it bring it in this)

Cor

\\\ Needs a form with two datagrids, a button and a label
'The first click on the button shows one datagrid
'The second click shows the same info with two datagrid
'To make it nice a lot of other code is needed by instance
datagridtablestyles
'and columnstyles
Private Ds As New DataSet
Private dtCountries As DataTable
Private dtVBLTRegulars As DataTable
Private Sub Form1_Load(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles MyBase.Load
CreateTables()
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button1.Click
Static pushed As Integer = 1
Select Case pushed
Case 1
Label1.Text = "Relation One Datagrid"
Dim drel As New DataRelation _
("Regulars", Ds.Tables("Countries").Columns("Country"), _
Ds.Tables("Persons").Columns("Country"))
Ds.Relations.Add(drel)
DataGrid1.DataSource = dtCountries
DataGrid1.Expand(-1)
Case 2
Ds.Relations.RemoveAt(0)
Label1.Text = "Relation two Datatgrids"
Dim drel As New DataRelation _
("Regulars", Ds.Tables("Countries").Columns("Country"), _
Ds.Tables("Persons").Columns("Country"))
Ds.Relations.Add(drel)
DataGrid1.SetDataBinding(Ds, "Countries")
DataGrid2.SetDataBinding(Ds, "Countries.Regulars")
End Select
pushed += 1
End Sub
'This is only needed to show the sample
Private Sub CreateTables()
dtVBLTRegulars = New DataTable("Persons")
dtVBLTRegulars.Columns.Add("Id")
dtVBLTRegulars.Columns.Add("Name")
dtVBLTRegulars.Columns.Add("Country")
For i As Integer = 0 To 7
Dim dr As DataRow = dtVBLTRegulars.NewRow
dr(0) = i.ToString
dtVBLTRegulars.Rows.Add(dr)
Next
dtVBLTRegulars.Rows(0)(1) = "Herfried K. Wagner"
dtVBLTRegulars.Rows(1)(1) = "Ken Tucker"
dtVBLTRegulars.Rows(2)(1) = "CJ Taylor"
dtVBLTRegulars.Rows(3)(1) = "Jay B Harlow"
dtVBLTRegulars.Rows(4)(1) = "Terry Burns"
dtVBLTRegulars.Rows(5)(1) = "Tom Shelton"
dtVBLTRegulars.Rows(6)(1) = "Cor Ligthert"
dtVBLTRegulars.Rows(0)(2) = "EU"
dtVBLTRegulars.Rows(1)(2) = "US"
dtVBLTRegulars.Rows(2)(2) = "US"
dtVBLTRegulars.Rows(3)(2) = "US"
dtVBLTRegulars.Rows(4)(2) = "EU"
dtVBLTRegulars.Rows(5)(2) = "US"
dtVBLTRegulars.Rows(6)(2) = "EU"
dtCountries = New DataTable("Countries")
dtCountries.Columns.Add("Id")
dtCountries.Columns.Add("Country")
For i As Integer = 0 To 1
Dim dr As DataRow = dtCountries.NewRow
dr(0) = i.ToString
dtCountries.Rows.Add(dr)
Next
dtCountries.Rows(0)(1) = "EU"
dtCountries.Rows(1)(1) = "US"
Ds.Tables.Add(dtVBLTRegulars)
Ds.Tables.Add(dtCountries)
End Sub
///
I hope this helps a little bit?

Cor

 

[Richard L Rosenheim]

Cor,

I finally got time to try the example you provided. Some unexpected matters
prevented me from getting to it sooner. Thank you. The behavior of the
child (second) datagrid is exactly what I'm trying to achieve.

My issue in relating your example to my problem is that I don't have the
first datagrid. I'm displaying the parent information in text boxes after
the parent record is selected, by the user, from a listbox.

When I change the selected row in the parent datagrid (either by clicking
with the mouse, or programmatically using the CurrentRowIndex property), the
child datagrid is automatically refreshed. So, somehow, the fact that the
selected record focus has changed is being transmitted to the child
datagrid. Either directly, or (I'm assuming) via either the dataset. Yet,
I have not able to find any class members in either the datagrid, dataset,
datatable or relation objects that would seem to be playing a role.

Any ideas of how that's happening, or (more importantly), how I can
programmatically instruct the child datagrid which parent value to utilize?

The worst case scenario is to actually have a parent datagrid, and just not
make it visible. When the user selects the parent record from the listbox,
I could programmatically select the corresponding record in the datagrid.
But, that seems like an extremely crude hack and very inefficient way of
resolving this problem.

Richard Rosenheim

 

[Cor Ligthert]

Richard,

I do it just this way when it is with a listbox or a combobox
\\\a form, a datagrid, a listbox and a textbox
Private ds As New DataSet
Private dtCountries As DataTable
Private dtVBreg As DataTable
Private dv As DataView
Private Sub Form1_Load(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles MyBase.Load
CreateTables()
dv = New DataView(dtVBreg)
ListBox1.DataSource = ds.Tables("Countries")
ListBox1.DisplayMember = "Country"
ListBox1.ValueMember = "Country"
DataGrid2.DataSource = dv
TextBox1.DataBindings.Add("Text", dv, "Name")
dv.RowFilter = "Country = '" & ListBox1.SelectedValue.ToString & "'"
End Sub
Private Sub ListBox1_SelectedIndexChanged(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged
dv.RowFilter = "Country = '" & ListBox1.SelectedValue.ToString & "'"
End Sub
Private Sub CreateTables()
dtVBreg = New DataTable("Persons")
dtVBreg.Columns.Add("Name")
dtVBreg.Columns.Add("Country")
For i As Integer = 0 To 7
dtVBreg.Rows.Add(dtVBreg.NewRow)
Next
dtVBreg.Rows(0).ItemArray = New Object() _
{"Herfried K. Wagner", "EU"}
dtVBreg.Rows(1).ItemArray = New Object() _
{"Ken Tucker", "US"}
dtVBreg.Rows(2).ItemArray = New Object() _
{"CJ Taylor", "US"}
dtVBreg.Rows(3).ItemArray = New Object() _
{"Jay B Harlow", "US"}
dtVBreg.Rows(4).ItemArray = New Object() _
{"Terry Burns", "EU"}
dtVBreg.Rows(5).ItemArray = New Object() _
{"Tom Shelton", "US"}
dtVBreg.Rows(6).ItemArray = New Object() _
{"Cor Ligthert", "EU"}
dtCountries = New DataTable("Countries")
dtCountries.Columns.Add("Country")
For i As Integer = 0 To 1
Dim dr As DataRow = dtCountries.NewRow
dr(0) = i.ToString
dtCountries.Rows.Add(dr)
Next
dtCountries.Rows(0)(0) = "EU"
dtCountries.Rows(1)(0) = "US"
ds.Tables.Add(dtVBreg)
ds.Tables.Add(dtCountries)
End Sub
//
I hope this helps?

Cor

 

[Richard L Rosenheim]

Cor,

Thanks for the reply and the code. I saw that you are binding the datagrid
to the listbox. While it should have, it never occurred to me to try
binding the datagrid to the listbox...

But, in trying out the code you provided me, I'm encountering the same
issue. Yes, when I click between "US" and "EU" in the listbox, the datagrid
is updated accordingly. But, when I go to add a new record in the datagrid,
the "Country" field is not being automatically populated, which is what I'm
trying to achieve.

The reasons I'm trying to achieve that is (a) there's really is no reason to
display that column as the user selected the country via the list box and
(b) it will actually be a numeric record key which the user won't know
anyway.

There's a Microsoft chat on using the datagrid control starting in about 45
minutes. I'll let you know if I learn anything.

Thanks again for the assistance,

Richard Rosenheim

 

[Richard L Rosenheim]

Cor (and anyone else),

Here's the modified code:

Private ds As New DataSet
Private dtCountries As DataTable
Private dtVBreg As DataTable

Private Sub Form1_Load(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles MyBase.Load
CreateTables()
ListBox1.DataSource = ds.Tables("Countries")
ListBox1.DisplayMember = "Country"
ListBox1.ValueMember = "Country"
DataGrid2.DataSource = ds
DataGrid2.DataMember = "Countries.CountryPersons"
TextBox1.DataBindings.Add("Text", "ListBox1", "SelectedItem")
End Sub

Private Sub ListBox1_SelectedIndexChanged(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged

CType(Me.BindingContext(ds, "Countries"), CurrencyManager).Position
= ListBox1.SelectedIndex
End Sub

Private Sub CreateTables()
Dim drel As DataRelation

dtVBreg = New DataTable("Persons")
dtVBreg.Columns.Add("Name")
dtVBreg.Columns.Add("Country")
For i As Integer = 0 To 7
dtVBreg.Rows.Add(dtVBreg.NewRow)
Next
dtVBreg.Rows(0).ItemArray = New Object() _
{"Herfried K. Wagner", "EU"}
dtVBreg.Rows(1).ItemArray = New Object() _
{"Ken Tucker", "US"}
dtVBreg.Rows(2).ItemArray = New Object() _
{"CJ Taylor", "US"}
dtVBreg.Rows(3).ItemArray = New Object() _
{"Jay B Harlow", "US"}
dtVBreg.Rows(4).ItemArray = New Object() _
{"Terry Burns", "EU"}
dtVBreg.Rows(5).ItemArray = New Object() _
{"Tom Shelton", "US"}
dtVBreg.Rows(6).ItemArray = New Object() _
{"Cor Ligthert", "EU"}
dtCountries = New DataTable("Countries")
dtCountries.Columns.Add("Country")
For i As Integer = 0 To 1
Dim dr As DataRow = dtCountries.NewRow
dr(0) = i.ToString
dtCountries.Rows.Add(dr)
Next
dtCountries.Rows(0)(0) = "EU"
dtCountries.Rows(1)(0) = "US"
ds.Tables.Add(dtVBreg)
ds.Tables.Add(dtCountries)

drel = New DataRelation("CountryPersons", _
dtCountries.Columns(0), _
dtVBreg.Columns(1))
ds.Relations.Add(drel)
End Sub

 

[Cor Ligthert]

Richard,

It works, however how do you now fix that column insert, that stays the
problem

Cor

 

[Richard L Rosenheim]

I don't understand what you mean. What column insert? Can you please
elaborate?

Also, I noticed that the textbox binding broke in my experimentation. To
get it to display the country name, I commented out the textbox binding
statement in the Form_Load and added the following line to the ListBox1
SelectedIndexChanged event:

TextBox1.Text = ListBox1.SelectedItem("Country").ToString

Richard

 

[Cor Ligthert]

Richard,

There is not much difference in this solution and the one I provided.
You still use the selectedindex change to get the defaultview of the
datagrid changed.

It would be great when that was not necessary as what it is with 2
datagrids.

I thought that your question was how can I set the country in this case by
default to the one in the datagrid by the persons. For that I have no other
answer than I gave and I did not see another one, however maybe I missed
something.

Cor

 

[Richard L Rosenheim]

The issue I was trying to resolve was how to populate the parent key in the
child record when the user adds a new child record. Using your example, it
would be to populate the child's Country field with the country that was
currently selected in the listbox. With the two datagrid example, that was
being done automatically. But, when I moved away from using the two
datagrids, that capability disappeared. By using the code JFuentes
provided, that ability was restored.

The importance is that in real life, the Country table will be normalized,
and as such it would have both the Country name, and an ID field (which
would be an autonumber index). This ID field would need to be assigned to
the child record's Country field, and is not a value that the end user would
know. So expecting them to enter such a field would not be practical.

Thanks again for all your help,

Richard Rosenheim

 

[Cor Ligthert]

Richard,

You are right I never use that method with the * anymore because I cannot
delete with it, so I did not expect that, therefore I have learned again
something.

Cor