Building a invoice number SP in MS-SQL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Building a invoice number SP in MS-SQL

Author

Topic

veasnamuch
Starting Member

5 Posts

Posted - 2011-03-14 : 23:43:14

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]
FType char(3)
FNumber bigint
LastUpdated datetime

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

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-15 : 07:48:30

Veasna, to me your stored proc seems like it is thread-safe. I must be missing something. Your stored proc was buried below a lot of VB code, so I am copying and pasting it here again, so if others have any thoughts they can comment on it.

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

Ifor
Aged Yak Warrior

700 Posts

Posted - 2011-03-15 : 08:04:50

The SP is NOT thread safe unless the isolation level is increased.

There may be better ways but the simple way around this is to use the t-sql form of the UPDATE statement:


SET ANSI_NULLS, QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[UDP_GETINVOICENUMBER]
   @form_type char(3) 
   ,@form_number bigint OUTPUT
AS

SET NOCOUNT ON;

UPDATE tblFormRunningNumber 
SET @form_number = FNumber = FNumber + 1
	,LastUpdated = CURRENT_TIMESTAMP 
WHERE FType = @form_type;
GO

Edit: I have just re-read the original SP and it does look thread safe. I would still be inclined to use the t-sql form of the UPDATE statement.

veasnamuch
Starting Member

5 Posts

Posted - 2011-03-15 : 22:06:35

I did re-program my store procedure as shown below


CREATE PROCEDURE [dbo].[UDP_GETINVOICENUMBER]
	@form_type as char(3), 
        @form_number AS BIGINT OUTPUT
AS
BEGIN
	SET NOCOUNT ON;
	BEGIN TRANSACTION		 
		UPDATE tblFormRunningNumber 
		SET @form_number=FNumber=FNumber+1
		WHERE(FType=@form_type) 	
	COMMIT TRANSACTION

But I still find duplicate invoice number happen in my system when I simulate in the loop of 1000 in each 4 different PC, after clicking on a button cmdTest_Click() to start generating invoice by calling to store procedure UDP_GETINVOICENUMBER.

I still have no clue what wrong to my implementation? How would they do for such banking system or accounting system where multi-users environment is applied? Do they face same problem? I thought they might not happen.

Best regards,

Veasna

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-03-16 : 02:25:59

Why not use AUTONUMBER or IDENTITY column?

N 56°04'39.26"
E 12°55'05.63"

veasnamuch
Starting Member

5 Posts

Posted - 2011-03-16 : 04:14:38

Hi Peso,

I do not use IDENTITY or AUTO INCREMENT because, I have different type of invoice. That's why I decided to used single running invoice number for all type of invoice, just separate them by FType.

How is suggestion of using IDENTITY or AUTO INCREMENT? You mean to have different table which have identity or auto increment for each type of invoice right?

I would try to implement that if I can not find other possibility to fix the current solution of using single running invoice number.

Best regards,

Veasna

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-16 : 07:55:40

If you are still experiencing problems, I would look at 2 things:

a) in about the same range where you see duplicated invoice numbers, do you also see gaps in the invoice numbers?

b) if you do, I would look to the VB code as the source of the problem. In particular, the DoCmd method you are using. I am no expert on VB, and have no experience with VB's threading model, but I suspect that DoCmd may be yielding to another thread.

veasnamuch
Starting Member

5 Posts

Posted - 2011-03-16 : 21:36:02

Dear sunitabeck,

I will review my VBA code to see its possibility of making the problem. So the store procedure that I have implemented is guaranteeing of getting single value for a thread(SQL Server)?

Thanks for your suggestion,

Veasna

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-16 : 22:18:09

Your stored proc is thread-safe. It was before you made the change Ifor suggested. And after you made that change it is better and it is still thread-safe.

Do the tests that I said earlier. To rule out DoCmd being the issue, remove everything between

lnginvoicenumber = GetFRNumber("EXP")

and

Next j

and replace it with either a statement to write the invoice number to a text stream, or to insert the invoice number into an array. Examine the text stream/array from all four test pc's outside of the loop.

aleximas
Starting Member

11 Posts

Posted - 2011-03-17 : 04:23:54

unspammed

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources