Controls not updating properly when ConstraintException occurs

[cjard]

Suppose I have a form where a control is allowed to edit the primary key of some datatable. Binding is through a bindingsource.
You can change the value so that it collides with another primary key already present in the table. As soon as EndEdit() is called (however so), a ConstraintException occurs and the proposed changes are destroyed.

This in itself is not a problem; the problem is that the typing I did in the controls, is still there.. If I edited the PK from "one" to "two" but "two" was already present, then a ConstraintException has occurred and the datatable still says "one" but the control now says "two".
There are two courses of action here:

Reset the controls to show the "last known good" values - i.e. the Current values in the datarow. This would basically involve having the controls re-query/refresh the underlying data. How do we do this? (binding is normally two way - if I put a button on the form that sets a column in the the current row to a new value, then the change is immediately reflected in the textbox. In case of ConstraintException, it seems the binding is not two way)

Be more clever; return the user to the state before EndEdit was called - the controls still show the new values, and ask the user to pick a different PK. How do we do this?

 

[jmcilhinney]

Presumably you would handle the ConstraintException and prompt the user then.

 

[vis781]

Could all this not be done it the 'RowChanging' event? As i understand it calling EndEdit raises the RowChanging event so the validation can be done there.

 

[cjard]

Presumably you would handle the ConstraintException and prompt the user then.

The ConstraintException arises whenever EndEdit is called. Several things may cause EndEdit to be called, including scrolling the data using a bindingnavigator's positional controls. Given that there may potentially be hundreds of entry points to calling of EndEdit even on a single form, trying and catching them all is hard. I CAN do it, however. Catching the error is not the problem
I can even use Regexes to pick apart the exception message (in the light of lacking any other reasonable way to do it) and determine which columns and which values violated the constraint, the big problem is what to do next:

All the controls still show the new data, the underlying data is still the old data. Either the controls must be reverted, or the underlying data must be synced to the controls. There is no suitable method I can see of doing either. Does anyone know?

Could all this not be done it the 'RowChanging' event? As i understand it calling EndEdit raises the RowChanging event so the validation can be done there.

I'm afraid not: Calling EndEdit with violating data causes a ConstraintException to arise before RowChanging is called. I'm currently discussing the wisdom of this with a few MSMVPs and the responses I'm getting are of the form:

If you have constraint checking you dont need to do your own validation in rowchanging. If you have constraint checking its quck and easy to set up and you can be notified of the errors but there is little you can do about them. If you implement your own validation it's a disproportionately huge amount of work but you can achieve the result you want.

Both approaches are equally useless at present. I'm waiting for some gem of wisdom that reveals Microsoft's secret way to make one of the routes viable. Right now, it feels the most sensible thing is do disable constraints entirely, and do validation on the database only - let the users bang multiple records with the same PK into the database; the data updates are performed by merge statements so PK violation is impossible and pretty soon all values will be verified by regex and rejected by the database if they are crazy. Part of me shakes its head at this approach; i prefer multi-tier security and validation but MS is making it just too hard to do right now

 

[vis781]

Ok, i don't even know if this will work as i've just been thinking about how you can intercept the changing of a datatable. So here are my thoughts...

The DataTable implements IBindingList, in the IBindingList interface there is an event called 'ListChanged' so wouldn't it be possible to add a handler to this event and trap the change before it actually occurs? Mmmm dunno, long shot i know. The code would go something like....

AddHandler DirectCast(DataTable, IBindingList).ListChanged, 
New ListChangedEventHandler(AddressOf 'Some Method With 
The Appropriate Signature i.e (ByVal sender As Object, ByVal e As ListChangedEventArgs)

Like i say a really long shot.

 

[cjard]

I have eventually determined that the ColumnChanging event fires quite reliably, though the RowChanging never gets to fire before the exception occurs. I am, however, rather unwilling to leverage this, as ColumnChanging fires once for every cell in a filling datatable; I dont want the performance hit, so i've decided to remove the constraints from the DataSet entirely, disable them and let the back end stored procedures responsible for inserting data worry about the constraints.

If anyone can see a flaw in this logic (other than lack of fine grained control) then I'd love to hear it