In my first ADO.Net 2.0 project I'm trying to create a Windows Forms application that allows updating customer records. These records are in an SQL Server 2003 database, and I am using the VS.Net 2005 DataSet Designer to create a typed DataSet and Customer TableAdapter. To start, I just output the internal IDs of fields to TextBoxes on a form. So far, so good.

One of the fields is for Language (English, French, Spanish, etc.), and there is a database table with language names and IDs. So I add a new Language TableAdapter and Relation to the DataSet Designer. The Relation is:

Previewing all TableAdapters look fine, and all customers have valid languageIDs. I create a Language ComboBox in my form, choose "Use Data bound items" and... where's Language? There are a few other tables I'm going to be using, but no Language. There's even another relation, but not the language one. Well, I'll leave it unbound for now, run it, and...

It lies; all rows contain valid unique data, and no constraint violations. I can run the SELECT commandText in all DataAdapters on the database without errors. This only started to happen after I added the Language table.

What am I doing wrong?

I figured it out.

When I added the Language table and comboBox to my form, VS helpfully added the languageTableAdapter to my Form_Load method:


But Language is parent to Customer and has a primary key relation. When Customer gets loaded Language hasn't been, and there are no foreign keys for Customer.languageID. How did I fix this? Simply load Language first:


The try/catch method worked because in my catch clause I loaded Language first. Proof again that design-time controls are no benefit if you don't understand the technology.

the following code is weird but works.

my fillby returnsa schema different from the fill schema- the default oneu can call it.

and i am getting the constraints exeption. need to catch and clear it again it seems dont ask me why. see if this brings you somewhere

Me.CostingDataSet.Costing_Bill.Constraints.Clear()

Try

Me.Costing_BillTableAdapter1.FillBy(Me.CostingDataSet.Costing_Bill)

Catch r As ConstraintException

Me.CostingDataSet.Costing_Bill.Constraints.Clear()

Me.Costing_BillTableAdapter1.FillBy(Me.CostingDataSet.Costing_Bill)

End Try

Ugh that is bad, like "fixing" a blown fuse by putting a jumper on it. But ya know what? It worked, the second time I try the Fill it doesn't throw.

I think it has something to do with my LanguageTableAdapter; when I took that out and created a separate LanguageBindingSource, the new binding source shows up as a data source for the comboBox, gets filled with all languages, and throws no exceptions. The problem is that this completely disconnects it from my Customer table; it does not show the customer's language and changing it has no effect on Customer.languageID.

Man, I do not understand this stuff. Can someone suggest documentation or a tutorial that shows updating foreign keys from a Forms app?

I figured it out.

When I added the Language table and comboBox to my form, VS helpfully added the languageTableAdapter to my Form_Load method:


But Language is parent to Customer and has a primary key relation. When Customer gets loaded Language hasn't been, and there are no foreign keys for Customer.languageID. How did I fix this? Simply load Language first:


The try/catch method worked because in my catch clause I loaded Language first. Proof again that design-time controls are no benefit if you don't understand the technology.