Major problem involving primary key constraints

[Guest]

I'm writing a Winforms project under Dot Net 2.0. One of my tables has a
multiple field primary key. One of the key segments is a fixed char field
used to determine the order in which the records are displayed in a grid. (S
+ a 2 digit number. This is entirely due to some legacy code restrictions.)
The connection to the database is fairly conventional. I have added an xsd
to the project, dragged the table onto the xsd, and then made use of the fill
and update calls.

Here's the problem: The datagridview used to display the records needs to
also be able to reorder the records. To this end I put a pair of buttons on
the screen and wrote a routine that swaps the key segment of the current grid
row with the one one above or beneath it. (This action takes place entirely
within the attached datatable. The update call comes later.) This works
fine, although even then I had to give one of the records a temporary dummy
key value to avoid a duplicate record error.

When the the update call is finally made, I get the message: Violation of
PRIMARY KEY constraint 'pk_ftutil'. Cannot insert duplicate key in object
'ftutil'.

So apparently the standard update call is not smart enough to handle this
sort of situation. What are my options? Anyone else ever have this problem?

(I'm not sure dropping and restoring the primary key is a good idea in this
context although I'm thinking abou it.)

 

[Guest]

Sorry. Appended too soon. Solved the problem by adding an identity field
and using that as a key component rather than the char field. No problem.

(Still, I wonder what would have been my options if I didn't have the
freedom to change the primary key?)

 

[Patrice]

Not much I'm afraid. The problem is that as each update is sent to the db,
you change the pk of a row but another row still uses the same pk (though it
will be changed at later time). An option could be to use a change table and
to apply all changes at once within a single atomic update statement ???

Anyway a pk should never be updated so your solution would be likely my
personal preference (plus it avoid having the sort order beidng dependent on
something that is used as a pk).