I want compare 2 datagridview table for calculation purpose. This is because this 2 table is come from different database. 1 is access and 1 is sql server. therefore I cannot join table.So I have 3 datagridview table to display data. I'm want datagridview3 ItemPerPack Column * datagridview1 Qty when they add to next table. datagridview2 is for select item purpose.when user key in PONO,qty and select item in datagridview2. then this detail will add into datagridview1
//display data into datagridview3 from Microsoft SQl server 2008.
//This table column are FGrade,Thk,Width,Length,ItemPerPack
private void Form1_Load(object sender, EventArgs e)
{
ConnectionStringSettings conSettings = ConfigurationManager.ConnectionStrings["MyDBConnectionString_ReportSystem"];
string name = conSettings.ProviderName;
string providerName = conSettings.ProviderName;
string ConnectionString = conSettings.ConnectionString;
string sql = "SELECT * FROM LabelQtyDining";
SqlConnection connection = new SqlConnection(ConnectionString);
connection.Open();
sCommand = new SqlCommand(sql, connection);
sAdapter = new SqlDataAdapter(sCommand);
sBuilder = new SqlCommandBuilder(sAdapter);
sDs = new DataSet();
sAdapter.Fill(sDs, "LabelQtyDining");
sTable = sDs.Tables["LabelQtyDining"];
connection.Close();
dataGridView3.DataSource = sDs.Tables["LabelQtyDining"];
dataGridView3.ReadOnly = true;
dataGridView3.Columns[5].HeaderText = "Item per Pack";
dataGridView3.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
}
//display data into datagridview2 from Access Database (Product list)
//This table column are Model,FGrade,Thk,T_Unit,Width,W_Unit,Length,L_Unit,Qty
private void Product()
{
ConnectionStringSettings conSettings = ConfigurationManager.ConnectionStrings["MyDBConnectionString"];
string name = conSettings.ProviderName;
string providerName = conSettings.ProviderName;
string ConnectionString = conSettings.ConnectionString;
string sql = "SELECT * From Product Where Cust='" + cboCustomer.Text + "' ORDER BY Model,FGrade";
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, "Product");
sTable = sDs.Tables["Product"];
connection.Close();
dataGridView2.DataSource = sDs.Tables["Product"];
dataGridView2.ReadOnly = true;
dataGridView2.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
}
//select row in datagridview
private void btnAddGoods_Click(object sender, EventArgs e)
{
OleDbCommand cmdCheckID;
OleDbDataReader dtrCheckID;
foreach (DataGridViewRow r in dataGridView1.SelectedRows)
{
string PONO = r.Cells[0].Value.ToString();
string DeliveryDate = r.Cells[1].Value.ToString();
string Customer = r.Cells[2].Value.ToString();
string Name = r.Cells[3].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 T_Unit = r.Cells[8].Value.ToString();
string Width = r.Cells[9].Value.ToString();
string W_Unit = r.Cells[10].Value.ToString();
string Length = r.Cells[11].Value.ToString();
string L_Unit = r.Cells[12].Value.ToString();
string Qty = r.Cells[13].Value.ToString();
string StockIn = r.Cells[14].Value.ToString();
string Shortage = r.Cells[15].Value.ToString();
string ItemPerPackage = r.Cells[16].Value.ToString();
string Package = r.Cells[17].Value.ToString();
string Remain = r.Cells[18].Value.ToString();
Boolean InternalOrder = Convert.ToBoolean(r.Cells[19].Value.ToString());
Boolean Loading = Convert.ToBoolean(r.Cells[20].Value.ToString());
Boolean Finished = Convert.ToBoolean(r.Cells[21].Value.ToString());
Boolean Urgent = Convert.ToBoolean(r.Cells[22].Value.ToString());
string ReceivedDate = r.Cells[23].Value.ToString();
string Remark = r.Cells[24].Value.ToString();
//Connectt to Database
ConnectionStringSettings conSettings = ConfigurationManager.ConnectionStrings["MyDBConnectionString"];
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 PDC_InHand where PONO='" + PONO + "' and Model='" + Model + "' and FGrade='" + 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 PDC_InHand values(@PONO,@DeliveryDate,@Customer,@Name,@Item,@Model,@FGrade,@Thk,@T_Unit,@Width,@W_Unit,@Length,@L_Unit,@Qty,@StockIn,@Shortage,@ItemPerPackage,@Package,@Remain,@InternalOrder,@Loading,@Finished,@Urgent,@ReceivedDate,@Remark)", con);
OleDbCommand comm1 = new OleDbCommand("Delete From PDC where PONO=@PONO and Model=@Model and FGrade=@FGrade and Thk=@Thk and Width=@Width and Length=@Length and Item=@Item", con);
try
{
if (dtrCheckID.HasRows)
{
MessageBox.Show("Duplicate Data!!!!!!");
dtrCheckID.Close();
}
else
{
dtrCheckID.Close();
comm.Parameters.AddWithValue("@PONO", PONO);
comm.Parameters.AddWithValue("@DeliveryDate", DeliveryDate);
comm.Parameters.AddWithValue("@Customer", Customer);
comm.Parameters.AddWithValue("@Name", Name);
comm.Parameters.AddWithValue("@Item", Item);
comm.Parameters.AddWithValue("@Model", Model);
comm.Parameters.AddWithValue("@FGrade", FGrade);
comm.Parameters.AddWithValue("@Thk", Thk);
comm.Parameters.AddWithValue("@T_Unit", T_Unit);
comm.Parameters.AddWithValue("@Width", Width);
comm.Parameters.AddWithValue("@W_Unit", W_Unit);
comm.Parameters.AddWithValue("@Length", Length);
comm.Parameters.AddWithValue("@L_Unit", L_Unit);
comm.Parameters.AddWithValue("@Qty", Qty);
comm.Parameters.AddWithValue("@StockIn", StockIn);
comm.Parameters.AddWithValue("@Shortage", Shortage);
comm.Parameters.AddWithValue("@ItemPerPackage", ItemPerPackage);
comm.Parameters.AddWithValue("@Package", Package);
comm.Parameters.AddWithValue("@Remain", Remain);
comm.Parameters.AddWithValue("@InternalOrder", InternalOrder);
comm.Parameters.AddWithValue("@Loading", Loading);
comm.Parameters.AddWithValue("@Finished", Finished);
comm.Parameters.AddWithValue("@Urgent", Urgent);
comm.Parameters.AddWithValue("@ReceivedDate", ReceivedDate);
comm.Parameters.AddWithValue("@Remark", Remark);
comm1.Parameters.AddWithValue("@PONO", PONO);
comm1.Parameters.AddWithValue("@Model", Model);
comm1.Parameters.AddWithValue("@FGrade", FGrade);
comm1.Parameters.AddWithValue("@Thk", Thk);
comm1.Parameters.AddWithValue("@Width", Width);
comm1.Parameters.AddWithValue("@Length", Length);
comm1.Parameters.AddWithValue("@Item", Item);
comm.ExecuteNonQuery();
comm1.ExecuteNonQuery();
lblMessage.Text = ("Successful Insert!!!");
con.Close();
timerLoad.Enabled = true;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
}