BindingNavigator's AddNew vs. AutoIncrement DB fields
-
Hi, First, I apologize if this is not the proper forum to ask my question. I have a .NET 2.0 Windows Form App which uses BindingNavigator, BindingSource and DataSet. The user can use the AddNew and Delete buttons of the BindingNavigator to Add/Delete records to the underlying typed DataSet. In my particular case, I'm showing a Work Order record whose PK is also the table's IDENTITY field (MS SQL 2005). My DataSet is setup with negative increment for this PK in order to avoid clashes. So, when the user clicks the AddNew button, he sees a negative Work Order number showing up until the Update method is called on the TableAdapter. This could be a source of confusion in some users, so I'd like to proceed differently. Should I handle this field manually, getting from the DB the highest Work Order number right when the record is created in the DataSet ? If so, how do I take care of concurrency in the case another user also creates a new Work Order before first user actualy commits his new record ? Or should I simply call Update right away when the record is created ? I assume this is a very common issue in DB programming. Thank for any tips. Luc Morin
-
Hi, First, I apologize if this is not the proper forum to ask my question. I have a .NET 2.0 Windows Form App which uses BindingNavigator, BindingSource and DataSet. The user can use the AddNew and Delete buttons of the BindingNavigator to Add/Delete records to the underlying typed DataSet. In my particular case, I'm showing a Work Order record whose PK is also the table's IDENTITY field (MS SQL 2005). My DataSet is setup with negative increment for this PK in order to avoid clashes. So, when the user clicks the AddNew button, he sees a negative Work Order number showing up until the Update method is called on the TableAdapter. This could be a source of confusion in some users, so I'd like to proceed differently. Should I handle this field manually, getting from the DB the highest Work Order number right when the record is created in the DataSet ? If so, how do I take care of concurrency in the case another user also creates a new Work Order before first user actualy commits his new record ? Or should I simply call Update right away when the record is created ? I assume this is a very common issue in DB programming. Thank for any tips. Luc Morin
There is no way of knowing for sure what id a record will get until it's created. If you use any method to guess the id (e.g. from the previous records), you might get it right most of the time, but not all the time. If you handle the id manually, you still won't get it right all the time. Sometimes some other user will have created a record, so the insert will fail. Then you would have to handle the error condition, give the id a new number and try to save it again. This might be acceptable, but one big drawback is that the more users you have, the more frequently this will happen. --- b { font-weight: normal; }
-
There is no way of knowing for sure what id a record will get until it's created. If you use any method to guess the id (e.g. from the previous records), you might get it right most of the time, but not all the time. If you handle the id manually, you still won't get it right all the time. Sometimes some other user will have created a record, so the insert will fail. Then you would have to handle the error condition, give the id a new number and try to save it again. This might be acceptable, but one big drawback is that the more users you have, the more frequently this will happen. --- b { font-weight: normal; }
Guffa wrote:
There is no way of knowing for sure what id a record will get until it's created. If you use any method to guess the id (e.g. from the previous records), you might get it right most of the time, but not all the time.
Yes, this is exactly my concern. How do most apps handle this situation ? I was thinking of making the Work Order TextBox invisible until the user actuallty saves the record. What do you think ? Regards, Luc Morin
-
Guffa wrote:
There is no way of knowing for sure what id a record will get until it's created. If you use any method to guess the id (e.g. from the previous records), you might get it right most of the time, but not all the time.
Yes, this is exactly my concern. How do most apps handle this situation ? I was thinking of making the Work Order TextBox invisible until the user actuallty saves the record. What do you think ? Regards, Luc Morin
You could make a call to a stored procedure that just creates a blank record in the table. The autoincremented value (ID) gets created, then the SP returns just that ID. You're code can then show a blank edit form so all the details can be filled in. Then the record can be written back to the table with the "checked-out" ID number. RageInTheMachine9532 "...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome