How to make an auto increment number based on a year

Question

0.00/5 (No votes)

See more:

I am building a simple C# Windows Forms application where a custom auto increment number should be there, it will be used as a serial number for each application.

How can I make this custom number reset based on the year ?

Example:
2019
serial number: 1/2019
serial number: 2/2019
serial number: 3/2019
serial number: 4/2019
.
.
.
serial number: 200/2019 "let's suppose this is on December 31st 2019"

2020
serial number: 1/2020 "let's suppose this is on January 1st 2020"
serial number: 2/2020
serial number: 3/2020
serial number: 4/2020
serial number: 5/2020
.
.
.

What I have tried:

I have tried a lot of stuff, the code looks messy and non-understandable that's why I didn't share anything, just to keep the question neat, of course it will be be stored in database, I will be keeping the auto increment ID column as it is and use it for search, sort, etc.. and I have created a separate column for this custom serial number, the problem is how to make the software aware that we have entered 2020 so reset the serial and add the year value after the slash.

Posted 16-Oct-19 4:02am

Moosa Arbeed

Updated 19-Nov-19 1:18am

Add a Solution

Comments

Richard MacCutchan 16-Oct-19 10:32am

Your application will need to make that decision at run time.

4 solutions

Solution 2

Hey,
You can concatinate the incremented value with the year, please refer the below snippet and accordingly change the code.

SQL

DECLARE	@y int
set @y = DateName(yy,getdate())
select cast(ID as varchar(10)) +'/' + cast(@y as varchar(10)) from myTable

Note: Below code is the secondary solution from code level

C#

class Program
    {
        static void Main(string[] args)
        {
            int[] serialNumbers = new int[] { 1, 2, 3, 4, 5 };//take your table id's
            foreach (var v in serialNumbers)
            {
                Console.WriteLine("serial number: " + v + " / " + DateTime.Today.Year.ToString());
            }
        }
    }

Output:-

serial number: 1 / 2019
serial number: 2 / 2019
serial number: 3 / 2019
serial number: 4 / 2019
serial number: 5 / 2019

Posted 16-Oct-19 4:46am

srko

Updated 21-Oct-19 2:56am

v6

Comments

Moosa Arbeed 16-Oct-19 12:37pm

I prefer doing it in the code, I am using C#, I am leaving the SQL as a second option.

srko 17-Oct-19 4:13am

Added a new solution in the bottom, is working absolutely fine :)
Hope this will surely help :)

CHill60 19-Oct-19 9:57am

Far better is to use "Improve solution" link and add extra notes to your existing solution. Far less confusing for people - after all, which solution is meant to be the correct one?

srko 21-Oct-19 8:44am

Both the solutions are correct, one solution will help from DB side and the other on from your code level.

CHill60 21-Oct-19 8:49am

All the more reason to update the original solution and to explain that. Posting multiple solutions to a question can be perceived as rep-point-hunting and can lead to downvoting

srko 21-Oct-19 8:53am

I'm new to this forum, i'm using it but never answered or wrote any article. Just trying it for the first time in this month. I hope people here will be supportive. thanks for your valuable inputs, it will help me in upcoming activities.

CHill60 21-Oct-19 11:22am

Some are, some are less supportive but welcome to QA! Thanks for updating the solution - it looks more complete now

srko 21-Oct-19 8:57am

Changed the solution as mentioned, thank you.

Solution 1

The way I do these is via Sql Server; and how I do is it by using DBCC CHECKIDENT(), which can be used to reset the IDENTITY column.
Where this differs from what you want to do is that the year I have is prepended to the customer number; e.g. 20190043, 20190044.. and when the new year comes in I will reset to 20200001, 20200002 etc

Posted 16-Oct-19 4:43am

MadMyche

Comments

Moosa Arbeed 16-Oct-19 12:37pm

I prefer doing it in the code, I am using C#
plus, I don't want to manually reset it every year

MadMyche 16-Oct-19 12:52pm

Mine is automatic; I use SQL Agent and run it at 01/01 00:00.... and the stored procedure for create account verifies the correct "prefix"

Solution 3

C#

int serialNumber = 23;   // Or whatever number you have got
int year = DateTime.Today.Year;
string outputString = "serial number: " + serialNumber.ToString() + " / " + year.ToString();

Posted 16-Oct-19 20:04pm

Member 13566383

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Moosa Arbeed · Accepted Answer · 2019-11-19T01:19:00

Solution 5

I have figured it out myself.

C#

public class SerialNumber
{
    private static readonly MyDbContext DbContext = new MyDbContext();

    public static string SrNo()
    {
        var sn = DbContext.PrimaryForms.ToList().Last().FormDescription;

        var array = sn.Split('/');

        if (int.Parse(array[0]) == DateTime.Today.Year)
        {
            return array[0] + "/" + (int.Parse(array[1]) + 1);
        }

        return (int.Parse(array[0]) + 1) + "/1";
    }
}

if code is not clear, ask me for explanation, thank you.

Posted 19-Nov-19 1:19am

Moosa Arbeed

Comments

Richard Deeming 19-Nov-19 13:18pm

That code has quite a few problems.

The .ToList call ensures that the entire table is loaded into your application's memory on every call, just to select a single record.

The .Last call assumes that the list returned is sorted correctly for your requirements, even though you haven't specified a sort order.

The .Last call will throw an exception if there are no items returned.

You are assuming that there is only ever one user entering data. If two users try to add a record at the same time, they will end up with duplicate serial numbers.

You are assuming that there will always be at least one item in every year. If there were no items last year, the first item for this year will have a serial number starting with 2018 instead of 2019.

You are also assuming that items never need to be added retrospecivley, with a serial number in a previous year. This may or may not be a problem, depending on your requirements.

Member 14649324 30-Aug-20 15:07pm

how you do this in vb.net plz can you tell mee i am a new to vb.net