how to generate auto number

[lianpiau]

I want generate auto number format like year/month/4digit number eg:11/04/0001

2. I want read the database find last number and let number auto + 1 to generate new number

[darkagn]

Not sure what you mean by the first one, but for 2, it is easier and more accurate to use an identity field in the database.

[Mitja Bonca]

To read the last record from the DB you do:

"SELECT MAX(ColumnName) FROM MyTable"

And because this is a string (varchar) format, you 1st have to retreive the number out of the string, convert it to number (int), then do the incrementation (auto add 1) and convert it back to string and join with the rest of the "date" string".

And one important thing to mention: Before doing the incrementation (addint +1) you have to get the current date. If the month is not the same as the last retreived date, you have to start with number 1.

[Mitja Bonca]

Ok, I did the full working solution.
What you have to do, is to conside of getting the last record out of the database (look into my 1st post).
Then you use this code I did for you:

public Form1()
        {
            InitializeComponent();

            //get record out of database
            //you can use any method you want, 
            string record = "11/04/0011";
            record = CalculateNewRecordNumber(record);
        }

        private string CalculateNewRecordNumber(string record)
        {            
            string[] data = record.Split('/');
            DateTime currentDate = DateTime.Today;
            int recordYear = int.Parse(data[0]);
            recordYear = int.Parse("20" + recordYear);
            int recordMonth = int.Parse(data[1]);
            if (currentDate.Year == recordYear)
            {
                if (currentDate.Month == recordMonth)
                {
                    //year and month are the same, we only have to increment the number:
                    int number = int.Parse(data[2]);
                    //do the increment of the record number:
                    number++;
                    //create new record:
                    record = recordYear + "/" + recordMonth + "/";
                    string _recNumberOnly = number.ToString();

                    //loop to create 4 digits number!
                    for (int i = 0; i < 4; i++)
                    {
                        if (_recNumberOnly.Length == 4)
                            break;
                        else
                            _recNumberOnly = "0" + _recNumberOnly;
                    }
                    record += _recNumberOnly;
                }
                else
                {
                    //there is a new month!
                    //increment a month (year stays the same) and starts with number 0001:
                    recordMonth++;
                    record = recordYear + "/" + recordMonth + "/0001";
                }
            }
            else
            {
                //there is a new year!
                //increment a year and start from month 1 and starts with number 0001:
                recordYear++;
                record = recordYear + "/01/0001";
            }
            return record;
        }

[lianpiau]

Ok, I did the full working solution.
What you have to do, is to conside of getting the last record out of the database (look into my 1st post).
Then you use this code I did for you:

public Form1()
        {
            InitializeComponent();

            //get record out of database
            //you can use any method you want, 
            string record = "11/04/0011";
            record = CalculateNewRecordNumber(record);
        }

        private string CalculateNewRecordNumber(string record)
        {            
            string[] data = record.Split('/');
            DateTime currentDate = DateTime.Today;
            int recordYear = int.Parse(data[0]);
            recordYear = int.Parse("20" + recordYear);
            int recordMonth = int.Parse(data[1]);
            if (currentDate.Year == recordYear)
            {
                if (currentDate.Month == recordMonth)
                {
                    //year and month are the same, we only have to increment the number:
                    int number = int.Parse(data[2]);
                    //do the increment of the record number:
                    number++;
                    //create new record:
                    record = recordYear + "/" + recordMonth + "/";
                    string _recNumberOnly = number.ToString();

                    //loop to create 4 digits number!
                    for (int i = 0; i < 4; i++)
                    {
                        if (_recNumberOnly.Length == 4)
                            break;
                        else
                            _recNumberOnly = "0" + _recNumberOnly;
                    }
                    record += _recNumberOnly;
                }
                else
                {
                    //there is a new month!
                    //increment a month (year stays the same) and starts with number 0001:
                    recordMonth++;
                    record = recordYear + "/" + recordMonth + "/0001";
                }
            }
            else
            {
                //there is a new year!
                //increment a year and start from month 1 and starts with number 0001:
                recordYear++;
                record = recordYear + "/01/0001";
            }
            return record;
        }

Thank You for help me. You Code is work.
But If I want let last for digit continue to plus 1 even different year and month.
For example: 11/04/0757 and the next number is 11/05/0758, next 11/05/0759

and the year I want last two digit. For Example: 11 instead 2011

Therefore, how to change it

[lianpiau]

Ok, I did the full working solution.
What you have to do, is to conside of getting the last record out of the database (look into my 1st post).
Then you use this code I did for you:

private void Form2_Load(object sender, EventArgs e)
        {
            OleDbConnection conAuthor;

            ConnectionStringSettings conSettings = ConfigurationManager.ConnectionStrings["MyDBConnectionString_Loading"];
            string name = conSettings.ProviderName;
            string providerName = conSettings.ProviderName;
            string ConnectionString = conSettings.ConnectionString;

            conAuthor = new OleDbConnection(ConnectionString);

            OleDbCommand command = new OleDbCommand();
            command.Connection = conAuthor;
            command.CommandText = "SELECT  MAX(LoadingNo) AS LoadingNo FROM LoadItem";
            command.CommandType = CommandType.Text;

            conAuthor.Open();
            OleDbDataReader dr = command.ExecuteReader();

            while (dr.Read())
            {
                textBox1.Text = dr["LoadingNo"].ToString();
            }

            dr.Close();
            conAuthor.Close();

            string record = textBox1.Text;
            record = CalculateNewRecordNumber(record);

           textBox2.Text = record;
            
        }

        private string CalculateNewRecordNumber(string record)
        {
            string[] data = record.Split('/');
            DateTime currentDate = DateTime.Today;
            int recordYear = int.Parse(data[0]);
            recordYear = int.Parse("20" + recordYear);
            int recordMonth = int.Parse(data[1]);
            if (currentDate.Year == recordYear)
            {
                if (currentDate.Month == recordMonth)
                {
                    //year and month are the same, we only have to increment the number:
                    int number = int.Parse(data[2]);
                    //do the increment of the record number:
                    number++;
                    //create new record:
                    record = recordYear + "/" + recordMonth + "/";
                    string _recNumberOnly = number.ToString();

                    //loop to create 4 digits number!
                    for (int i = 0; i < 4; i++)
                    {
                        if (_recNumberOnly.Length == 4)
                            break;
                        else
                            _recNumberOnly = "0" + _recNumberOnly;
                    }
                    record += _recNumberOnly;
                }
                else
                {
                    //there is a new month!
                    //increment a month (year stays the same) and starts with number 0001:
                    recordMonth++;
                    record = recordYear + "/" + recordMonth + "/0001";
                    
                }
            }
            else
            {
                //there is a new year!
                //increment a year and start from month 1 and starts with number 0001:
                recordYear++;
                record = recordYear + "/01/0001";
            }
            return record;

           
        }


    }

[lianpiau]

How to let month display with 2 digit. such as 04

[abelLazm]

in c#

textField.Text = DateTime.Now.ToShortDateString();
// Shows date as 29/04/2011....
string strFormat = "dd/MM/yy";
textField.Text = DateTime.Now.ToString(strFormat);
// Display date as 29/04/11...

for sql check this link

[hirenpatel53]

for second one

Select Top 1 Id from tablename order by id desc

Save this value in any int variable and add one into it it will gives you auto increament number

[lianpiau]

in c#

textField.Text = DateTime.Now.ToShortDateString();
// Shows date as 29/04/2011....
string strFormat = "dd/MM/yy";
textField.Text = DateTime.Now.ToString(strFormat);
// Display date as 29/04/11...

for sql check this link

thanks. it work for me.

[lianpiau]

But I got another problem which is related with this question.
http://www.daniweb.com/software-development/csharp/threads/362648

Title is 2 auto generate number