Click here to Skip to main content
15,867,308 members
Please Sign up or sign in to vote.
2.50/5 (2 votes)
Hi guys,

I want to insert 4 or 5 digits numeric value in my sql table.
what data type should i have to take n how can i insert it.

and how can i increment this value.

this value is for registration number.

eg:
0001
0002
0003
0004
.
.
.
0010
.
.
0100
.
.


starting value should be 0001 and implicitly increment it by code.

Please help me.

Thanks
Posted
Updated 21-Oct-14 6:43am
v4
Comments
Kornfeld Eliyahu Peter 21-Oct-14 13:00pm    
Think about it! The difference between 1 and 0001 is only visual (formatting) so why bother SQL with that, leave it to the UI!
Also an exactly 4 digit (or 5) value limits you to 10000 (or 100000) rows!

You can use an identity[^] column.
E.g.
C#
reg_num int IDENTITY(1000,1),

The number will be incremented automatically for each new row that is inserted.

Edit - an alternative (not recommended) can be found amongst the solutions to this post How to do auto generate EMPID in sql ?[^]
 
Share this answer
 
v2
Comments
abdul subhan mohammed 21-Oct-14 11:31am    
but i dont want to start from 1000 n it should to start from 0001 n then increment, plz.
CHill60 21-Oct-14 11:35am    
Then use IDENTITY(1,1) - if you want to have preceding zeroes then either do that at display time, or have a second char(4) column with a trigger that formats the id number to 4 chars e.g. RIGHT('0000' + CAST(reg_num AS NCHAR(4)), 4 )
abdul subhan mohammed 21-Oct-14 11:32am    
n i want to manually increment the value.
CHill60 21-Oct-14 11:36am    
Then just have an int column - why do you want to manually increment a registration number???
abdul subhan mohammed 21-Oct-14 11:40am    
there are departments based on depts i have to increment its value
Hi,

You can use IDENTITY column in SQL Server or SEQUENCE in Oracle .It will automatically increment its value and also you can set the starting value.


Thanks
 
Share this answer
 
SELECT 'RAK' + right('0000' + convert(varchar(10), Suffix), 5) AS 'Ticket' from tbl_Tickets

//'Suffix' is a column name
 
Share this answer
 
v2
Comments
Kornfeld Eliyahu Peter 21-Oct-14 14:50pm    
IMHO it is the most stupid sting to use SQL to format data for display...
abdul subhan mohammed 22-Oct-14 12:28pm    
then what is the wise man string to use SQL to format data for display...
C#
                cmd=new sqlcommand("select max(reg_code) from tbl_registration",con);
dr=cmd.ExecuteReader();
                dr.Read();
                if (dr.HasRows)
                {
                    if (dr[0].ToString() != "")
                    {
                        maxNo = Int64.Parse(dr[0].ToString());


                        long b = (maxNo + 1);

                        if (b <= 9)
                        {
                            txtusercode.Text = "000" + b.ToString();
                        }
                        else if (b <= 99)
                        {
                            txtusercode.Text = "00" + b.ToString();
                        }
                        else if (b <= 999)
                        {
                            txtusercode.Text = "0" + b.ToString();
                        }
                        else if (b <= 9999)
                        {
                            txtusercode.Text = b.ToString();
                        }
                        dr.Close();
                    }
                    else
                    {
                        txtusercode.Text = "0001";
                        dr.Close();
                    }
                }
                else
                {
                    dr.Close();
                    txtusercode.Text = "0001";
                }
 
Share this answer
 
Comments
CHill60 23-Oct-14 6:55am    
This is a truly awful solution. Firstly don't use max() to generate an id - it doesn't work well in multi-user environments.
Secondly all those if statements can be replaced by txtusercode.Text=b.ToString("0000");

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900