2 people create new record in datagridview on dataset, both save, last to save gets primary key violation - fix?
-
I'm certain this is about as noob-ish as it gets, but I'd like to know what typical solutions to this issue are. 2 people (on two computers) get passed their own dataset instances, representing 1 table in the database. They both want to add a row to the db table. They both click on the new row in the datagridview, and it happily inserts a new row on their monitors, with a new Primary Key value (identity column). Problem is, the number is the same on both computers. So the first person can save just fine, but the second person naturally receives an PK violation error when he or she saves. How is this issue supposed to be dealt with (i.e. avoided)? Avoid creating the PK value at all on the client side,allowing the database to create the PK when insert has been performed, and just refresh the datagridview with that value (created by the database)? Some other approach? Thanks for ideas, cdj
-
I'm certain this is about as noob-ish as it gets, but I'd like to know what typical solutions to this issue are. 2 people (on two computers) get passed their own dataset instances, representing 1 table in the database. They both want to add a row to the db table. They both click on the new row in the datagridview, and it happily inserts a new row on their monitors, with a new Primary Key value (identity column). Problem is, the number is the same on both computers. So the first person can save just fine, but the second person naturally receives an PK violation error when he or she saves. How is this issue supposed to be dealt with (i.e. avoided)? Avoid creating the PK value at all on the client side,allowing the database to create the PK when insert has been performed, and just refresh the datagridview with that value (created by the database)? Some other approach? Thanks for ideas, cdj
I generally avoid PK creation on the client-side. The database alone should be responsible for that. However, if that isn't possible, then my next preferred solution is to use a GUID. That way the two machines will generate different numbers. Of course, your DBA may baulk at the use of GUIDs as the data will be inserted randomly (and since the PK is often the clustered index, that can slow the performance of the database if you are doing a lot of inserts or do searches on sequential [or near-sequential] ranges of PK values)
-
I generally avoid PK creation on the client-side. The database alone should be responsible for that. However, if that isn't possible, then my next preferred solution is to use a GUID. That way the two machines will generate different numbers. Of course, your DBA may baulk at the use of GUIDs as the data will be inserted randomly (and since the PK is often the clustered index, that can slow the performance of the database if you are doing a lot of inserts or do searches on sequential [or near-sequential] ranges of PK values)
Yep - as I was typing the question, I realized that the answer was to simply not create the pk client side. :) Thanks! cdj