How to Auto increment a database field

Question

3.00/5 (2 votes)

See more:

My Project contains a database table 'DetailsTB'. and passenger record are save with respect to it's AutoNo, ReceiptNo.
AutoNo. column is an data type with bigint.
AutoNo is an auto increment column.
The AutoNo start with 1 in the year 2013. and the ReceiptNo make it as 12013.
and at the end of year 2013 the ReceiptNo reaches to 55702013.
The ReceiptNo is a combination of AutoNo and the current year.

The problem is when the year 2014 start, the Receipt No. start with 55712014. instead of 12014. Means only AutoNo must start with 1 when 2014 start.
I want, when the 2014 start, the Receipt No. not start with 55712014 it must start with 12014. means AutoNo stop to 5570 at end of year 2013 and same column AutoNo start with 1 when 2014 start.

when previous year start the first ReceiptNo is 12013 and when new year 2014 start the first ReceiptNo is automatically 12014. so there is easily differentiation and identifiable between ReceiptNo 12013 and 12014 with respect to year.

why I am doing this ?
Because, if I am not make AutoNo to 1 when new year start it's still continued...but at certain year later it reaches to uncountable(infinite) numbers so the necessary to make it as 1 when new year start.

How to do that same.

My code for auto increment:

con.Open();
cmd = new SqlCommand("SELECT IsNull(MAX(ReceiptNo),0)+1 FROM DetailsTB", con);
SqlDataReader sdr = cmd.ExecuteReader();
while (sdr.Read())
{
txt_haj_receiptno.Text = sdr[0].ToString();
}
con.Close();

Thanks in advance.

Posted 1-Dec-13 6:01am

er.shoaib

Updated 1-Dec-13 8:11am

v2

Add a Solution

Comments

CHill60 1-Dec-13 19:08pm

In addition to the solutions below ... using MAX(ReceiptNo) is really dangerous in a multi-user multi-volume situation .... and if you're that worried about how big the numbers are going to get I guess you are talking some volume here

4 solutions

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)

Ron Beyer · Answer 1 · 2013-12-01T06:14:00

If you have Receipt No as the primary key then you can't. This would break relational data in the database if you use the same primary key for two records.

If this is something you really want to do, I would suggest using a different table for each year, or adding another primary key like RecordNo that can auto-increment. If you do it that way, you would have to manually auto-increment each record as its inserted (best to do this with a stored procedure).

Out of curiosity, why would you want to have the same receipt number for two different receipts? Think of growth here, what happens when you do end 2013 with number 5700 and then on January 31 you release product XYZ that gets 5700 orders in one day (would be nice). You'd have to receipts with the same number just one day apart... An unlikely scenario I know, but what does it matter if they increment forever?

PIEBALDconsult · Answer 2 · 2013-12-01T06:19:00

Solution 3

I recommend using a sequence instead. Auto-increment/identity fields aren't worth the trouble they cause; there's always a better way.

Posted 1-Dec-13 6:19am

PIEBALDconsult

Comments

er.shoaib 1-Dec-13 14:17pm

Thanks for giving me your valuable time.
Please read the question carefully.
Thanks in advance

CHill60 1-Dec-13 19:06pm

I am puzzled by this... auto-increment database columns have always been good to me... what trouble do they cause that I have missed?

Debopam Pal · Answer 3 · 2013-12-01T06:16:00

Solution 2

A filed in a Table cannot contain same ID e.g. in 2013 there is already an ID 1, and in 2014 there cannot be an ID 1, if you want to do it, then you must make a composite primary key where the composition will be Year field and ID field.

Posted 1-Dec-13 6:16am

Debopam Pal

Comments

er.shoaib 1-Dec-13 14:16pm

Thanks for giving me your valuable time.
Please read the question carefully.
Thanks in advance

NWPU_Tbeck · Answer 4 · 2013-12-01T16:53:00

if you use a sequence for increment, you can reset it when the year is changed. For example, store the year number in some place, when insert check the year number is the same as stored year, if not reset the sequence and updated the new year number.
See follow function:

SQL

Try this Function

CREATE OR REPLACE function restart_seq(SeqName IN varchar2) return Integer
as
PRAGMA AUTONOMOUS_TRANSACTION;
vs integer;
inc integer;
junk integer;
sqName varchar2(50);
begin
sqName := seqName||'.nextval';
execute immediate 'select '||sqName||' from dual' into vs;
inc := 0 - vs;
execute immediate 'alter sequence '||SeqName||' minvalue '|| inc;
execute immediate 'alter sequence '||SeqName||' increment by '||inc;
execute immediate 'select '||sqName||' from dual' into junk;
execute immediate 'alter sequence '||SeqName||' increment by 1';
RETURN junk;
end;

/

actually, i really suggest use guid instead of increment tag.

How to Auto increment a database field

4 solutions

Solution 1

Solution 3

Solution 2

Solution 4

Add your solution here

Preview 0