Strange behavior on calls to EndCurrentEdit

[Greg]

Because of a poor db design that supports the FoxPro app we are rewriting,

we are stuck working with an odd relation.

The parent table (in sql server 7.0) is a Member table and the child table

is an Address table. They link on Member.HomeAddress = Address.Control.

So, Member.HomeAddress = Address.Control, 1:1, in Sql Server.

In the client code, we have 2 DataTables (Member and Address) with a

DataRelation between them (Member.HomeAddress, Address.Control).

Calls to AddNew on the DataRelation cause the Address.Control DataColumn's

value to be set to the Member.HomeAddress's DataColumn's value.

So, if the Member.HomeAddress DataColumn's value = "123" and we call AddNew

on the DataRelation, the new Address DataRow's Control DataColumn's value

gets set to "123". I assume this happens because a DataRelation is 1:many

and it assumes there will be many Addresses with a control value of "123"

for the one Member record with that value in it's HomeAddress field.

I assume a ConstraintException is not thrown at this point since the new

Address DataRow has not been added to the row's collection.

Now, if we call EndCurrentEdit on the DataRelation, we get a

ConstraintException since "123" is already in the Address DataTable.

To get around this, we change the Member.HomeAddress DataColumn's value to a

dummy value and we also change the new Address DataRow Control DataColumn's

value to the same dummy value. Lets say the dummy value is "new". So the

Member.HomeAddress is set to "new" and the new Address DataRow Control

DataColumn's is set to "new". So, at this point the Member DataRow is

Modified and we have not added the Address DataRow to the rows collection.

Now here is where things get strange. We call EndCurrentEdit on Member and

the DataRelation. As soon as EndCurrentEdit executes, the new Address

DataRow gets added to the rows collection as expected. The modifed Member

DataRow's HomeAddress field is still storing "new" and we also have an Added


Address DataRow whose Control field is storing "new". All good so far.

However, for some unknown reason to me, the Member DataRow is relating to

the Address DataRow is was relating to prior to the call to AddNew ("123")

on the DataRelation. After the call to AddNew, the controls reflect the

values for the new DataRow (control = "new"). However, after the call to

EndCurrentEdit, the controls reflect the values for the UnChanged "123"

Address DataRow. The Member DataRow's HomeAddress field is still showing

"new" so I would assume the controls would show the values for the Address

DataRow whose control field = "new". But they do not, they show the values

for the Address DataRow whose control field = "123" (the 'old' Address

DataRow).

Thinking this was related to the DataRelation constraint, I set

EnforceConstraints to False. Same results though.

Why is this happening?

 

[Kathleen Dollard]

Greg,

I was amazed by how difficult it was for me to read this format. Please copy
into Notepad, then here if you have problems with formatting.

From skimming this, I think we'll need to see some code. Although first,
check your code for AcceptChanges and remove it (at least temporarily) if
you find it. If you think you need AcceptChanges, please post a question on
that. It is commonly misused.

 

[Greg Robinson]

Our issue is this:

Our Detail combobox is bound to a RelatedView.

Changes to the selection in the Master combobox cause the Detail
combobox to be bound to a new RelatedView.

When the Detail combobox binds to the new RelatedView, the ValueMember
does not change.

So, even though the Detail combobox is displaying new selections, the
ValueMember is still the same.

Now, if a user changes the Master combobox selection, the Detail
combobox binds to a new RelatedView, the selections in the Detail
combobox change. If the user likes the selection currently displayed in
the Detail combobox (without having to click on the Detail combobox),
they hit Save. We call EndCurrentEdit and do a handoff to our DAL.

Since the ValueMember in the Detail combobox did not change, we save a
new value for the Master combobox but not for the Detail combobox, so
now the data is bad in sql server.

I tried changing the Detail Combobox's ValueMember in code when the
Master combobox SelectedValue changes. This did not work.

I tried changing the SelectedValue for the Detail combobox when the
Master combobox SelectedValue changes. This did not work.

So, what can we do?

 

[Greg Robinson]

Please ignore my last reply, wrong post.

 

[Greg Robinson]

Because of a poor db design that supports the FoxPro app we are
rewriting,
we are stuck working with an odd relation.

The parent table (in sql server 7.0) is a Member table and the child
table
is an Address table. They link on Member.HomeAddress = Address.Control.

So, Member.HomeAddress = Address.Control, 1:1, in Sql Server.

In the client code, we have 2 DataTables (Member and Address) with a
DataRelation between them (Member.HomeAddress, Address.Control).

Calls to AddNew on the DataRelation cause the Address.Control
DataColumn's
value to be set to the Member.HomeAddress's DataColumn's value.

So, if the Member.HomeAddress DataColumn's value = "123" and we call
AddNew
on the DataRelation, the new Address DataRow's Control DataColumn's
value
gets set to "123". I assume this happens because a DataRelation is
1:many
and it assumes there will be many Addresses with a control value of
"123"
for the one Member record with that value in it's HomeAddress field.

I assume a ConstraintException is not thrown at this point since the new
Address DataRow has not been added to the row's collection.

Now, if we call EndCurrentEdit on the DataRelation, we get a
ConstraintException since "123" is already in the Address DataTable.

To get around this, we change the Member.HomeAddress DataColumn's value
to a
dummy value and we also change the new Address DataRow Control
DataColumn's
value to the same dummy value. Lets say the dummy value is "new". So
the
Member.HomeAddress is set to "new" and the new Address DataRow Control
DataColumn's is set to "new". So, at this point the Member DataRow is
Modified and we have not added the Address DataRow to the rows
collection.

Now here is where things get strange. We call EndCurrentEdit on Member
and
the DataRelation. As soon as EndCurrentEdit executes, the new Address
DataRow gets added to the rows collection as expected. The modifed
Member
DataRow's HomeAddress field is still storing "new" and we also have an
Added
Address DataRow whose Control field is storing "new". All good so far.

However, for some unknown reason to me, the Member DataRow is relating
to
the Address DataRow is was relating to prior to the call to AddNew
("123")
on the DataRelation. After the call to AddNew, the controls reflect the
values for the new DataRow (control = "new"). However, after the call
to
EndCurrentEdit, the controls reflect the values for the UnChanged "123"
Address DataRow. The Member DataRow's HomeAddress field is still showing
"new" so I would assume the controls would show the values for the
Address
DataRow whose control field = "new". But they do not, they show the
values
for the Address DataRow whose control field = "123" (the 'old' Address
DataRow).

Thinking this was related to the DataRelation constraint, I set
EnforceConstraints to False. Same results though.

Why is this happening?

 

[Kathleen Dollard]

Greg,

I think we'll need a dozen or so lines of code to pinpoint the problem.

Since the ValueMember in the Detail combobox did not change, we save a
new value for the Master combobox but not for the Detail combobox, so
now the data is bad in sql server.

Of course the value member doesn't change. It's a property name like
"LastName". Are you setting it to something other than a business object
property, a column name or similar?

 

[Greg Robinson]

1) We call AddNew on the DataRelation
Me.BindingContext(DataSet, "ParentTable.RelationName).AddNew()
This creates a New DataRow in our Address DataTable (the child in the
relation)

2) We set a couple of values in the New Address DataRow (not changing
the DataRelation column values)

3) We set the Text property of a textbox control that binds to the New
Address DataRow's DataRelation column
Me.txtHAddrControl.Text = "nhome"
This textbox's Text property is bound to the Address Datatables
'control' field which is the DataColumn used in the DataRelation.
We also manually change the Parent Datatables's DatRelation column value
to "nhome" ( new home address)

4) Users enter values for the new Address DataRow

5) We call EndCurrentEdit on the Parent Datatable and the Datarelation.
We have a modifed Parent DataRow with the value "nhome" in it's
DataRelation column and we have an Added DataRow in the Address
DataTable with a value of "nhome" in it's DataRelation column.

6) In the GUI, the Parent DataRow is now relating back to the Address
DataRow it was relation to prior to the call to AddNew on the
DataRelation.

 

[Greg Robinson]

Kathleen,

What is the best way to code this? We do not have a 1:many relation
between our Member and Address Datatables. It is really a 1:1.

There are four fields in our Members DataRow. Each field represents one
of 4 possible address a member can have. These fields store a 'control
number', which is the key to our address DataTable. So, if my home
address field is storing "123456", then there is a DataRow in our
Address DataTable whose control field = "123456".

In the case described, a member does not have a home address and the
user wants to add one. So, we call AddNew on the DataRelation, the
controls blank out, the user enters data and then we call EndCurrentEdit
and validate the data.

What is happening is the data will fail validation so we notify the
user. Well, now the user sees the data that was present in the controls
prior to selecting 'New' ("123456" from the Address DataTable). Which
tells us, the member datarow is relating back to the previous address
datarow, no the Added Address DataRow.

The new one is in the Address DataTable marked as Added. So, how do we
relate our members datarow to this Added Address DataRow?

Second question, is is normal for the Address Datarow's (in our example)
control field to be set to the Member's address field on a call to
AddNew? If the member's address field is storing "123456" and we call
AddNew on the DataRelation, the Addresses DataRow's control field is
automatically set to "123456". We do not do this, so i assume because
of the constraint this is getting set for us?