Unique invoice number multi user

[fahedksa]

these good idea and i want ask
1-when there is dublicate can i handle that from visual basic and who i can do these (code)
thank

[cjard]

Your users will never know the difference.

yeah they will.. the program will work more correctly than it does now!

[cjard]

if your invoice numbers must run sequentially (i dont see why) then make one table for each kind of invoice you will handle, and use the auto-numbering field mentioned before

i personalyl think it would be easier just to have a "referenceNumber" field and a "invoiceType" field..

1 inv
2 cod
3 crn
4 cod
5 cod
6 inv
7 owe

that way the tax man is happy that all numbers run sequentially, and you ahve the type of invoice specced separately

[Thread1]

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

Instead of using the recordset object to insert record you may use direct sql statement to avoid this problem. Hence, in the sql statement you can easily determine what is the current maximum value of a certain field (your invoice) in the server with the aggregate function MAX.. The following sample code assumes that the invoice field rank as first in the order of fields..


INSERT INTO <table name>
VALUES ((SELECT CASE WHEN MAX(invoice) IS NULL THEN 1 ELSE MAX(invoice) + 1 END FROM <table name>), <value2>, <value3>, .. , <valueN>)


You could also create an stored procedure for this and make returns the inserted invoice value to refresh the client interface.

[fahedksa]

thank all for your replies.
About make seperate tables for each invoice type its will be make many tables instead of 2 tables can handle all.
-----
Thread 1:
1-If i use direct sql statment its will be 100% that these problem will be solved or still apility that may some times happen?
2-each invoce saved in two table one (Bill) and (BillDetail) who you think that i will do these code?

Thank you all for your help again

[Thread1]

thank all for your replies.
About make seperate tables for each invoice type its will be make many tables instead of 2 tables can handle all.
-----
Thread 1:
1-If i use direct sql statment its will be 100% that these problem will be solved or still apility that may some times happen?
2-each invoce saved in two table one (Bill) and (BillDetail) who you think that i will do these code?

Thank you all for your help again

Ok, I think the problem with the recordset object is that the data retrieve from the server may become outdated when someone (client) updates the data to the server. In this way, the task of generating the invoice number is controlled by the client which is phrone to the problem. Unlike with the direct SQL statement or stored proc (using the connection/command object), you are giving the task to the server to handle the generation of the invoice number for your application which is for me is 100%-efficient.

If you are doing this with two tables, for the connection/command (client) you may wrap the SQL statements inside a transaction to ensure the completeness of the update but no need for the stored proc.

The future of your program is still depend on you, what I got here is just opinions/ideas