The problem is Red color there. when add record into table. I want have same LoadingNo but different RecNo. But I'm using this only can add record until 2 only. Expert please help me!!

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";
                    //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 = string.Format("{0:yy}", DateTime.Now) + "/" + string.Format("{0:MM}", DateTime.Now) + "/";
                    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 year!
                //increment a year and start from month 1 and starts with number 0001:
                recordYear++;
                //record = recordYear + "/01/0001";
                //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 = string.Format("{0:yy}", DateTime.Now) + "/" + string.Format("{0:MM}", DateTime.Now) + "/";
                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;
            }
            return record;
        }

        private void timerLoadingNo_Tick(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);

            //let textbox display date format
            textBox2.Text = DateTime.Now.ToShortDateString();
            string strFormat = "dd/MM/yy";
            textBox2.Text = DateTime.Now.ToString(strFormat);

            textBox2.Text = record;

            timerLoadingNo.Enabled = false;
        }

        private void timer1_Tick(object sender, EventArgs e)
        {
            ConnectionStringSettings conSettings = ConfigurationManager.ConnectionStrings["MyDBConnectionString"];
            string name = conSettings.ProviderName;
            string providerName = conSettings.ProviderName;
            string ConnectionString = conSettings.ConnectionString;

            string sql = "SELECT * FROM PDC_FG Order by DeliveryDate,PONO,Model,Item";
            OleDbConnection connection = new OleDbConnection(ConnectionString);
            connection.Open();
            sCommand = new OleDbCommand(sql, connection);
            sAdapter = new OleDbDataAdapter(sCommand);
            sBuilder = new OleDbCommandBuilder(sAdapter);
            sDs = new DataSet();
            sAdapter.Fill(sDs, "PDC_FG");
            sTable = sDs.Tables["PDC_FG"];
            connection.Close();
            dataGridView1.DataSource = sDs.Tables["PDC_FG"];
            dataGridView1.ReadOnly = true;

            lblMessage.Text = "";
            dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
            timer1.Enabled = false;
        }

        private void btnLoading_Click(object sender, EventArgs e)
        {
            OleDbCommand cmdCheckID, cmdCheckID1;
            OleDbDataReader dtrCheckID, dtrCheckID1;

            foreach (DataGridViewRow r in dataGridView1.SelectedRows)
            {


                string PONO = r.Cells[0].Value.ToString();
                string Customer = r.Cells[2].Value.ToString();
                string Item = r.Cells[4].Value.ToString();
                string Model = r.Cells[5].Value.ToString();
                string FGrade = r.Cells[6].Value.ToString();
                string Thk = r.Cells[7].Value.ToString();
                string Width = r.Cells[8].Value.ToString();
                string Length = r.Cells[9].Value.ToString();
                string Qty = r.Cells[10].Value.ToString();
                string Remark = r.Cells[21].Value.ToString();

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

                //Code Insert Both values into database table
                OleDbConnection con = new OleDbConnection(ConnectionString);

                con.Open();
                
                string strChechID = "select * from LoadItems where PONo='" + PONO + "' and Model='" + Model + "' and Material='" + FGrade + "'and Thk='" + Thk + "'and Width='" + Width + "' and Length ='" + Length + "' and Item='" + Item + "'";
                cmdCheckID = new OleDbCommand(strChechID, con);
                dtrCheckID = cmdCheckID.ExecuteReader();

                
                string strChechID1 = "select LoadingNo from LoadItems where LoadingNo=LoadingNo";
                cmdCheckID1 = new OleDbCommand(strChechID1, con);
                dtrCheckID1 = cmdCheckID1.ExecuteReader();

                OleDbCommand comm = new OleDbCommand("insert into LoadItems values(@LoadingNo,@RecNo,@PONo,@Cust,@Item,@Model,@Material,@Thk,@Width,@Length,@Qty,@Remark)", con);

                

                try
                {

                    if (dtrCheckID.HasRows)
                    {
                        MessageBox.Show("Duplicate Data!!!!!!");

                        dtrCheckID.Close();


                    }
                    else
                    {
                        dtrCheckID.Close();

                        try
                        {
                            int RecNo = 1;


                            if (dtrCheckID1.HasRows)
                            {

                                RecNo += 1;



                                comm.Parameters.AddWithValue("@LoadingNo", textBox2.Text);
                                comm.Parameters.AddWithValue("@RecNo", RecNo);
                                comm.Parameters.AddWithValue("@PONo", PONO);
                                comm.Parameters.AddWithValue("@Cust", Customer);
                                comm.Parameters.AddWithValue("@Item", Item);
                                comm.Parameters.AddWithValue("@Model", Model);
                                comm.Parameters.AddWithValue("@Material", FGrade);
                                comm.Parameters.AddWithValue("@Thk", Thk);
                                comm.Parameters.AddWithValue("@Width", Width);
                                comm.Parameters.AddWithValue("@Length", Length);
                                comm.Parameters.AddWithValue("@Qty", Qty);
                                comm.Parameters.AddWithValue("@Remark", Remark);


                                comm.ExecuteNonQuery();
                                lblMessage.Text = ("Successful Insert!!!");
                                con.Close();

                                timer1.Enabled = true;
                            }

                            
                                else
                            {
                                comm.Parameters.AddWithValue("@LoadingNo", textBox2.Text);
                                comm.Parameters.AddWithValue("@RecNo", RecNo);
                                comm.Parameters.AddWithValue("@PONo", PONO);
                                comm.Parameters.AddWithValue("@Cust", Customer);
                                comm.Parameters.AddWithValue("@Item", Item);
                                comm.Parameters.AddWithValue("@Model", Model);
                                comm.Parameters.AddWithValue("@Material", FGrade);
                                comm.Parameters.AddWithValue("@Thk", Thk);
                                comm.Parameters.AddWithValue("@Width", Width);
                                comm.Parameters.AddWithValue("@Length", Length);
                                comm.Parameters.AddWithValue("@Qty", Qty);
                                comm.Parameters.AddWithValue("@Remark", Remark);


                                comm.ExecuteNonQuery();
                                lblMessage.Text = ("Successful Insert!!!");
                                con.Close();

                                timer1.Enabled = true;
                            }
                        }
                        catch (Exception ex)
                        {
                            MessageBox.Show(ex.ToString());
                        }
                    }
                }

                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }

            }
        }

Edited 5 Years Ago by lianpiau: n/a

Could you explain what CalculateNewRecordNumber is supposed to do.

I think it can be done more efficiently.


Why are you doing this?

textBox2.Text = DateTime.Now.ToString(strFormat);

textBox2.Text = record;

This statement doesn't look right

string strChechID1 = "select LoadingNo from LoadItems where LoadingNo=LoadingNo";

Ok this is Where you say the problem is.

int RecNo = 1;

if (dtrCheckID1.HasRows)
{

	RecNo += 1;



	comm.Parameters.AddWithValue("@LoadingNo", textBox2.Text);
	comm.Parameters.AddWithValue("@RecNo", RecNo);
	comm.Parameters.AddWithValue("@PONo", PONO);
	comm.Parameters.AddWithValue("@Cust", Customer);
	comm.Parameters.AddWithValue("@Item", Item);
	comm.Parameters.AddWithValue("@Model", Model);
	comm.Parameters.AddWithValue("@Material", FGrade);
	comm.Parameters.AddWithValue("@Thk", Thk);
	comm.Parameters.AddWithValue("@Width", Width);
	comm.Parameters.AddWithValue("@Length", Length);
	comm.Parameters.AddWithValue("@Qty", Qty);
	comm.Parameters.AddWithValue("@Remark", Remark);


	comm.ExecuteNonQuery();
	lblMessage.Text = ("Successful Insert!!!");
	con.Close();

	timer1.Enabled = true;
}


else
{
	comm.Parameters.AddWithValue("@LoadingNo", textBox2.Text);
	comm.Parameters.AddWithValue("@RecNo", RecNo);
	comm.Parameters.AddWithValue("@PONo", PONO);
	comm.Parameters.AddWithValue("@Cust", Customer);
	comm.Parameters.AddWithValue("@Item", Item);
	comm.Parameters.AddWithValue("@Model", Model);
	comm.Parameters.AddWithValue("@Material", FGrade);
	comm.Parameters.AddWithValue("@Thk", Thk);
	comm.Parameters.AddWithValue("@Width", Width);
	comm.Parameters.AddWithValue("@Length", Length);
	comm.Parameters.AddWithValue("@Qty", Qty);
	comm.Parameters.AddWithValue("@Remark", Remark);


	comm.ExecuteNonQuery();
	lblMessage.Text = ("Successful Insert!!!");
	con.Close();

}

All I see is one statement has RecNo += 1; and the other doesn't.

Could you please clarify what exactly you want, also coding your code would immensely help.

Peace.

Edited 5 Years Ago by finito: formatting

Could you explain what CalculateNewRecordNumber is supposed to do.

I think it can be done more efficiently.


Why are you doing this?

textBox2.Text = DateTime.Now.ToString(strFormat);

textBox2.Text = record;

This statement doesn't look right

string strChechID1 = "select LoadingNo from LoadItems where LoadingNo=LoadingNo";

Ok this is Where you say the problem is.

int RecNo = 1;

if (dtrCheckID1.HasRows)
{

	RecNo += 1;



	comm.Parameters.AddWithValue("@LoadingNo", textBox2.Text);
	comm.Parameters.AddWithValue("@RecNo", RecNo);
	comm.Parameters.AddWithValue("@PONo", PONO);
	comm.Parameters.AddWithValue("@Cust", Customer);
	comm.Parameters.AddWithValue("@Item", Item);
	comm.Parameters.AddWithValue("@Model", Model);
	comm.Parameters.AddWithValue("@Material", FGrade);
	comm.Parameters.AddWithValue("@Thk", Thk);
	comm.Parameters.AddWithValue("@Width", Width);
	comm.Parameters.AddWithValue("@Length", Length);
	comm.Parameters.AddWithValue("@Qty", Qty);
	comm.Parameters.AddWithValue("@Remark", Remark);


	comm.ExecuteNonQuery();
	lblMessage.Text = ("Successful Insert!!!");
	con.Close();

	timer1.Enabled = true;
}


else
{
	comm.Parameters.AddWithValue("@LoadingNo", textBox2.Text);
	comm.Parameters.AddWithValue("@RecNo", RecNo);
	comm.Parameters.AddWithValue("@PONo", PONO);
	comm.Parameters.AddWithValue("@Cust", Customer);
	comm.Parameters.AddWithValue("@Item", Item);
	comm.Parameters.AddWithValue("@Model", Model);
	comm.Parameters.AddWithValue("@Material", FGrade);
	comm.Parameters.AddWithValue("@Thk", Thk);
	comm.Parameters.AddWithValue("@Width", Width);
	comm.Parameters.AddWithValue("@Length", Length);
	comm.Parameters.AddWithValue("@Qty", Qty);
	comm.Parameters.AddWithValue("@Remark", Remark);


	comm.ExecuteNonQuery();
	lblMessage.Text = ("Successful Insert!!!");
	con.Close();

}

All I see is one statement has RecNo += 1; and the other doesn't.

Could you please clarify what exactly you want, also coding your code would immensely help.

Peace.

textBox2.Text = DateTime.Now.ToString(strFormat);
 
textBox2.Text = record;

this code is want convert it back to datetime format. if no do this, it will display month without 0 in front eg. 5 -> 05

string strChechID1 = "select LoadingNo from LoadItems where LoadingNo=LoadingNo";

this code is I want to detect they have same LoadingNo. If they have same LoadingNo. The RecNo will increase 1 and so on.

Actually LoadingNo and RecNo is use to Unique record. Both r primary key. When I select a few rows of records from datagridview into database table. Therefore, these record will have same LoadingNo but different Recno.The LoadingNo Format is yy/MM/4 digit number. eg,11/05/0001 , 11/06/0002, 12/05/0003. RecNo format is digit only start from 1. eg 1 , 2, 3,......

So when I insert a few records, they will auto generate LoadingNo and RecNo

Edited 5 Years Ago by lianpiau: n/a

CalculateNewRecordNumber
You still didn't clarify what this is?

Try this for the textBox

textBox2.Text = DateTime.Now.ToString("dd/MM/yy", DateTimeFormatInfo.InvariantInfo);

I still don't understand what you are doing here.

string strChechID1 = "select LoadingNo from LoadItems where LoadingNo=LoadingNo";

Please provide the Create Table statement for this table and provide a sample Input/Output.

-------
EDIT
-------

Ok I understand what loadingNo is now, But then what is the point of RecNo.

i.e. if LoadingNo is 12/05/0003 is the third Invoice or Loading that happened that day. When what is Rec?

Won't Rec be 3 as well?

------------------------

Peace

Edited 5 Years Ago by finito: n/a

CalculateNewRecordNumber
You still didn't clarify what this is?

Try this for the textBox

textBox2.Text = DateTime.Now.ToString("dd/MM/yy", DateTimeFormatInfo.InvariantInfo);

I still don't understand what you are doing here.

string strChechID1 = "select LoadingNo from LoadItems where LoadingNo=LoadingNo";

Please provide the Create Table statement for this table and provide a sample Input/Output.

-------
EDIT
-------

Ok I understand what loadingNo is now, But then what is the point of RecNo.

i.e. if LoadingNo is 12/05/0003 is the third Invoice or Loading that happened that day. When what is Rec?

Won't Rec be 3 as well?

------------------------

Peace

CalculateNewRecordNumber is use to set format of LoadingNo. So it format is setting is ok.

string strChechID1 = "select LoadingNo from LoadItems where LoadingNo=LoadingNo";

This code is identify Duplicate LoadingNo. I don't know this is right or wrong. I think is when user add record to database. It will add 1 record 1st, after that read next record, if record duplicate, RecNo will +1 and add record to system. Let say, I add five rows records at 1 time. so these five record have same LoadingNo but Different RecNo. This 2 number is use to different each record. Because same month same year can have same record.

Attachments db.jpg 44.82 KB sample.JPG 86.13 KB

CalculateNewRecordNumber
You still didn't clarify what this is?

Try this for the textBox

textBox2.Text = DateTime.Now.ToString("dd/MM/yy", DateTimeFormatInfo.InvariantInfo);

I still don't understand what you are doing here.

string strChechID1 = "select LoadingNo from LoadItems where LoadingNo=LoadingNo";

Please provide the Create Table statement for this table and provide a sample Input/Output.

-------
EDIT
-------

Ok I understand what loadingNo is now, But then what is the point of RecNo.

i.e. if LoadingNo is 12/05/0003 is the third Invoice or Loading that happened that day. When what is Rec?

Won't Rec be 3 as well?

------------------------

Peace

sample data and database structure with attach pic

One thing, I would strongly suggest you to use only one column for the record number.
You have to combine Loading number and Record number. This way you will have a lot less problems.
It sure is possible to create a working code for this kind of dataBase structure you have now, but I repeat my self, if there is no condition to have borh columns, remove one, and have only one.
Anfd there is still one thing missing in the Loading number: a year value. What if the year changes to the next one? You will get duplicates for sure.

Your record number shoud look like "dd/MM/yyyy/record number (4 digits)
So you will run records by date and the record number:
4/5/2011/0001
4/5/2011/0002
4/5/2011/0003
5/5/2011/0001
and so on!!

Isnt this a better, any most important, way simplier idea?

If you answered with yes, you can take a look into my code example, which does this work:

class Program
    {
        private static string connString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=D:\AppsTest\2011\Apr15DB_AutoGenerate\monthAutoGenerateID.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";
        static void Main(string[] args)
        {
            //get this month`s name:
            DateTime date = DateTime.Now;
            string month3Letters = String.Format("{0:MMM}", date);

            //get last id from dataBase:
            string newID = GetLastID();
            if (newID != null)
            {
                string onlyMonthName = newID.Substring(0, 3);
                if (onlyMonthName == month3Letters)
                {
                    //get number from id:
                    int numberID = Convert.ToInt32(newID.Remove(0, 3));
                    //auto-increment to get new id:
                    numberID++;
                    //now we have to add zeros on the left side of the number, so there will always be 4 numbers (or 0123, or 0012, or 0002):
                    string strNumber = numberID.ToString();
                    while (strNumber.Length < 4)
                        strNumber = "0" + strNumber;

                    //add number to the month name:
                    //3.
                    newID = month3Letters + strNumber;
                }
                else
                    //2.
                    newID = month3Letters + "0001";
            }
            else
                //1.
                newID = month3Letters + "0001";

            Console.WriteLine("New id is: {0}.", newID);
            //I have a 2nd column in my test dataBase (Item), so I will insert it:
            string name = "Some item";
            InsertNewRow(newID, name);
            Console.WriteLine("New Row was successfully inserted into database.");
            Console.ReadLine();
        }

        private static string GetLastID()
        {
            string lastID = null;
            using (SqlConnection sqlConn = new SqlConnection(connString))
            {
                string sqlQuery = @"SELECT MAX(ColumnID) FROM MyTable";
                SqlCommand cmd = new SqlCommand(sqlQuery, sqlConn);
                sqlConn.Open();
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        if (reader.GetValue(0) != DBNull.Value)
                            lastID = (string)reader[0];
                    }
                }
            }
            return lastID;
        }

        private static void InsertNewRow(string id, string name)
        {
            using (SqlConnection sqlConn = new SqlConnection(connString))
            {
                string sqlQuery = @"INSERT INTO MyTable VALUES (@id, @name)";
                SqlCommand cmd = new SqlCommand(sqlQuery, sqlConn);
                cmd.Parameters.Add("@id", System.Data.SqlDbType.VarChar, 50).Value = id;
                cmd.Parameters.Add("@name", System.Data.SqlDbType.VarChar, 50).Value = name;
                sqlConn.Open();
                cmd.ExecuteNonQuery();                
            }
        }
    }

You only have to adapt to your needs.
best regards,

Comments
Continous effort. :)

One thing, I would strongly suggest you to use only one column for the record number.
You have to combine Loading number and Record number. This way you will have a lot less problems.
It sure is possible to create a working code for this kind of dataBase structure you have now, but I repeat my self, if there is no condition to have borh columns, remove one, and have only one.
Anfd there is still one thing missing in the Loading number: a year value. What if the year changes to the next one? You will get duplicates for sure.

Your record number shoud look like "dd/MM/yyyy/record number (4 digits)
So you will run records by date and the record number:
4/5/2011/0001
4/5/2011/0002
4/5/2011/0003
5/5/2011/0001
and so on!!

Isnt this a better, any most important, way simplier idea?

If you answered with yes, you can take a look into my code example, which does this work:

class Program
    {
        private static string connString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=D:\AppsTest\2011\Apr15DB_AutoGenerate\monthAutoGenerateID.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";
        static void Main(string[] args)
        {
            //get this month`s name:
            DateTime date = DateTime.Now;
            string month3Letters = String.Format("{0:MMM}", date);

            //get last id from dataBase:
            string newID = GetLastID();
            if (newID != null)
            {
                string onlyMonthName = newID.Substring(0, 3);
                if (onlyMonthName == month3Letters)
                {
                    //get number from id:
                    int numberID = Convert.ToInt32(newID.Remove(0, 3));
                    //auto-increment to get new id:
                    numberID++;
                    //now we have to add zeros on the left side of the number, so there will always be 4 numbers (or 0123, or 0012, or 0002):
                    string strNumber = numberID.ToString();
                    while (strNumber.Length < 4)
                        strNumber = "0" + strNumber;

                    //add number to the month name:
                    //3.
                    newID = month3Letters + strNumber;
                }
                else
                    //2.
                    newID = month3Letters + "0001";
            }
            else
                //1.
                newID = month3Letters + "0001";

            Console.WriteLine("New id is: {0}.", newID);
            //I have a 2nd column in my test dataBase (Item), so I will insert it:
            string name = "Some item";
            InsertNewRow(newID, name);
            Console.WriteLine("New Row was successfully inserted into database.");
            Console.ReadLine();
        }

        private static string GetLastID()
        {
            string lastID = null;
            using (SqlConnection sqlConn = new SqlConnection(connString))
            {
                string sqlQuery = @"SELECT MAX(ColumnID) FROM MyTable";
                SqlCommand cmd = new SqlCommand(sqlQuery, sqlConn);
                sqlConn.Open();
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        if (reader.GetValue(0) != DBNull.Value)
                            lastID = (string)reader[0];
                    }
                }
            }
            return lastID;
        }

        private static void InsertNewRow(string id, string name)
        {
            using (SqlConnection sqlConn = new SqlConnection(connString))
            {
                string sqlQuery = @"INSERT INTO MyTable VALUES (@id, @name)";
                SqlCommand cmd = new SqlCommand(sqlQuery, sqlConn);
                cmd.Parameters.Add("@id", System.Data.SqlDbType.VarChar, 50).Value = id;
                cmd.Parameters.Add("@name", System.Data.SqlDbType.VarChar, 50).Value = name;
                sqlConn.Open();
                cmd.ExecuteNonQuery();                
            }
        }
    }

You only have to adapt to your needs.
best regards,

Actually I'm make a system in C# but this system want link to old system(vb). So that I'm just add the data into old system database. I'm found that have RecNo. So I'm not choice, must be follow old system database. because some other system also follow this. Because this part of system is use to arrange Lorry can take how many thing.

Do u have any looping method to let RecNo increase when detect Same LoadingNo?

Try this

// Getting Max RecordNo
string maxRec = getMaxRec("12/05/0003");

which will be followed by

//WHERE LNo is your current Loading Number.
private static string getMaxRec(string LNo)
{
    string MaxRec;
    try
    {                                                           
         MaxRec =  new OleDbCommand("Select Max(RecNo) + 1 From LoadItems where LoadingNo="+LNo, con).ExecuteScaler();
    }
    catch { MaxRec = 1; }

    return MaxRec;
}

The try catch is for the very first entry.

When it tries to fetch the MaxRecNo for the very first entry of the day where RecNo should be 1 it will get nothing and throw an exception where you catch it and set it to 1.

I didn't try the solution so there maybe some punctuation I may have missed please check it.

This code also Assumes you have LNo already set for the day.

I still don't completely understand your code, but I understand the concept.

I assume you are trying to say that one LoadingNo (LNo) has many RecNo. So RecNo is really the count of different Pallets or Items that go into the Lorry but why not keep it the Product/Serial No of the Pallet/Item?

It may be a better idea for you to go with Mitja Bonca advice as it will be a lot simpler to work with in the future.

Peace.

Edited 5 Years Ago by finito: formatting

Actually I'm make a system in C# but this system want link to old system(vb). So that I'm just add the data into old system database. I'm found that have RecNo. So I'm not choice, must be follow old system database. because some other system also follow this. Because this part of system is use to arrange Lorry can take how many thing.

Anyway, you can still use code i gave you, just a bit modified.

Tell me something, how do you know when you need a new Record number, because of this depends what to look 1st.

Anyway, you can still use code i gave you, just a bit modified.

Tell me something, how do you know when you need a new Record number, because of this depends what to look 1st.

When user click Insert button to add new Record number. I think I can use datagridview row number as RecNo

Try this

// Getting Max RecordNo
string maxRec = getMaxRec("12/05/0003");

which will be followed by

//WHERE LNo is your current Loading Number.
private static string getMaxRec(string LNo)
{
    string MaxRec;
    try
    {                                                           
         MaxRec =  new OleDbCommand("Select Max(RecNo) + 1 From LoadItems where LoadingNo="+LNo, con).ExecuteScaler();
    }
    catch { MaxRec = 1; }

    return MaxRec;
}

The try catch is for the very first entry.

When it tries to fetch the MaxRecNo for the very first entry of the day where RecNo should be 1 it will get nothing and throw an exception where you catch it and set it to 1.

I didn't try the solution so there maybe some punctuation I may have missed please check it.

This code also Assumes you have LNo already set for the day.

I still don't completely understand your code, but I understand the concept.

I assume you are trying to say that one LoadingNo (LNo) has many RecNo. So RecNo is really the count of different Pallets or Items that go into the Lorry but why not keep it the Product/Serial No of the Pallet/Item?

It may be a better idea for you to go with Mitja Bonca advice as it will be a lot simpler to work with in the future.

Peace.

I'm still cannot do it. I'm upload that part of program. Hope u can help me. Thanks.
Put the database at C:\

Anyway, you can still use code i gave you, just a bit modified.

Tell me something, how do you know when you need a new Record number, because of this depends what to look 1st.

I'm attach part of program. Hope you can help me

I'm solve problem already. Thanks all who helping me. Answer is at Text with Bold and underline

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using System.Configuration;

namespace WindowsFormsApplication1
{
    public partial class Form2 : Form
    {
        OleDbCommand sCommand;
        OleDbDataAdapter sAdapter;
        OleDbCommandBuilder sBuilder;
        DataSet sDs;
        DataTable sTable;
        

        public Form2()
        {
            InitializeComponent();
        }

        private void Form2_Load(object sender, EventArgs e)
        {
            timer1.Enabled = true;
            timerLoadingNo.Enabled = true;
        }

        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";
                    //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 = string.Format("{0:yy}", DateTime.Now) + "/" + string.Format("{0:MM}", DateTime.Now) + "/";
                    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 year!
                //increment a year and start from month 1 and starts with number 0001:
                recordYear++;
                //record = recordYear + "/01/0001";
                //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 = string.Format("{0:yy}", DateTime.Now) + "/" + string.Format("{0:MM}", DateTime.Now) + "/";
                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;
            }
            return record;
        }

        private void timerLoadingNo_Tick(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);

            //let textbox display date format
            textBox2.Text = DateTime.Now.ToShortDateString();
            string strFormat = "dd/MM/yy";
            textBox2.Text = DateTime.Now.ToString(strFormat);

            textBox2.Text = record;

            timerLoadingNo.Enabled = false;
        }

        private void timer1_Tick(object sender, EventArgs e)
        {
            ConnectionStringSettings conSettings = ConfigurationManager.ConnectionStrings["MyDBConnectionString"];
            string name = conSettings.ProviderName;
            string providerName = conSettings.ProviderName;
            string ConnectionString = conSettings.ConnectionString;

            string sql = "SELECT * FROM PDC_FG Order by DeliveryDate,PONO,Model,Item";
            OleDbConnection connection = new OleDbConnection(ConnectionString);
            connection.Open();
            sCommand = new OleDbCommand(sql, connection);
            sAdapter = new OleDbDataAdapter(sCommand);
            sBuilder = new OleDbCommandBuilder(sAdapter);
            sDs = new DataSet();
            sAdapter.Fill(sDs, "PDC_FG");
            sTable = sDs.Tables["PDC_FG"];
            connection.Close();
            dataGridView1.DataSource = sDs.Tables["PDC_FG"];
            dataGridView1.ReadOnly = true;

            lblMessage.Text = "";
            dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
            timer1.Enabled = false;
        }

        private void btnLoading_Click(object sender, EventArgs e)
        {
            OleDbCommand cmdCheckID;
            OleDbDataReader dtrCheckID;
            [B][U]var RecNo = 0;[/U][/B]
            foreach (DataGridViewRow r in dataGridView1.SelectedRows)
            {
                [B][U]this.Text = Convert.ToString(RecNo);
                RecNo++;[/U][/B]

                string PONO = r.Cells[0].Value.ToString();
                string Customer = r.Cells[2].Value.ToString();
                string Item = r.Cells[4].Value.ToString();
                string Model = r.Cells[5].Value.ToString();
                string FGrade = r.Cells[6].Value.ToString();
                string Thk = r.Cells[7].Value.ToString();
                string Width = r.Cells[8].Value.ToString();
                string Length = r.Cells[9].Value.ToString();
                string Qty = r.Cells[10].Value.ToString();
                string Remark = r.Cells[21].Value.ToString();

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

                
                OleDbConnection con = new OleDbConnection(ConnectionString);

                con.Open();
                
                string strChechID = "select * from LoadItems where PONo='" + PONO + "' and Model='" + Model + "' and Material='" + FGrade + "'and Thk='" + Thk + "'and Width='" + Width + "' and Length ='" + Length + "' and Item='" + Item + "'";
                cmdCheckID = new OleDbCommand(strChechID, con);
                dtrCheckID = cmdCheckID.ExecuteReader();


                OleDbCommand comm = new OleDbCommand("insert into LoadItems values(@LoadingNo,@RecNo,@PONo,@Cust,@Item,@Model,@Material,@Thk,@Width,@Length,@Qty,@Remark)", con);

                

                try
                {

                    if (dtrCheckID.HasRows)
                    {
                        MessageBox.Show("Duplicate Data!!!!!!");

                        dtrCheckID.Close();


                    }
                    else
                    {
                        dtrCheckID.Close();

                        comm.Parameters.AddWithValue("@LoadingNo", textBox2.Text);
                        comm.Parameters.AddWithValue("@RecNo", RecNo);
                        comm.Parameters.AddWithValue("@PONo", PONO);
                        comm.Parameters.AddWithValue("@Cust", Customer);
                        comm.Parameters.AddWithValue("@Item", Item);
                        comm.Parameters.AddWithValue("@Model", Model);
                        comm.Parameters.AddWithValue("@Material", FGrade);
                        comm.Parameters.AddWithValue("@Thk", Thk);
                        comm.Parameters.AddWithValue("@Width", Width);
                        comm.Parameters.AddWithValue("@Length", Length);
                        comm.Parameters.AddWithValue("@Qty", Qty);
                        comm.Parameters.AddWithValue("@Remark", Remark);


                                comm.ExecuteNonQuery();
                                lblMessage.Text = ("Successful Insert!!!");
                                con.Close();

                                timer1.Enabled = true;
                            }
                    }

                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }

            }
        }

    }
}

Edited 5 Years Ago by lianpiau: n/a

This question has already been answered. Start a new discussion instead.