Data Relations are not working correctly...

[cjard]

... I have a set of tables such that:

[bankCustomer.accountSortcode]M = 1[banksTable.sortcode]

[banksTable.addressID]1 = M[addressesTable.addressID]


a bank is identified by its sort code, many customers may have the same sortcode indicating they are a customer of the one bank hence the relation is M:1
the addresses table is used by several other tables who also need to store addresses, and additionally we keep every known address so if a bank moves, it gets a new address detail, but the same id. this table has a compound primary key of ID plus a date field of ewhen the address came into being. hence the relationship is... bank 1:M address



in my dataset these are represented a little differently. i have chosen to represent them in this way:


[bankCustomer.accountSortcode]F = 8[banksTable.sortcode]

[banksTable.addressID]F = 8[addressesTable.addressID]


where F is the little key symbol, and 8 is the little infinity symbol. I.e. i have reversed the relationship of the bankCustomers and banks.
this is because i want to have the customers screen driving the banks table.. i will type a sort code into the customer screen, and as soon as it reaches 6 digits i will:
look up the banks table for that sortcode. there should be either 1 or 0.
if there is 1 then the bank is known. the address id will be read and the addresses known for the bank will be loaded.


so far IO have gotten to the stage of:
clearing the customer datatable
accepting the changes
adding a new row with blank empty strings in place for all the primary key values in the customer datatable
implementing the keyhandler such that when the sort code is typed in, the banks table is filled with a valid bank (i am typing a valid sort code)
and following on from a above the addresses are populated.

herein lies my problem.. the fields on the form are not showing anything for the bank or address data


-

as a test i put 3 datagrid on the form and bound them directly to the tables the form uses

i put another 2 and bound them to the relations between the tables

-

the 3 that are bound to the datatables show exactly the data that i want them to show. the two that are bound to the relations, are blank


-

at what part has my process failed such that:

I need to disable constraints on my dataset immediately before i fill the bank data in (after getting the sortcode from the user) otherwise it complains
When i disable constraints, the data i want to see is loaded into the tables, but the relationship is broken such that i cannot see the data

Is it because I have typed into the textbox, and the underlying datatable is not committed?

 

[cjard]

*thud*

yes, it was - after just advising someone else to call endedit before they send database updates (to move proposed values into current) i am caught out by the same thing

I'm still curious why i need to disable constraints before filling my banks table

reminder, the process is:

<user types 6 digits into text box bound to customertable.sortcode>

        If ACCOUNT_SORT_CODETextBox.Text.Length <> 6 Then Exit Sub

        Try
            DDH_BANKEESBindingSource.EndEdit()

            HubDSInstance.EnforceConstraints = False

            DDH_BANKSTableAdapter.FillBySortCode(HubDSInstance.DDH_BANKS, DirectCast(DDH_BANKEESBindingSource.Current, DataRowView).Row("BANK_SORT_CODE").ToString())
            If HubDSInstance.DDH_BANKS.Rows.Count = 1 Then
                DDH_ADDRESSESTableAdapter.FillByAddressRef(HubDSInstance.DDH_ADDRESSES, HubDSInstance.DDH_BANKS.Rows.Item(0).Item("ADDRESS_REF").ToString())
            End If

            'HubDSInstance.EnforceConstraints = True

        Catch ex As Exception
            MessageBox.Show("Error: " & ex.Message)
        End Try

why do i need to disable constraints?

 

[cjard]

we can cancel this one too...

for some reason the column in the dataset had a maxlength of 9 and the column in the db has a max of 30..

Royal Bank of Scotland, is a bit longer than 9..



i have a final question - why do i solve my problems 5 minutes after posting them, and does anyone mind be using this forum as my cardboard analyst?



heres a tip,. so some good can come out of this:

if you get a "failed to enable constraints" error (hopefully you used a try/catch and caught this constraintException) on your dsataset (in this example, i'll use HubDSInstance, as it is the name of my dataset) then in your immediate window you can find the problem like i did.. here is the copy of what i wrote in my immediate window:

?hubdsinstance.HasErrors
True
?hubdsinstance.DDH_BANKEES.HasErrors
False
?hubdsinstance.DDH_BANKS.HasErrors
True
?hubdsinstance.DDH_BANKS.GetErrors
{Length=1}
(0): {DDHub.HubDataSet.DDH_BANKSRow}
?hubdsinstance.DDH_BANKS.GetErrors(0).ItemArray
{Length=3}
(0): "208526"
(1): "Royal Bank Of Scotland"
(2): "00000133"
?hubdsinstance.DDH_BANKS.GetErrors(0)
{DDHub.HubDataSet.DDH_BANKSRow}
DDHub.HubDataSet.DDH_BANKSRow: {DDHub.HubDataSet.DDH_BANKSRow}
HasErrors: True
ItemArray: {Length=3}
RowError: "Column 'BANK_NAME' exceeds the MaxLength limit."
RowState: Unchanged {2}
Table: {DDHub.HubDataSet.DDH_BANKSDataTable}

i wish that was part of the constraintexception message...