Multi User Problems

[szlamany]

We have tables in our financial packages that have compound primary keys.

PK is FiscalYr (int) and TransactionNumber (int).

We cannot use IDENTITY (MS SQL version of AUTONUMBER) on the TransactionNumber column.

So instead we have a CONFIG table where we store the "last Transaction" number in use.

In order to grab "three transaction number from this table" we do the following - note we do this in STORED PROCEDURES but you could do it from VB just the same against any type of database.

This is pseudo-code.

1) Get the "last transaction number in use" (Select TransNum From ConfigTable)

2) Remember this original "last trans number" (OrigLastTrans = TransNumb)

3) Add "three" to this value (NewTransNumb = TransNumb + 3)

4) Now attempt the UPDATE - this attempt to UPDATE uses a trick to handle multiuser environments

....Update ConfigTable Set TransNum=NewTransNumb Where TransNumb=OrigTransNumb

5) Now the important part - check the "ROWCOUNT" to see if the update occured - we use @@ROWCOUNT in the STORED PROCEDURE - but most providers/ADO have a method to tell you if a row was updated.

6) If the row was not updated - the row count is zero - then you know that someone else updated before you (the multiuser part of this). If this is true then simply got back to step 1 and do this all over again.

Here is a snippet of the code from MS SQL SERVER (from a STORED PROCEDURE)

RedoB:
	Set @DataValue=(select DataValue from Control_T
				where FiscalYr=@FiscalYr and Item='LAST BATCH')
	Set @NewDataValue=Right('000000'+Cast(Cast(@DataValue as int)+1 as VarChar(6)),6)
	
	Update Control_T
	Set DataValue=@NewDataValue,Tdate=@Tdate
	Where FiscalYr=@FiscalYr and Item='LAST BATCH' and DataValue=@DataValue
	
	If @@RowCount<>1 Goto RedoB