Bind Windows Form DataGridView based on another DataGridView row double Click using C# and VB.Net

Last Reply 4 months ago By pandeyism

Posted 4 months ago

how to add previous value of datagridviewwith a new id? when i doubleclick on gridview row then previous value fetch into textbox and i want to again addthis value  with a new ID in datagridview

My project is this I have one tabcontrol and on tabcontrol having two tabpages

One tabpage is OrderDetail and second tabpage is SalesOrder,I show you in below link

In one tabpage have a textboxes and datagridview to input values textboxes behind using a sales table and In datagridview behind using second table of Orderdetail and orderDetail have a foreign key of Salestable

        private void btnInsert_Click(object sender, EventArgs e)
        {
            if (ValidateMasterDetailForm())
            {
                int _OrderNO1 = 0;
                using (SqlConnection sqlCon = new SqlConnection(strConnectionString))
                {
                    sqlCon.Open();
                    //Master[SalesOrderAddOrEdit]
                    //string q = "INSERT INTO SalesOrder(Order_Ref_No,CustomerID,Order_date,Status) VALUES(@Order_Ref_No, @CustomerID, @Order_date, @Status)";
                    SqlCommand sqlCmd = new SqlCommand("[InsertSalesOrderAddOrEdit]", sqlCon);
                    sqlCmd.CommandType = CommandType.StoredProcedure;
                    sqlCmd.Parameters.AddWithValue("@OrderNo", inorderNO1);
                    sqlCmd.Parameters.AddWithValue("@Order_Ref_No", txtOrderRefNo.Text.Trim());
                    sqlCmd.Parameters.AddWithValue("@CustomerID", Convert.ToInt32(cmbCustomerName.SelectedValue.ToString()));
                    sqlCmd.Parameters.AddWithValue("@Order_date", dateTimePicker1.Value);
                    //sqlCmd.Parameters.AddWithValue("@SOpirority", txtSO.Text.Trim());
                    sqlCmd.Parameters.AddWithValue("@Status", cmbStatus.Text);

                    _OrderNO1 = Convert.ToInt32(sqlCmd.ExecuteScalar());
                }
                //Details
                using (SqlConnection sqlCon = new SqlConnection(strConnectionString))
                {
                    sqlCon.Open();
                    foreach (DataGridViewRow dgvRow in dgvOrderDetail.Rows)
                    {
                        if (dgvRow.IsNewRow) break;
                        else
                        {
                           // string q1 = "INSERT INTO OrderDetail(Orderno,CodeItem,orderqty) VALUES(@Orderno, @CodeItem, @orderqty)";
                            SqlCommand sqlCmd = new SqlCommand("Insertorder", sqlCon);
                            sqlCmd.CommandType = CommandType.StoredProcedure;
                            sqlCmd.Parameters.AddWithValue("@IDOD", Convert.ToInt32(dgvRow.Cells["dgvtxtIDOD"].Value == DBNull.Value ? "0" : dgvRow.Cells["dgvtxtIDOD"].Value));
                            sqlCmd.Parameters.AddWithValue("@OrderNo", _OrderNO1);
                            sqlCmd.Parameters.AddWithValue("@CodeItem", Convert.ToInt32(dgvRow.Cells["dgvtxtCodeItem"].Value == DBNull.Value ? "0" : dgvRow.Cells["dgvtxtCodeItem"].Value));
                            sqlCmd.Parameters.AddWithValue("@orderqty", Convert.ToInt32(dgvRow.Cells["dgvorderqty"].Value == DBNull.Value ? "0" : dgvRow.Cells["dgvorderqty"].Value));
                            sqlCmd.ExecuteNonQuery();
                        }
                    }
                }
                FillEmployeeDataGridView();
                Clear();
                MessageBox.Show("Submitted Successfully");
            }
        }
        //Double click on datagridview:
        private void dgvSales_DoubleClick(object sender, EventArgs e)
        {
            if (dgvSales.CurrentRow.Index != -1)
            {
                DataGridViewRow _dgvCurrentRow = dgvSales.CurrentRow;
                inorderNO = Convert.ToInt32(_dgvCurrentRow.Cells[0].Value);
                using (SqlConnection sqlCon = new SqlConnection(strConnectionString))
                {
                    sqlCon.Open();
                    SqlDataAdapter sqlDa = new SqlDataAdapter("salesViewByID", sqlCon);
                    sqlDa.SelectCommand.CommandType = CommandType.StoredProcedure;
                    sqlDa.SelectCommand.Parameters.AddWithValue("@OrderNo", inorderNO);
                    DataSet ds = new DataSet();
                    sqlDa.Fill(ds);

                    //Master - Fill
                    DataRow dr = ds.Tables[0].Rows[0];
                    txtOrderRefNo.Text = dr["Order_Ref_No"].ToString();
                    txtSO.Text = dr["SOpirority"].ToString();
                    cmbCustomerName.SelectedValue = Convert.ToInt32(dr["CustomerID"].ToString());
                    dateTimePicker1.Value = Convert.ToDateTime(dr["Order_date"].ToString());
                    cmbStatus.Text = dr["Status"].ToString();
                  
               
                    dgvOrderDetail.AutoGenerateColumns = false;
                    dgvOrderDetail.DataSource = ds.Tables[1];
                    btnRemove.Enabled = true;
                    tabControl1.SelectedIndex = 0;
                }
            }
        }

 

Posted 4 months ago Modified on 4 months ago

Hi Sadia.net,

Refer below sample.

Database

For this sample I have used of NorthWind database that you can download using the link given below.

Download Northwind Database

Namespaces

C#

using System.Data.SqlClient;

Code

C#

private void Search(object sender, EventArgs e)
{
    DataTable dt = GetData("SELECT CustomerId,Country,City FROM Customers WHERE Country = @Country", txtCountry.Text);
    dataGridView2.DataSource = dt;
}

private DataTable GetData(string query, string data)
{
    SqlConnection con = new SqlConnection(constr);
    SqlCommand cmd = new SqlCommand(query, con);
    cmd.Parameters.AddWithValue("@Country", data);
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    DataTable dt = new DataTable();
    da.Fill(dt);
    return dt;
}

private void dataGridView2_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
{
    if (dataGridView2.CurrentRow.Index != -1)
    {
        DataGridViewRow row = dataGridView2.CurrentRow;
        string country = row.Cells[1].Value.ToString();
        DataTable dt = GetData("SELECT CustomerId,Country,City FROM Customers WHERE Country = @Country", country);
        dataGridView1.DataSource = dt;
    }
}

private void Save(object sender, EventArgs e)
{
    SqlConnection con = new SqlConnection(constr);
    SqlCommand cmd = null;
    if (!string.IsNullOrEmpty(txtCountry.Text))
    {
        cmd = new SqlCommand("INSERT INTO CountryTable(@Country)", con);
        cmd.Parameters.AddWithValue("@Country", txtCountry.Text);
        con.Open();
        cmd.ExecuteNonQuery();
        for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
        {
            cmd = new SqlCommand("INSERT INTO tblDetails(@Id,@City,@Country)", con);
            cmd.Parameters.AddWithValue("@Id", dataGridView1.Rows[i].Cells[0].Value);
            cmd.Parameters.AddWithValue("@Country", dataGridView1.Rows[i].Cells[1].Value);
            cmd.Parameters.AddWithValue("@City", dataGridView1.Rows[i].Cells[2].Value);
            cmd.ExecuteNonQuery();
        }
    }
    con.Close();
    MessageBox.Show("Submitted Successfully");
}

Screenshot