Unique invoice number multi user

[fahedksa]

my invoice system used in network (20pc's)
i use visual basic 6 + SQL server2000 +Crytsal Report 9.5
Problem:
Some times when there is many clients save new invoices they get same invoice number and one of them saved and the other after its printed its not found .
may these happen becuase many enteries in same time i use function get the last invoice number from table and add 1 and save it.
So,What best idea to solve these problem and avoid these probelm?
Thanks

[WizBang]

It sounds like the problem is in the way the database is being opened. Try searching for "LockOptimistic" and see what you find.

[George1111]

The important thing is to LOCK the record before reading it
If someone else is currently using that record, then you will get an error, which simply tries to get the record again - hopefully now it has been freed


On Error goto LockErr

Lock InvoiceNumberRecord

InvoiceNumberTable.Read(next)
InvoiceNumberTable.Edit
InvoiceNumber = InvoiceNumber + 1
MyNewNumber = Invoice Number
InvoiceNumberTable.Update

UnLock InvoiceNumberRecord

Exit Sub

LockErr:
Resume Next


not quite sure what type of file you are storing the invoice number in -

hope this helps

THIS IS FROM VB HELP ON "LOCK"

Lock, Unlock Statements Example
This example illustrates the use of the Lock and Unlock statements. While a record is being modified, access by other processes to the record is denied. This example assumes that TESTFILE is a file containing five records of the user-defined type Record.

Type Record ' Define user-defined type.
ID As Integer
Name As String * 20
End Type

Dim MyRecord As Record, RecordNumber ' Declare variables.
' Open sample file for random access.
Open "TESTFILE" For Random Shared As #1 Len = Len(MyRecord)
RecordNumber = 4 ' Define record number.
Lock #1, RecordNumber ' Lock record.
Get #1, RecordNumber, MyRecord ' Read record.
MyRecord.ID = 234 ' Modify record.
MyRecord.Name = "John Smith"
Put #1, RecordNumber, MyRecord ' Write modified record.
Unlock #1, RecordNumber ' Unlock current record.
Close #1 ' Close file.

[jp140768]

Could you not set the invoice number on the SQL table to be an "identity" field, not sure if that's what its called, in Access it would be called an autonumber field - basically one that increments itself on each insert.

[fahedksa]

I will explain the procedure of open and save invoice
first
Myrec1.Open "Select * From Bill order by invoiceno", DB, adOpenStatic, adLockOptimistic


so after that the user will click new invoice and after he finsh he click save and it willbe like these

Myrec1.AddNew
End If
Myrec1!Type = 3
.
.
.
Myrec1!invoiceno= GetMaxNo("bill", 3, "Invoiceno", Cur_Branch)
myrec1.update



---------------------
Function GetMaxNo(TbName$, type1$, Fname$, comb As String) As Long
If LastNo.State = 1 Then LastNo.Close
LastNo.Open "select Max(" & Fname$ & ") From " & TbName$ & " where left(hdate,4)=" & Format(Cur_Date_H, "yyyy") & " and branchid='" & comb & "' and type=" & type1, DB, adOpenDynamic, adLockOptimistic

GetMaxNo = Cur_Branch_Code & "" & Mid(Cur_Date_H, 3, 2) & "000001"
If LastNo.RecordCount > 0 Then
If Not IsNull(LastNo.Fields(0)) Then
GetMaxNo = Val(LastNo.Fields(0)) + 1
End If
End If
Set LastNo = Nothing
End Function
------------------------------------
So, these what i use and its work fine in 90% but if there many users enter new invoice make these probelm

[sotoasty]

jp has the best solution. However, if you are having this problem, I would think there are a few things to alleviate the problem for most of the time.

1. Refresh the recordset right before the addnew.
2. Save just the invoice number first, then check for any duplicates then. Update as needed. You can use a random time interval to check so that 2 computers that do get the same number would check at random times to see if another computer has updated the recordset.
3. Edit and save for the rest of the information.

OR.

Create a separate program to feed you Invoice Numbers. Run this program on the server. Have this program be the only one that generates invoice numbers. I would suggest using winsock and have the clients request an invoice number from the server. The server would then process requests in a sequential order and you would not get duplicates.

Good luck

[fahedksa]

Hay all,
for first soultion from sotoasty & JP may be easier but why i need to make refresh while i get the invoicenumber from the function and in next line i save it to database.
Iand i cant make these field auto increment becuase i have different type of invoices in same table like purchase ,return,..etc and each type has its own number .
I think about something i dont know if its possible or not if make addition field called status and when i save a new invoice i but status value=1 then we have stored procedure when he get new row he check status value if its 1 he create new invoice number ,If status value not equal 1 then do nothing.
and after that i return the value of invoiceno to visual basic .

May be these stubit idea but thats what in my small head now?

[jp140768]

I think one of your problems is in the way you're opening the recordset.

Myrec1.Open "Select * From Bill order by invoiceno", DB, adOpenStatic, adLockOptimistic

adOpenStatic will not show you the updates that are happening around you, you may get better results by changing to adOpenDynamic.

How is the primary key of your table constructed? eg is it invoice type and invoice number? eg cn 1 for credit note 1, in 1 for invoice 1?

If I was doing this, I would have a table with the relevant sequence numbers, and just before I added the record to the main table, I would get the next sequence number, and update it and then do the insert into the main table.

HTH

[sotoasty]

I guess you are correct with your refresh comment, I wasn't paying enough attention to the Get Max code.

So what I would do, is to take a new look at my database.

1. Add an index to the invoice number fields and make sure there are no duplicates allowed. You don't need to have it autoincrement. If a duplicate is attempted to be added, the SQLServer will not allow it.

[fahedksa]

First thank you sotoasty to your replies.
But about dublicate values must thier is in the table becuase i will explan to you
all purchase and sales and reyurn saved in same table and each one of them has type value
purchase type filed=1
sales type filed=2
return purchase type filed=3
.
.
etc.
after many enteres i will find
invoiceno type
1 1
1 2
2 1
3 1
3 2

So, invoiceno field must accept dublicate value

[sotoasty]

A unique index can span multiple fields. Just build an index on invoiceno, type.

[fahedksa]

But is Index only help for fast search only or its gone help here

[sotoasty]

An index does have more control than just for searching. If you create a UNIQUE index, the SQLServer will reply with an error anytime you try to add fields that violate this index rule.

[fahedksa]

My knowledge about index not too much,as i told you have dublicate value for invoiceno field and also for type field how i can make index here to accept thier dublicte values and also is it possible if a user want save invoice and its invoiceno value located for the same type he return and try and try next value for invoiceno field without give error for the user depending on these error

[sotoasty]

you will need to do something like this.

CREATE UNIQUE INDEX MyInvoiceNumber ON tbl_name (invoiceno, type);

Then when you try your .UPDATE you will get an error returned from the server like "unable to insert record, duplicate value for key 1". At that point, you know there is a duplicate, and you can handle the error, by getting a new invoice number and trying the .UPDATE again. Your users will never know the difference.