Invoice no should starting with 1 for every finacial year

Question

0.00/5 (No votes)

See more:

I have one application having invoice number.invoice textbox having auto increment value initial as "1". Now I want invoice number should be started fresh for every financial year.

What I have tried:

C#

private void FillInvoiceID()
    {
        string con = ConfigurationManager.AppSettings["FilePath"].ToString();
        OleDbConnection c1 = new OleDbConnection(con);
        if (c1.State == ConnectionState.Open)
        {
            c1.Close();
        }
        c1.Open();
        string retInvoiceIDQuery = "SELECT Max(INVOICENO) as INVOICENO FROM TAXINVOICE";
        OleDbCommand commd = new OleDbCommand(retInvoiceIDQuery, c1);
        OleDbDataAdapter datadp = new OleDbDataAdapter(commd);
        DataTable datset = new DataTable();
        datadp.Fill(datset);
        if (datset.Rows[0][0] != null && string.IsNullOrEmpty(datset.Rows[0][0].ToString()))
        {
            datset.Rows[0][0] = "0";
        }

        invoice = Convert.ToInt32(datset.Rows[0]["INVOICENO"].ToString()) + 1;
        txtInvoice.Text = invoice.ToString();
        c1.Close();
    }

Posted 7-Mar-17 18:49pm

Member 12742983

Updated 8-Mar-17 7:29am

Bryian Tan

v2

Add a Solution

Comments

Karthik_Mahalingam 8-Mar-17 1:30am

then it will have duplicate invoice rite ?

[no name] 8-Mar-17 19:17pm

This is the obvious problem. Invoice #xxx2323. What year was that?

Er. Puneet Goel 8-Mar-17 1:42am

So if you start with 1, every year it will start repeating. So you can go for Year_InvoiceNo like 2017-10000, 2017-10001 etc.

Jignesh Khant 8-Mar-17 1:46am

You can rename the existing table and recreate the same table at start of financial year. By doing this you can start invoice number from 1 but you cannot have last financial years data.

Tomas Takac 8-Mar-17 2:32am

What I would do is to create a table InvoiceNumbers(FiscalYear, CurrentInvoiceNumber). To get next invoice number you need to read the current invoice number from the table for the current fiscal year, increment it by 1 and then update the table with that new number. Please make sure this runs in a transaction and you place an update lock on the table while reading it. Then you are free to use this invoice number as you please.

Dave Kreskowiak 8-Mar-17 13:46pm

WRONG WRONG WRONG. This does not work in a multi-user environment. You NEVER do something like this. You always let the database assign the numbers.

Tomas Takac 8-Mar-17 14:08pm

I'm not sure what you mean. Whay it wouldn't work?

Dave Kreskowiak 8-Mar-17 14:17pm

If more than one machine runs the code to get the current invoice number, those machines will all use the same invoice number for different invoices.

Tomas Takac 8-Mar-17 14:29pm

But if you run it in transaction and lock the table, or use serializable transactions, then I don't see any reason why it shouldn't. But maybe I'm missing something.

Dave Kreskowiak 8-Mar-17 14:33pm

Have any idea how many times I've seen people screw that up or not use a transaction at all? Makes for fun times fixing the f-up in the code and the data once its in production. Experience shows that it's way too easy to screw it up.

CHill60 8-Mar-17 11:59am

I strongly advise you to drop this requirement - it's far more trouble than it is worth.

[no name] 8-Mar-17 19:20pm

Repeat invoice numbers. A brilliant idea.

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2017-03-08T07:29:00

Here is a solution that uses an identity column and triggers and in theory should work well in a multi-user environment (unlike solutions that read the current max invoice number).
I have an invoice table that contains at least:

SQL

create table invoice
(
	id int identity(1,1),
	invoicedate date,
	invoiceNumber varchar(20)
)

Note that I have an id column that SQL is going to automatically increment for me but I also have an invoiceNumber column which will contain the contrived invoice number starting at 1 each year.

I also need a table to track the maximum id per year.

SQL

create table invoiceYears
(
	invoiceYear int NOT NULL,
	maxid int
)

I then create a TRIGGER on the invoice table to "calculate" the contrived invoiceNumber each time an invoice is inserted into the table.

SQL

CREEATE TRIGGER trig ON [dbo].invoice 
AFTER INSERT
AS
	declare @invid int;
	declare @invyear int;
	declare @invnum varchar(20);


	select @invid=i.id from inserted i;	
	select @invyear=datepart(YEAR, i.invoicedate) from inserted i;	

	if exists (SELECT TOP 1 invoiceYear FROM invoiceYears WHERE invoiceYear = @invyear)
	BEGIN
		UPDATE invoiceYears set maxid = @invid WHERE invoiceYear = @invyear
	END
	ELSE
	BEGIN
		INSERT INTO invoiceYears values (@invyear, @invid)
	END

	UPDATE inv SET invoiceNumber = 'ABC-' + CAST(@invyear AS VARCHAR(4)) + '-' + CAST(inv.id - ISNULL(IY.maxid, 0) AS varchar(20))
	FROM invoice inv
	LEFT OUTER JOIN invoiceYears IY ON IY.invoiceYear = @invyear - 1
	WHERE inv.id = @invid

GO

Note that it will insert a new row into this table at the change of the year. If your financial year does not start on 1st January of each year you will need to replace the DATEPART with the calculation of the financial year.

Other things to note:
- Because I've used a trigger then this isn't the most performant method to use in a high volume scenario.
- It won't work if you "skip" a year - unless you manually update the invoiceYears table
- I would normally leave the formatting of the invoice number to the presentation layer - if this is removed from the trigger then it performs better on inserts with only a minor extra overhead on reads.
- See the comments from Tomas above regarding transactions and locks - I haven't put these into the solution (for the sake of keeping as a simple example)

Invoice no should starting with 1 for every finacial year

1 solution

Solution 1

Add your solution here

Preview 0

Invoice no should starting with 1 for every finacial year

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us