Since this is in the VB.NET section, I'll give you my answer using code and a sequence table (you could do this within the database with triggers and/or computed columns and remove the need for a sequence table).
Create a table (or if you have a setting table) where you will read and update your next invoice sequence (1,2,3...9999999)
When you save a new invoice, fetch the sequence and increment your sequence table (if you fetch the sequence before you save you can run into concurrency issues if you have multiple users creating invoices). You may want to test before you save that your sequence is unique, if it is not, fetch another (until you have a unique sequence).
value = GetSequence()
While Not IsUniqueInvoice(value)
value = GetSequence()
End While
GetSequence()
would return the next invoice number from your sequence table:
User A: save invoice ... select sequence = 1, update sequence table = 2
User B: save invoice ... select sequence = 2, update sequence table = 3
Let say that User C and User D save at the same time where both users are assigned the same sequence:
User C: save invoice ... select sequence = 3, update sequence table = 4
User D: save invoice ... select sequence = 3, update sequence table = 4
User C's entry would save (since 3 is unique); however User D would fail (would create a duplicate invoice number). That is where the
IsUniqueInvoice
would prevent the duplication.
So you would want User D to fetch another sequence (loop until you have an unused sequence):
User D: select sequence = 4, update sequence table = 5
GetSequence()
would fetch and update the sequence table. You could create a stored procedure that selects and updates the sequence in one call.