Fun and excitement updating related data

[Earl]

Ideally, what I would like to do is to populate a datagrid with the
information from 2 tables. Man, does that sound simple ... if so, please
join me on this expedition ...

Begin by using two dataAdapters, filling one from the main database table
and another from the foreign-keyed table and set up a data relation (thanks
to Kevin on pointing me in the general direction). Now use the expressions
to add columns that we want displayed in the grid.

But this is where things get crazy.

Indeed, I can now bind this related data to the datagrid. Everything "looks"
great. I can even do a delete with CurrencyManager. But when I try to do an
Insert to the main table, I get a constraint exception,
"ForeignKeyConstraint requires the child key values to exist in the parent
table"

Do who, huh, what? The child (Supplier) key values must exist in the parent
(Zip) table. My update logic calls only for the Supplier table (and worked
perfect before I went down this road). And I most certainly do not want to
be inserting any data into the Zip table (it is a lookup table for all zip,
city, state names in the U.S.).

Just because I enjoy banging my head against the wall, I set the relation
constraint to False. Now we actually add a record to the Supplier table, but
I still get an exception: "Cannot change ReadOnly property for the
Expression column"

Here is the relevant snippet. If anyone can see clearly through this, advice
would be appreciated. Note that what should be the Parent table is set as
the Child because the silly syntax for the Expression apparently does not
allow you to associate a column from the Child.

********************************************
Dim strSQLServer As New SqlConnection(strConnString)

Dim cmdSuppliers As New SqlCommand("GetAllSuppliers", strSQLServer)
'a join query that matches up all supplier zip IDs
Dim cmdZipMatches As New SqlCommand("GetAllSupplierZips", strSQLServer)
cmdSuppliers.CommandType = CommandType.StoredProcedure
cmdZipMatches.CommandType = CommandType.StoredProcedure

cmdSuppliers.Connection = strSQLServer
cmdZipMatches.Connection = strSQLServer
strSQLServer.Open()

Dim da As New SqlDataAdapter(cmdSuppliers)
Dim daZip As New SqlDataAdapter(cmdZipMatches)
da.Fill(dsx, "dtSuppliers")
daZip.Fill(ds, "dtSupplierZips")

ds.Tables("dtSuppliers").Columns("SupplierID").ColumnMapping =
MappingType.Hidden
ds.Tables("dtSuppliers").Columns("Tstamp").ColumnMapping =
MappingType.Hidden
ds.Tables("dtSuppliers").Columns("SupplierZipCityID").ColumnMapping =
MappingType.Hidden
ds.Tables("dtSupplierZips").Columns("ZipCityStateID").ColumnMapping =
MappingType.Hidden

Dim rel As DataRelation
rel = New DataRelation("CityStateZipSupplier", _
ds.Tables("dtSupplierZips").Columns("ZipCityStateID"), _
ds.Tables("dtSuppliers").Columns("SupplierZipCityID"), False)
ds.Relations.Add(rel)

ds("dtSuppliers").Columns.Add("City", GetType(String),
"Parent(CityStateZipSupplier).City")
ds.Tables("dtSuppliers").Columns.Add("State", GetType(String),
"Parent(CityStateZipSupplier).State")
ds.Tables("dtSuppliers").Columns.Add("Zip", GetType(String),
"Parent(CityStateZipSupplier).Zip")

dtSuppliers = ds.Tables("dtSuppliers")
DataGrid1.DataSource = dtSuppliers

 

[Earl]

Please ignore the typos on:

da.Fill(dsx, "dtSuppliers")

should say,
da.Fill(ds, "dtSuppliers")

And also,

ds("dtSuppliers").Columns.Add("City", GetType(String),
"Parent(CityStateZipSupplier).City")

should say,
ds.Tables("dtSuppliers").Columns.Add("City", GetType(String),
"Parent(CityStateZipSupplier).City")

Neither of these are issues.

 

[Kevin Yu [MSFT]]

Hi Earl,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you cannot add rows to the child table
because of foreign key constraint. If there is any misunderstanding, please
feel free to let me know.

As far as I know, when you have created a DataRelation object between two
tables, ADO.NET will create a ForeignKeyConstraint object on the child
table automatically to make sure that each zip points to a valid zip code
in the parent table. This is by design. To workaround this issue, I think
we have to remove that constraint object in code and everything works fine.

Here is an example. On my computer, the ForeignKeyConstraint object's index
is 1. So I use RemoveAt(1) to achieve this.

Dataset1 ds = new Dataset1();
sda2.Fill(ds.CityStateZip);
sda1.Fill(ds.Supplier);
ds.Supplier.Constraints.RemoveAt(1);

HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Earl]

Thanks for the ideas Kevin. But what does the ordinal (1) represent in your
RemoveAt(1) method? And why would not EnforceConstraints = False do the same
thing? In any event, I tried both methods. With RemoveAt() I can find no
ordinal that represents a constraint. EnforceContstraints to false does
nothing either.

 

[Kevin Yu [MSFT]]

Hi Earl,

Setting EnforceConstraint to false will also turn off other constraints. So
it's not recommended. You can try to check in watch window for
ds.Supplier.Constraints collection to see which item is the
ForeignKeyConstraint object. I think there are two constraint objects in
it. Index 0 for UniqueConstraint and index 1 for ForeignKeyConstraint. It
works well on my machine.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Earl]

Thanks Kevin, I will take your recommendation to check for the collection.