HELP! Datasets and control synchronization

[steve]

Hello,
I have been trying to populate 2 or more controls from two *different*
(related) tables for the last week without success.
My latest try is: 2 simple SELECT statements in 2 adapters that populate the
*same* dataset and then i define a relation among the two tables.
SIMPLE !

Everything works fine but the controls from different tables are NOT
synchronised.

Am I doing something wrong or this is the wrong way altogether to attack
this problem?

I *also* tried to create a "complete" SELECT statement and pass the result
in the dataset with no success either.

Can anyone help? I know its very easy but i cant see what is wrong with my
code.

(Obviously i left out connection string etc. since everything else is
working)
And I am NOT asking for DataGrids!!!!!! this is easy. it worked. and i could
see where every column was coming from.
I am interested in labels, textboxes and droplists.

Thanx in advance
-steve

-----------------------------------------------------------------------

Dim strSelect1 As String = " SELECT * FROM tblCustomers"
Dim strSelect2 As String = " SELECT * FROM tblOrders "
Dim DS As New DataSet

'SQL Data Adapter
Dim DBAdapter1 As OleDbDataAdapter = New OleDbDataAdapter(strSelect1,
DBConnection)
DBAdapter1.Fill(DS, "tblCustomers")

Dim DBAdapter2 As OleDbDataAdapter = New OleDbDataAdapter(strSelect2,
DBConnection)
DBAdapter2.Fill(DS, "tblOrders")

DS.Relations.Add("CustomerOrders", _
DS.Tables("tblCustomers").Columns("CustomerID"), _
DS.Tables("tblOrders").Columns("CustomerID"))


'****** populate controls ***************
cmb1.DataSource = DS
cmb1.DisplayMember = "tblCustomers.CustomerID"

cmb2.DataSource = DS
cmb2.DisplayMember = "tblOrders.OrderID"

lbl1.DataBindings.Clear()
lbl1.DataBindings.Add("text", DS, "tblOrders.Date")
'*********************

 

[Trygve Lorentzen]

Hi,

as far as I know, this is not handled automatically, I have never tried this
with comboboxes like you're doing, but I believe you would have to use a
DataView and a filter or the GetChildRows() method.

GetChildRows:
In the event of the first (parent) combobox changing, you must set the
datasource of the second (child) combobox to
DS.Tables("tblCustomers").getChildRows("CustomerOrders")

DataView and filter:
DataView dvOrders = new DataView(DS.Tables("tblOrders"));
cmb2.DataSource = dvOrders;
cmb2.DisplayMember = "dvOrders.OrderID"

In the event of the first (parent) combobox changing, you must set the
dataview filter as follows:
dvOrders.RowFilter("CustomerID = " +
DS.Tables("tblCustomers").Columns("CustomerID"));

Or something like that. You will also have to set the label accordingly to
which of the two approaches you choose. Note that my code is written just as
I remember it, syntax may be wrong, but you will figure it out I guess, this
should be the core of what you need at least. Good luck!

Cheers,
Trygve

 

[steve]

An addition:

when i change the first connection string to:
SELECT tblCustomers.*, tblOrders.* FROM (tblCustomersINNER JOIN tblOrders ON
tblOrders.CustomerID = tblCustomers.CustomerID)

then things work *except* the first combobox: tblCustomers.CustomerID !!!

Actually as i read on the Net if you do something like this then the
resulting table is by fefault called by the first table's name: tblCustomers
in our case. This is why i can access tblCustomers.OrderID or something,
that doesnt belong to that table originally.

 

[Grzegorz Danowski]

Hello,
I have been trying to populate 2 or more controls from two *different*
(related) tables for the last week without success.
My latest try is: 2 simple SELECT statements in 2 adapters that populate
the
*same* dataset and then i define a relation among the two tables.
SIMPLE !
(...)

DS.Relations.Add("CustomerOrders", _
DS.Tables("tblCustomers").Columns("CustomerID"), _
DS.Tables("tblOrders").Columns("CustomerID"))


'****** populate controls ***************
cmb1.DataSource = DS
cmb1.DisplayMember = "tblCustomers.CustomerID"

cmb2.DataSource = DS
cmb2.DisplayMember = "tblOrders.OrderID"

lbl1.DataBindings.Clear()
lbl1.DataBindings.Add("text", DS, "tblOrders.Date")
'*********************

Please try this settings:
cmb1.DataSource = DS
cmb1.DisplayMember = "tblCustomers.CustomerID"

cmb2.DataSource = DS
cmb2.DisplayMember = "tblCustomers.CustomerOrders.OrderID"

lbl1.DataBindings.Add("text", DS, "tblCustomers.CustomerOrders.Date")

It works in my situation but I use typed dataset, and maybe there is some
differences.

Regards,
Grzegorz

 

[Grzegorz Danowski]

Hello,
I have been trying to populate 2 or more controls from two *different*
(related) tables for the last week without success.
My latest try is: 2 simple SELECT statements in 2 adapters that populate
the
*same* dataset and then i define a relation among the two tables.
SIMPLE !
(...)

DS.Relations.Add("CustomerOrders", _
DS.Tables("tblCustomers").Columns("CustomerID"), _
DS.Tables("tblOrders").Columns("CustomerID"))


'****** populate controls ***************
cmb1.DataSource = DS
cmb1.DisplayMember = "tblCustomers.CustomerID"

cmb2.DataSource = DS
cmb2.DisplayMember = "tblOrders.OrderID"

lbl1.DataBindings.Clear()
lbl1.DataBindings.Add("text", DS, "tblOrders.Date")
'*********************

Please try this settings:

cmb1.DataSource = DS
cmb1.DisplayMember = "tblCustomers.CustomerID"

cmb2.DataSource = DS
cmb2.DisplayMember = "tblCustomers.CustomerOrders.OrderID"

lbl1.DataBindings.Add("text", DS, "tblCustomers.CustomerOrders.Date")

It works perfectly in my situation but I use typed dataset, and maybe there
are some
differences.

Regards,
Grzegorz

 

[steve]

This seems to work so far.
I am still very "uncertain' about a lot of things in Ado.NET.... but this
makes more sense since you access a relation.
A few people keep telling me about dataviews, i should look into that also.
I really appreciate your help!!! and everyone else's.