Constraint Error in Checkpointing

Ouch, identity column idea didn't work. I guess I'm going to have to play the SortOrder game after all.

Mark, if you have an example, I'm interested. :)

-AJ

Hi AJ,

One thing I have done in the past when altering the database isn't feasible:

Add a property to your class called SortOrder, which is initially set to the primary key, when the users move things around it is only altering this property that isn't persisted. Would that work?

I can post an example if that would make more sense.

Mark

That's unfortunate.  I can't find a solution that will handle both save and rollback without an error when either PK swapping or delete/create is used.

It's unfortunate that you can't change the data model, since a non-key property like "SortOrder" would certainly make things easier.

I have a tough problem, and I desperately hope you can help me.

I am writing a form where users can edit a report and its parameters. fReport is an entity that can have zero or more fReportParame entities. The primary key of fReport is ReportName. The primary key of fReportParam is (ReportName,UnqiueID). Note: UniqueID, despite its name, is only unique for a given ReportName.

The calling form does a BeginCheckpoint and then does either a SaveChanges or a RollbackCheckpoint based on whether the edit form returns OK or Cancel.

The edit form contains contains data bound report fields as well as an editable grid that I fill with the parameter entities for the particular report. The user can add or remove these parameters. They can also use Up/Down buttons to move the selected row up or down in the grid. Here is where it gets tricky. The grid is sorted by the UnqiueID column. When Up or Down is pressed, I swap the UnqiueID value of the selected entity with the one above or below it accordingly.

Now supposing the parameter grid looks like this:

UnqiueID  ParamName
0         A
1         B
2         C

If I select the first row and click the Down button, the IDs swap and I get this:

UnqiueID  ParamName
0         B
1         A
2         C

Great. However, when I go to save it, it tries to change the UnqiueID of B to 0, which causes a duplicate error because A hasn't been updated and is still 0 in the database.

No problem. I work around that. Since there are never more than 10 parameters for a report, I solve this by adding 100 to the UnqiueID of all the parameters before saving, then subtracting 100 from all of them and saving again. It works, I no longer get a database error during saving. However, I now get a similar problem with these steps:

1. edit a report
2. swap the UnqiueIDs of 0 and 1
3. hit OK which closes the edit window and performs the SaveChanges (with the +/- 100 game)
4. edit the same report again
5. swap the UnqiueIDs of 0 and 1
6. swap the UnqiueIDs of 1 and 2

It all works until step 6 at which point I get the error (included at bottom, note that 'asdf' is the report name I'm testing with). It appears to have something to do with the checkpointing.

Here is the code I use for the Down button (obviously, the Up button is quite similar):

      'find the two rows involved
      Dim param1 As fReportParam = mReport.fReportParams(mParametersGridView.GetDataSourceRowIndex(mParametersGridView.FocusedRowHandle))
      Dim param2 As fReportParam = mReport.fReportParams(mParametersGridView.GetDataSourceRowIndex(mParametersGridView.FocusedRowHandle + 1))
      Dim param1ID As Integer = param1.UniqueID
      Dim param2ID As Integer = param2.UniqueID
     
      'swap their unique IDs
      param2.UniqueID = 99 'otherwise next line gets rejected due to unique constraint
      param1.UniqueID = param2ID '!!!THIS IS WHERE THE ERROR OCCURS!!!!
      param2.UniqueID = param1ID

As you can see, I am guarding against having 2 parameters with the same UniqueID at any point in time. However, I think that the checkpointing code is storing copies of the original values somewhere and when I did step 5 it stored copies of 0 and 1, and when I did step 6 it tried to store copies of 1 and 2 and errored because 1 was changed once previously and it is supposed to be unique. Am I on track? If so, do you have any ideas how I can work around this? I realize that changing the PK of an entity is troublesome, but I don't have the luxury of changing the data model. :(

Here is the error: