Multi User Saving

[computermat]

Hi, i have built a booking system that is used simultaniously by 4 users.

Interactions with SQL Server
Database is downloaded onto a client machine, new row is added to this then the primary key that is set as Booking ID is bound to a label on my main form.

After the customer and booking details are entered the data is sent back to the server and the label that has the booking id in it is used as a parameter for printing reports on the next form.

Problem
The problem is that the booking ID is being duplicated on client machines becuase the booking being made with this ID number has not been completed and therfore the server does not know it exists so other users are getting it.

How could i get round this?
I have had a thought of downloading the database and saving the new details all as one transaction but then the data wouldn't be saved that has been entered in the binded textboxes.

Any help would be appreciated

 

[cjard]

the database should calculate the PK at the time of SAVING the new record, and then pass back the generated ID to the client, if it's even interested

 

[cjard]

Database is downloaded onto a client machine

ps; I always wondered about the mindset of someone who downlaods a database from the server to the client; why do you do this? What's even the point of having a quad cpu server with 32 gigs of ram if youre going to downlaod the entire database into a low spec client whenever you want to work with it?

 

[computermat]

Hi, thanks for your reply

i might of phrased downloading the database wrong. I get the data as a select statement and show it in a data grid view, then save the changed data back to the server with the tableadapter.

So when the data is saved should i refresh the label that is holding the ID number. Wouldnt this give the wrong id number.

PS
When i start this procedure i first get the data then add a new row. When saving the data i use BookingsBindingSource.endedit and then bookingstableadapter.update(me.TO_BOOK.bookings)

 

[cjard]

So when the data is saved should i refresh the label that is holding the ID number. Wouldnt this give the wrong id number.

Hence the reason why INSERT statements are different to UPDATE statements
When the record is new, you generate the ID. when youre updating you use the PK that insert generated..

PS
When i start this procedure i first get the data then add a new row.

Why would you do this. If youre adding a row, add a row, dont download rows first!

When saving the data i use BookingsBindingSource.endedit and then bookingstableadapter.update(me.TO_BOOK.bookings)

Yes, and that process loops the entire table and looks at the rowstate.. if it is added, insert is called, modified, update is called, deleted, delet is called

Update() is NOTHING to do with sql UPDATE. Update() should be called Persist() or SaveToDB() or LoopAllRowsInTHisTableAndSendThemToTheDBByWhateverRelevantSQLStatement() or something

(but the latter is a bit wordy)

 

[jmcilhinney]

Here's how auto-generated IDs work. You have a DataTable locally. When you add a new row to that DataTable it generates a temporary ID. If the table is empty then it starts at the seed value, otherwise it will increment from the last ID used in the table, even if the row that used it has been deleted. You can safely use this ID to refer to the row locally, even as a foreign key in child tables.

When you save your data to the database a permanent ID will be generated with no regard for what the temporary value was. The record is now inserted with this final value. By default ADO.NET will refresh the DataTable to update all values generated by the database, including IDs and default values. You can also configure your DataRelations to automatically propagate the final ID values to any related child DataTables. When you then insert the child data they will have the correct foreign key values.

As cjard says, you should simply wait until you save the data to get your actual ID value, at which point you can issue it to the customer. If, for some reason, you cannot do that, which is possible, then you simply can't use an auto-generated ID as the customer's booking number.

You will have to add another column for the booking number and generate it before saving the data. You might use a GUID or perhaps have the database maintain another table containing just the last booking number. You could add a sproc that incremented the number and returned it. Now you're guaranteed that every request will get a unique booking number no matter how many clients there are. If any bookings are cancelled before saving that booking number will simply be discarded.

 

[cjard]

If, for some reason, you cannot do that, which is possible, then you simply can't use an auto-generated ID as the customer's booking number.

In oracle, like JMc says in the ensuing paragraph, you'd do that in a stored procedure, possibly based on a sequence.. You'd pull the ID when you clicked the Add button and that ID would be reserved for that application. If for any reason that app was thrown away, you WOULDNT normally go to the effort of trying to return the ID for recycling, you'd just accept the loss of it. For some people this is a huge issue; they cant stand to have nonsequential customer references or something and they will waste hours working out clever code to try and reuse the blanks.. The added complication usually causes more problems than it solves. If you have to have sequential IDs, calculate them on save, not new app