Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2004
    Posts
    5

    Unanswered: Building a invoice number SP in MS-SQL

    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****nSQL strSQLQuery

    strSQLQuery = "UPDATE dbo_tblEXCGD SET EXCGID=" & lnginvoicenumber & " " _
    & "WHERE(EXCGID=" & strRandomCode & ")"
    DoCmd****nSQL 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

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Have you considered using an identity column for the primary key? You have not told what UDP_GETINVOICENUMBER does, so it's hard to tell.

    When it simply takes the highest value + 1, use an identity column instead.

    When it does more than that, you might use a calculated column. But again, without knowing what UDP_GETINVOICENUMBER does, it is impossible to tell.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Aug 2004
    Posts
    5
    The store procedure UDP_GETINVOICENUMBER is in charge of getting the last invoice number +1 and then update back to table its self as its latest invoice number to be used base on FType type.

    I have different type of invoice which differentiated by FType. As your suggestion, if I have 3 type of invoices(Type1, Type2, and Type3) then I need to have tableType1(ID identity), tableType2(ID identity), and tableType3(ID identity)?

    How can I do if I had more then 3 type of invoice?

    Best regards,


    Veasna

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I don't know if I understand what your problem is.
    Can you give some sample data?
    What is "FType"?

    Please consider that no-one here on this forum knows what your problem at hand is. Give us some sample data and explain all the special terms you use.

    I'll give it a go, with what I think is the problem: You have to deal with different types of invoices, some of the data is shared among all the different types of invoices, some data is specific to just one invoice type, and you need a way to model that in a database. You are struck now on how to deal with the PK's for all those different invoice types and later on on how to define foreign keys that reference those invoices.

    If that is what you are looking for, I would go for inheritance. You define one base table Invoice (superclass or ancestor class) with columns for all the common data, like CustomerId, AmountDue, InvoiceDate, DueDate, .... You also give it an IDENTITY Primary Key.

    Per invoice type you create a table (subclass). The PK of that table is also the Foreign Key that references the Invoice table.

    The only IDENTITY PK is located in the Invoice table, all the subclass tables use that ancestor PK as their own PK, so you are rid of generating an array of different PK's for each separate invoice type table.

    All foreign keys to invoices should point to the Invoice table, never to one of the special invoice type tables.

    I would also add a column in all the tables (ancestor and subclasses) that references a code table (or reference table) of the invoice types, although that is redundant information, I found it can ease programming a lot later on. By using DEFAULT and a check on the value of the CoInvoiceTypeId, you don't have to worry about its value in your INSERT scripts, and it is protected from inadvertent changes by UPDATE scripts.
    You can run some periodic tests during development (and production) to assert that the CoInvoiceTypeId in both the ancestor and subclass tables match (that is the pain/prize associated with redundancy). But all this is purely optional.
    Code:
    CREATE TABLE dbo.Invoice(
    	Id		BIGINT	IDENTITY(1,1)	NOT NULL,
    	CoInvoiceTypeId	BIGINT	NOT NULL,
    	CustomerId	BIGINT	NOT NULL,
    	InvoiceDate	DATE	NOT NULL,
    	DueDate		DATE	NOT NULL,
    	AmountDue	DECIMAL	NOT NULL,
    	...
    	CONSTRAINT pk_Invoice PRIMARY KEY (Id)
    )
    
    alter table dbo.Invoice
       add constraint Invoice_CoInvoiceType foreign key (CoInvoiceTypeId)
          references dbo.CoInvoiceType(Id)
    
    
    CREATE TABLE dbo.InvoiceType1(
    	InvoiceId		BIGINT	NOT NULL,
    	CoInvoiceTypeId	BIGINT	NOT NULL	DEFAULT 1
    		CONSTRAINT CC_InvoiceType1_CoInvoiceTypeId CHECK (CoInvoiceTypeId = 1),
    	...
    	CONSTRAINT pk_InvoiceType1 PRIMARY KEY (Id)
    )
    GO
    
    alter table dbo.InvoiceType1
       add constraint InvoiceType1_Invoice foreign key (InvoiceId)
          references dbo.Invoice (Id)
    GO
    
    alter table dbo.InvoiceType1
       add constraint InvoiceType1_CoInvoiceType foreign key (CoInvoiceTypeId)
          references dbo.CoInvoiceType(Id)
    
    
    CREATE TABLE dbo.InvoiceType2(
    	InvoiceId		BIGINT	NOT NULL,
    	CoInvoiceTypeId	BIGINT	NOT NULL	DEFAULT 2
    		CONSTRAINT CC_InvoiceType2_CoInvoiceTypeId CHECK (CoInvoiceTypeId = 2),
    	...
    	CONSTRAINT pk_InvoiceType2 PRIMARY KEY (Id)
    )
    GO
    
    alter table dbo.InvoiceType2
       add constraint InvoiceType2_Invoice foreign key (InvoiceId)
          references dbo.Invoice (Id)
    GO
    
    alter table dbo.InvoiceType2
       add constraint InvoiceType2_CoInvoiceType foreign key (CoInvoiceTypeId)
          references dbo.CoInvoiceType(Id)
    
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Aug 2004
    Posts
    5
    Dear Wim,

    That's very great to see your cool comments. I need to review my code for awhile because there have some clue now on the problem which generate duplicate invoice number.

    Best regards,

    Veasna

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •