Multi User Problems

[Xium]

i have a table name BILLS. main field is BillNo which contain bill number. when user want to creat new bil, program select MAX(BillNo) from BILLS table, New BillNo will be MAX(BillN0) +1 , then program add new record in this table with new BillNo.. now problem is this , we have 20 user who access same table at the same time for new bill. now there's heavy chance to get same MAX(BillNo). but we dont wanna BillNo duplication. so how we resolve this issue ?

[dee-u]

How about using an Autonumber or identity field?

[Xium]

we cant use autonumber . bcz a bill may contain more than 1 items. so program place same bill no with each item of .. like there's bill of monitor + keyborad + mouse and max bill no is 100 then 101 will be placed 3 times in BILLS table with relative items ...

[Hack]

Please keep your thread titles professional.

What database are you using?

[jeroen79]

The usual method is to use two tables:
tbl_Invoice and tbl_InvoiceLine

Each user would then create one (autonumbered) tbl_Invoice record and for each item on the bill an (also autonumbered) tbl_InvoiceLine record.
The tbl_InvoiceLine records have a field that links to the associated tbl_Invoice record.

[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