Printable View
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
It sounds like the problem is in the way the database is being opened. Try searching for "LockOptimistic" and see what you find.
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.
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.
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
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
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?
I think one of your problems is in the way you're opening the recordset.
adOpenStatic will not show you the updates that are happening around you, you may get better results by changing to adOpenDynamic.Quote:
Myrec1.Open "Select * From Bill order by invoiceno", DB, adOpenStatic, adLockOptimistic
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
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.
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
A unique index can span multiple fields. Just build an index on invoiceno, type.
But is Index only help for fast search only or its gone help here
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.
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
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.
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
yeah they will.. the program will work more correctly than it does now!Quote:
Originally Posted by sotoasty
:)
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
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..Quote:
Originally Posted by fahedksa
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.
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.Quote:
Originally Posted by fahedksa
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:D