Parent expression column

[Jason James]

Hi all,

I am trying to create a visualisation of a many-to-many
relationship in VB.NET. The tables, columns and
relationships are detailed below (Sorry for all the code).

The cables and columns match those found in the
Acess database that the application is based around.

At the end of the code is an expression column that
I wish to add tothe joining table in the middle of
my many to many relationship.

Dim mmToothType As New DataColumn("Tooth_Type",
System.Type.GetType("System.String"), "Parent(Type_Teeth).yID")

The column uses a relationship to get the parent record of the
joining table. The record is added without error. However,
when I run my code, create the tables and relationship and then
load the data from the database, the expression column
shows a NULL when loaded to a datagrid control!!!

Does anyone has any experience of this type of expression
column? Is this the best way to illustrate the many-to-many
relationship? The joining table (Teeth) contains the number of
teeth of a particular type, as well as the FK columns from the
person table and the teethtype table and I need to be able to
display this column in the same grid.

Many thanks in advance for you help.

Jason.

' Person Table
Dim dPerson As New DataTable("Person")
' Add Name column
dPerson.Columns.Add("Name",
System.Type.GetType("System.String"))
' Add PK
Dim dColumnPK As New DataColumn
dColumnPK.ColumnName = "ID"
dColumnPK.DataType = System.Type.GetType("System.Int32")
dColumnPK.AllowDBNull = False
dColumnPK.AutoIncrement = True
dColumnPK.AutoIncrementSeed = 1
dColumnPK.AutoIncrementStep = 1
dColumnPK.Unique = True
dPerson.Columns.Add(dColumnPK)

' Teeth table
Dim tTeeth As New DataTable("Teeth")
' Add the number of teeth
tTeeth.Columns.Add("tNumber",
System.Type.GetType("System.Int32"))
' Add PK
Dim tColumnPK As New DataColumn
tColumnPK.ColumnName = "tID"
tColumnPK.DataType = System.Type.GetType("System.Int32")
tColumnPK.AllowDBNull = False
tColumnPK.AutoIncrement = True
tColumnPK.AutoIncrementSeed = 1
tColumnPK.AutoIncrementStep = 1
tColumnPK.Unique = True
tTeeth.Columns.Add(tColumnPK)
' FK to Person table
Dim tColumFKPerson As New DataColumn
tColumFKPerson.ColumnName = "tdID"
tColumFKPerson.DataType = System.Type.GetType("System.Int32")
tColumFKPerson.AllowDBNull = False
tTeeth.Columns.Add(tColumFKPerson)
' Add FK to TeethType table
Dim tColumFKToothType As New DataColumn
tColumFKToothType.ColumnName = "tyID"
tColumFKToothType.DataType =
System.Type.GetType("System.Int32")
tColumFKToothType.AllowDBNull = False
tTeeth.Columns.Add(tColumFKToothType)

' TeethType table
Dim tTeethType As New DataTable("TeethType")
' Tooth name
tTeethType.Columns.Add("yName",
System.Type.GetType("System.String"))
' Add PK
Dim yColumnPK As New DataColumn
yColumnPK.ColumnName = "yID"
yColumnPK.DataType = System.Type.GetType("System.Int32")
yColumnPK.AllowDBNull = False
yColumnPK.AutoIncrement = True
yColumnPK.AutoIncrementSeed = 1
yColumnPK.AutoIncrementStep = 1
yColumnPK.Unique = True
tTeethType.Columns.Add(yColumnPK)

' Person-Teethth relationship
Dim drPersonTeeth As New DataRelation("Person_Teeth",
dColumnPK, tColumFKPerson)
' ToothType - Teethth relationship
Dim drTeethType_Teeth As New DataRelation("Type_Teeth",
yColumnPK, tColumFKToothType)

' Add tables to dataset
ds.Tables.Add(dPerson)
ds.Tables.Add(tTeeth)
ds.Tables.Add(tTeethType)

' Add relationships to dataset
ds.Relations.Add(drPersonTeeth)
ds.Relations.Add(drTeethType_Teeth)

' Calculated expression that gets the parent from the
Type_Teeth relationship
Dim mmToothType As New DataColumn("Tooth_Type",
System.Type.GetType("System.String"), "Parent(Type_Teeth).yID")
' Add new column to Teeth table
Try
tTeeth.Columns.Add(mmToothType)
Catch ex As NullReferenceException
MessageBox.Show(ex.Message)
End Try

 

[Andrew Conrad]

Hi Jason -

This is a known bug with DataSet which is being fixed in Visual Studio
2005. A work around it is to manually update a value on the child row to
force refreshing of the parent expression value.

Andrew Conrad
Microsoft Corp

 

[Jason James]

Andrew

Thanks for the info. Interestingly it worked fine when I performed a

da.FillSchema(ds)

and then a

da.fill(ds)

command without first creating the data tables and their constraints.

Regards,

Jason.