Problem with primary key

[beerbsh316]

I have a table for purchase orders that has a composite primary key of the columns PONumber and Line_Number. I am trying to set it up so that when the user edits a purchase order and deletes a line number from the PO it will reset the other line numbers(ex. User deletes line number 3 then line 4 becomes line 3 line 5 becomes 4 and so on). The problem is whenever I try to do this I get a unique constraint error which should not happen because the row that was removed has already been deleted from the table. I have confirmed that the datatable I am using does not contain a row with the primary key I am changing. So how do I get around this.

 

[ss7thirty]

Are there any foreign keys that may be holding you back from doing this. This may be because you are trying to update records that have dependant records on other tables.

Also, keep in mind that if you are writing an application and this is going to be a large table to update all of those records everytime you delete one could be very time consuming for the application. You should try to create a stored procedure that will do all of this also.

 

[beerbsh316]

Nope no foreign keys. I dont think a stored procedure would work because this need done on the program side since I cant see a way to write a procedure to do what I want. I just dont understand how a row that has already been deleted from the database can cause a unique primary key violation when I change a row to that value.

 

[ss7thirty]

At this point, I would have to see the database structure and the actual data to tell you why this is happening.

 

[Arg81]

It's not the database, it's your app.

You need to go into the DataSet and change the IncrementSeed and IncrementStep of your Primary Key(s) to BOTH be -1

Your app also assigns the IDs. By changing the values to -1 , in essence you are bypassing the apps "intelligence" at defining your ID values, and it will take the correct value when submitted to your DB. THIS ONLY WORKS IF YOUR DB PRIMARY KEY COLUMN IS SET TO BE THE AUTONUMBER / IDENTITY FIELD.

 

[beerbsh316]

Ok its working now thanks for the help

 

[Arg81]

no problem, happens a lot, because VS is trying to control your ID as well (it's trying to be helpful but it isn't!!!)