lianpiau 0 Junior Poster

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

            }

        }
Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.21 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.