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());
}
}
}
