Relationships using multi-column key..

[Graham Blandford]

Hi all,

Can anyone assist me? VB6er trying to delve into the world of .NET...

I have inherited a poorly designed database (access).. that uses
multi-column keys to determine parent-child relationships. Could anyone
suggest how I can handle this using a relation in VB.NET.

E.g.

Table "Area"

Year
AreaId
AreaName

has a primary key of Year and AreaId,

Table "Section" has child records of "Area";

Year
AreaId
Section
Section Name

Therefore, child records are selected as those that have the same Year and
AreaId ..

If anyone can help I'd very much appreciate it.

Thanks,
Graham Blandford

[Val Mazur]

Hi,

What do you need, to create joined SQL statement or set relations between
the datatables inside of the dataset?

--
Val Mazur
Microsoft MVP
"Graham Blandford" <gr**************@sympatico.ca> wrote in message
news:U1********************@news20.bellglobal.com. ..

Hi all,

Can anyone assist me? VB6er trying to delve into the world of .NET...

I have inherited a poorly designed database (access).. that uses
multi-column keys to determine parent-child relationships. Could anyone
suggest how I can handle this using a relation in VB.NET.

E.g.

Table "Area"

Year
AreaId
AreaName

has a primary key of Year and AreaId,

Table "Section" has child records of "Area";

Year
AreaId
Section
Section Name

Therefore, child records are selected as those that have the same Year and
AreaId ..

If anyone can help I'd very much appreciate it.

Thanks,
Graham Blandford

[Cor Ligthert]

Hi Graham,

You mean something as this?

Cor
\\\
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()
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)
///

[William Ryan eMVP]

Hi Graham:

Cor and Val already answered it for you but i'd like to add one thing
semi-related if I may. If you look at Cor's example, that's how you join
table using a DataRelation w/ an untyped dataset.
This gives you a tremendous amount of power and flexibility and allows you
to navigate related records and perfom master-detail binding and all of that
good stuff. This is the crux of what you asked.

As an aside though, if you want to create composite keys, so that you can
find/navigate on multiple fields that may comprise a key, you can set the
PrimaryKey property of a datatable
http://www.knowdotnet.com/articles/dataviewsort.html . You should note
though that the PrimaryKey property takes an ARRAY of datacolumns, even if
that array contains just one item. It's a common mistake to set the
property to the column itself instead of an array.

Adding a Composite Key does nothing in and of itself for the problem you
originally asked about but it does allow you to enforce integrity rules and
use finds on the PrimaryKey field(s). Combined with using a dataRelation as
Cor illustrated can really allow you to do some cool stuff.

HTH,

Bill

--

W.G. Ryan, eMVP

http://forums.devbuzz.com/
http://www.knowdotnet.com/williamryan.html
http://www.msmvps.com/WilliamRyan/
"Graham Blandford" <gr**************@sympatico.ca> wrote in message
news:U1********************@news20.bellglobal.com. ..

Hi all,

Can anyone assist me? VB6er trying to delve into the world of .NET...

I have inherited a poorly designed database (access).. that uses
multi-column keys to determine parent-child relationships. Could anyone
suggest how I can handle this using a relation in VB.NET.

E.g.

Table "Area"

Year
AreaId
AreaName

has a primary key of Year and AreaId,

Table "Section" has child records of "Area";

Year
AreaId
Section
Section Name

Therefore, child records are selected as those that have the same Year and
AreaId ..

If anyone can help I'd very much appreciate it.

Thanks,
Graham Blandford