March 15, 2011 at 12:18 am
Good afternoon,
I have problem with my current development on a billing system which is in multi-user environment. I have develop its client base in Ms Access. We links all Ms SQL Server table to Ms Access client.
All Ms Access client form, can issue an invoice number. Those number can never delete, only cancelled can be applied for. Once, user click to print invoice, the program will call the store procedure UDP_GETINVOICENUMBER
to get its latest value.
I have tried to test my program in 4 different PC. Each PC is responsible to build 1000 invoice on a button cmdTest_Click()
Public Sub TimeDelay(ByVal nSeconds As Long)
Dim nStart As Long
nStart = Timer
Do Until CLng(Timer - nStart) > nSeconds
DoEvents
Loop
End Sub
Public Function GetFRNumber(strFromType As String) As Long
Dim objconn As ADODB.Connection
Dim objData As ADODB.Recordset
Dim prm As ADODB.Parameter
Dim cmd As ADODB.Command
Dim strFormNo As Long, stProcName As String
Dim CounterStep As Integer
CounterStep = 0
TryAgain:
On Error GoTo Err_GetFRNumber
Set objconn = New ADODB.Connection
'Getting Connection String
If (Trim(GBL_STRCONN) = "") Then
GBL_STRCONN = GetConnConfig()
End If
objconn.ConnectionString = GBL_STRCONN
objconn.Open
'Append parameters
Set cmd = New ADODB.Command
stProcName = "UDP_GETINVOICENUMBER" 'Define name of Stored Procedure to execute.
cmd.CommandType = adCmdStoredProc 'Define the ADODB command
cmd.ActiveConnection = objconn 'Set the command connection string
cmd.CommandText = stProcName 'Define Stored Procedure to run
With cmd
Set prm = .CreateParameter("@form_type", adVarChar, adParamInput, 3, strFromType)
.Parameters.Append prm
Set prm = cmd.CreateParameter("@form_number", adBigInt, adParamOutput, 20)
.Parameters.Append prm
.Execute
' Retrieve form Number
strFormNo = Val(.Parameters("@form_number"))
End With
objconn.Close
Set cmd = Nothing
Set objconn = Nothing
'Return Calculation value
GetFRNumber = strFormNo
Exit_GetFRNumber:
Exit Function
Err_GetFRNumber:
If CounterStep < 10 Then
Call TimeDelay(1) 'Delay for 1 second
CounterStep = CounterStep + 1
GoTo TryAgain
Else
GetFRNumber = -1
MsgBox "GetFRNumber", Err.Number, Err.Description
Resume Exit_GetFRNumber
End If
End Function
private Sub cmdTest_Click()
For j = 1 To 1000
lnginvoicenumber = GetFRNumber("EXP")
strSQLQuery = "UPDATE dbo_tblEXCG SET EXCGID=" & lnginvoicenumber & " " _
& "WHERE(EXCGID=" & strRandomCode & ")"
DoCmd.RunSQL strSQLQuery
strSQLQuery = "UPDATE dbo_tblEXCGD SET EXCGID=" & lnginvoicenumber & " " _
& "WHERE(EXCGID=" & strRandomCode & ")"
DoCmd.RunSQL strSQLQuery
Next j
End Sub
Table : [tblFormRunningNumber]
FTypechar(3)
FNumberbigint
LastUpdateddatetime
CREATE PROCEDURE [dbo].[UDP_GETINVOICENUMBER]
@form_type as char(3),
@form_number AS BIGINT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRANSACTION
--the forces the record to be locked by doing an arbitrary update to the row
UPDATE tblFormRunningNumber
SET LastUpdated=CURRENT_TIMESTAMP
WHERE (FType=@form_type)
--Make a selection to last number form storage + 1
SELECT @form_number=FNumber+1
FROM tblFormRunningNumber
WHERE (FType=@form_type)
--Update data to form number running last number table
UPDATE tblFormRunningNumber
SET FNumber=@form_number,LastUpdated=CURRENT_TIMESTAMP
WHERE(FType=@form_type)
COMMIT TRANSACTION
END
With the current implementation, I still found duplicate invoice number once the test on the 4 PCs is completed.
Is there any possibility to fix the problem of duplicate invoice number?
Best regards,
Veasna
March 15, 2011 at 8:00 am
If you make the Invoice Number field (column in SQL Server) the primary key for the table, or at least add a unique index using that field, you will prevent duplicates, but you will get an error when you try to save the record. Have you considered making that field an auto-number (identity in SQL Server) field so that SQL Server assigns the number?
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
June 11, 2011 at 12:14 pm
You should try and avoid assigning the number based on a value on a table and use the identity if possible.
If you go with the update table methodology you need to consolidate your code and include in within a transaction.
It you go with the table method as opposed to an identity you will not get duplicates but you run the risk of blocking.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 20, 2011 at 8:29 am
I agree, the first thing you must do is set a unique index on the invoice number column, even if it is the surrogate key and not the Primary Key. (Yes, I believe in surragate keys, but that's another topic.)
Another thing to try is to move this process to T-SQL and embed it in a transaction. That way you could create a cursor to loop through the records and build the invoice numbers and be guaranteed that noone else is trying to retrieve invoice numbers simultaneously.
Ultimately, you should move this process to SQL and use the T-SQL functions: DENSE_RANK() and ROW_NUMBER(). I use them to auto-build purchase orders, invoices, work orders, etc. These functions can be used to generate the sequential invoice numbers when creating batches of data without the need for client-side looping or server-side cursors.
June 22, 2011 at 12:01 pm
Robert Magrogan (6/20/2011)
Another thing to try is to move this process to T-SQL and embed it in a transaction. That way you could create a cursor to loop through the records and build the invoice numbers and be guaranteed that noone else is trying to retrieve invoice numbers simultaneously.Ultimately, you should move this process to SQL and use the T-SQL functions: DENSE_RANK() and ROW_NUMBER(). I use them to auto-build purchase orders, invoices, work orders, etc. These functions can be used to generate the sequential invoice numbers when creating batches of data without the need for client-side looping or server-side cursors.
Can you think of a scenario where you approach would not work or be a problem? 🙂
Why not use an identity column?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply