Data Relations with DataTable Class

  • Thread starter Thread starter Anil Gupte
  • Start date Start date
A

Anil Gupte

After reading a tutorial and fiddling, I finally got this to work. I can
now put two tables created with a DataTable class into a DataRelation.
Phew! And it works!
Dim tblSliceInfo As New DataTable("SliceInfo")

Dim tblSliceRatings As New DataTable("SliceRatings")

'.... All the adding datacolumns, datarows, etc. goes here..

DatasetInit.Tables.Add(tblSliceInfo)

DatasetInit.Tables.Add(tblSliceRatings)

Dim colSliceInfo As DataColumn

Dim colSliceRatings As DataColumn

colSliceInfo = tblSliceInfo.Columns("SliceID")

colSliceRatings = tblSliceRatings.Columns("SliceRatingsID")

Dim SliceIDRelation As DataRelation

SliceIDRelation = New DataRelation("SliceIDRelation", colSliceInfo,
colSliceRatings)

DatasetInit.Relations.Add(SliceIDRelation)

Anyway, what I want to do now is that instead of using those tables as
above, I want to use a SQL statement so that I can select the correct
records from the child table. How do I do this? I am planning to bind the
values in the fields to textboxes and then allow editing of those.

Please give me a clue how to go about it.
Thanx,
 
Cor:

Thanx for your input. Unfrtunately, that example uses an access database,
but I am using a DataTable class, which I think will be more secure, since
people will not be able to get their hands on the data. The problem I am
having is this. If you see the code below:
DatasetInit.Tables.Add(tblSliceInfo) ' tblSliceInfo= "SliceInfo"
DatasetInit.Tables.Add(tblSliceRatings) ' tblSliceRatings = "SliceRatings"

Dim colSliceInfo As DataColumn
Dim colSliceRatings As DataColumn
colSliceInfo = tblSliceInfo.Columns("SliceID")
colSliceRatings = tblSliceRatings.Columns("SliceRatingsID")
Dim SliceIDRelation As DataRelation
SliceIDRelation = New DataRelation("SliceIDRelation", colSliceInfo,
colSliceRatings)
DatasetInit.Relations.Add(SliceIDRelation)

DataGrid1.SetDataBinding(DatasetInit, "SliceInfo")
DataGrid2.SetDataBinding(DatasetInit, "SliceInfo.SliceIdRelation")

TextBox1.DataBindings.Add("Text", DatasetInit, "SliceInfo.SliceID")
TextBox2.DataBindings.Add("Text", DatasetInit, "SliceInfo.SliceTitle")
TextBox3.DataBindings.Add("Text", DatasetInit, "SliceInfo.SliceStartAbs")
TextBox4.DataBindings.Add("Text", DatasetInit, "SliceInfo.L3Filename")

TextBox5.DataBindings.Add("Text", DatasetInit, "SliceRatings.RatingName")
TextBox6.DataBindings.Add("Text", DatasetInit, "SliceRatings.RatingValue")
TextBox7.DataBindings.Add("Text", DatasetInit, "SliceRatings.RatingName")
TextBox8.DataBindings.Add("Text", DatasetInit, "SliceRatings.RatingValue")

Everything works great in the Datagrids. Datagrid1 shows the rows in the
master/parent table Datagrid2 shows the child rows in the child table. In
the text boxes, Textbox1 thru 4 are fine and as I step through the rows in
Datagrid1, their value changes. Unfortunately, the values in the Textbox5
thru 8 do not change at all, leading me to believe that they are not somehow
bound. Also, how do I make Textbox7 and 8 have the value from the second
child row (right now it just repeats what is in textbox5 and 6 respectively)

Thanx for any help.

--
Anil Gupte
www.keeninc.net
www.icinema.com
 
This may help. You might want to check out the BindingSource
component; using this for your databinding will more
easily enable the parent/child binding. Here's an example;
hopefully there will be something in here that you can use.

This example is for a form with two grids on it, and a bunch
of textboxes. One grid is the parent, the other is the
children, and the textboxes are bound to the parent.
---------------------------

I have a dataset that has two tables: Customers and Orders.
(This runs against Northwind, so if you have that
installed, you can try this out.)

I set up a strongly-typed dataset using the Data Set
Designer with both of those tables in it, and there is
a field called CustomerID in the Orders table that links
to the CustomerID table in Customers. So Orders have a
Customer as a parent. The data relation between
the customer and the orders is defined as FK_Orders_Customers.

(You can set up your dataset however you like.)

This is in Form_Load:

'First, populate the dataset.
Dim nwData As CustomersDataSet = CustomersDataSet.GetCustomers()

'Set the data source for the binding source to the dataset.
CustomersBindingSource.DataSource = nwData
'Add this so it knows which table to use .
CustomersBindingSource.DataMember = "Customers"
'Set the data source for the grid to the customers binding source.
CustomersDataGridView.DataSource = CustomersBindingSource

'Add the binding for the child; set its binding source to the same
' one as the parent.
Customers_OrdersBindingSource.DataSource = CustomersBindingSource
'Set the datamember to the foreign key joining the two tables.
' You can see this on your CustomersDataSet diagram.
Customers_OrdersBindingSource.DataMember = "FK_Orders_Customers"
'Set the data source on the child grid to points at its
' binding source.
OrdersDataGridView.DataSource = Customers_OrdersBindingSource

AddTextBoxDataBindings()
AddComboBoxDataBindings()

Here are the routines that bind the textboxes and combo box
in case you want them. These only bind to the Customers table,
but you can easily bind textboxes to the children by binding
to the Customers_OrdersBindingSource instead of the
CustomersBindingSource.

Private Sub AddTextBoxDataBindings()
'Bind each text box to the appropriate field in the
' CustomersBindingSource
CustomerIDTextBox.DataBindings.Add("Text", _
CustomersBindingSource, "CustomerID")
ContactNameTextBox.DataBindings.Add("Text", _
CustomersBindingSource, "ContactName")
CompanyNameTextBox.DataBindings.Add("Text", _
CustomersBindingSource, "CompanyName")
ContactPhoneTextBox.DataBindings.Add("Text", _
CustomersBindingSource, "Phone")
End Sub

Private Sub AddComboBoxDataBindings()
ContactsComboBox.DataSource = CustomersBindingSource
ContactsComboBox.DisplayMember = "ContactName"
ContactsComboBox.ValueMember = "CustomerID"
End Sub

This information came out of Brian Noyes's book on Data Binding.
---------------------------

As for displaying info from specific rows in textboxes
5 through 8, you could attach a handler to the positionChanged
event on the binding source of the parents. In the event, you
could check for multiple records in the children (probably
using the binding source, maybe BindingSource.Count?)
and populate the textboxes accordingly.

Hope this helps.
Robin S.
 
Thanx, my main problem is trying to do this with a DataTable (in memory, so
there is no DataConnection object, and no physical database). I have done
it before with a MS Access DB. And I have done the Parent table bindings
succesfully in he current problem. It is just that I have not been able to
do the Child table bindings to text boxes. Basically, I know that I need to
find a DataRow, and then DataRow.ChildRows and then bind Items(n) to each
text box. I want to do databinding so I can write just one simple update
function and update my DataTable.

Thanx,
 
It's not a DataTable, it's a DataSet with multiple tables, right?

Assuming that's true, my code should work; just replace the parts
where it fills the dataset by accessing the database with your
parts where you populate the dataset. You can still using the
BindingSource components to lubricate the process. After they
have made changes, you can invoke the EndEdit method on the
BindingSource to save the changes on the screen to the
underlying DataSet.

If you were attached to a database, then after doing the EndEdit,
you would invoke the Update method on the TableAdapter to
cascade the changes down into the database. You won't have that
part; you can save them however you want to after the DataTable
is updated.

I don't know of a way to bind different rows in a datagrid to
the textboxes, though. I've read a lot of material about data
binding, and have never seen anything that will do that. So
be sure to let us know if you find something like that. The
only way I could think of is to load them manually as the user
steps through the parent records, as noted below.

Good luck!

Robin S.
--------------------------------------
 
It *is* a DataTable and I am also using DataRow and DataColumn classes.
This way, I do not have to send an mdb file along with my app. Of course I
still created a Dataset using two DataTable objects.
Dim tblSliceInfo As New DataTable("SliceInfo")

Dim tblSliceRatings As New DataTable("SliceRatings")

Dim DatasetInit As New DataSet

DatasetInit.Tables.Add(tblSliceInfo)

DatasetInit.Tables.Add(tblSliceRatings)

Then the rest of the code I gave couple of messages ago.
 
You have a DataSet with two DataTables in it.

There is a DataRelation between the two tables.

You have a grid showing the parents.

You have a grid showing the children.

You have some textboxes showing information from the
currently selected parent.

You have some textboxes showing information from multiple
rows of the children for the currently selected parent.

You want to know how to bind the textboxes showing
information from the children rows for the currently
selected parent.

Is that right? Or am I completely not getting what
you're asking?

Robin S.
-----------------------
 
Almost correct. One important thing is when I say DataTables, I am
referring to the Class System.Data.DataTable which is aparently derived from
System.ComponentModel.MarshalByValueComponent

In other words, I did not create a DataConnection object or even a
DataAdapter object and there is no database - only the tables (out of thin
air). :-)

Also, the statement
You have some textboxes showing information from multiple
rows of the children for the currently selected parent.
Click to expand...

si not entirely correct - I am able to show only the first row in the
textboxes (it just repeats), although I can see multiple rows in the
DataGrid bound to the child table.

Thanx for your patience.
 
Well, it doesn't matter *how* the datatables got created. My
code sample should still work for you. Just replace the data
source stuff (see my comments way down below, marked with **).

Again, I iterate that I don't know of any way to bind the
children textboxes to the specific rows of the grid. I would
add an event to the PositionChanged event of the parent,
and fill the 4 child textboxes accordingly in code.

Robin S.
-----------------------------

Anil Gupte said:
Almost correct. One important thing is when I say DataTables, I am
referring to the Class System.Data.DataTable which is aparently
derived from System.ComponentModel.MarshalByValueComponent

In other words, I did not create a DataConnection object or even a
DataAdapter object and there is no database - only the tables (out of
thin air). :-)

Also, the statement

si not entirely correct - I am able to show only the first row in the
textboxes (it just repeats), although I can see multiple rows in the
DataGrid bound to the child table.

Thanx for your patience.
Click to expand...

**Set the parent DataSource to the name of your dataset
that has the two tables in it.

**set this to the name of your parent table.

**this is the parent grid. Set it to the parent BindingSource.

**This is the datasource for the bindingsource for the children,
** set to the binding source for the parents.

**Set this to the data relation between the two tables.

**this is the grid for the children.

**this is how to bind textboxes to show data from the parent grid.

**this still applies. I don't know of a way to bind to a
specific row in the grid. I would use this method to
fill the textboxes that show information from the children.
 
Back
Top