Filling datagridview cells after validating first row in C#

[Jim_11]

I have a DataGridView of 4-5 columns.
The DataGridView is Editable.
When I enter a value in the Reference Column then immediately it will fill the other values in the others cells from mysql database.
But when I go to the next row and I want to add another value in the reference cell and then it will fill the other like the first one, it didn't do it...
This is what I tried....

private void TAB_Credit_CellEndEdit(object sender, DataGridViewCellEventArgs e)
{
    if (TAB_Credit.Columns[e.ColumnIndex].Name == "Reference")
    {
 
         string cmdText = @"SELECT * FROM tb_ajout_articles 
                            WHERE Reference=@ref";
         MySqlDataAdapter sa = new MySqlDataAdapter(cmdText, MyConnexion);
         sa.SelectCommand.Parameters.Add("@ref", MySqlDbType.VarChar).Value =
         TAB_Credit.Rows[e.RowIndex].Cells["Reference"].Value.ToString();
 
         DataTable dt2 = new DataTable();
         sa.Fill(dt);
 
         if (dt.Rows.Count == 1)
         {
             TXT_Stock.Text = dt.Rows[0]["Quantite"].ToString();
             //double value = (double)TAB_Credit.Rows[e.RowIndex].Cells["Quantite"].Value * (double)TAB_Credit.Rows[e.RowIndex].Cells["Prix_Unitaire"].Value;
 
             TAB_Credit.Rows[e.RowIndex].Cells["Designation"].Value = dt.Rows[0]["Designation"].ToString();
             TAB_Credit.Rows[e.RowIndex].Cells["Quantite"].Value = dt.Rows[0]["Quantite"].ToString();
             TAB_Credit.Rows[e.RowIndex].Cells["Prix_Unitaire"].Value = dt.Rows[0]["Prix_Unitaire"].ToString();
             TAB_Credit["Total", e.RowIndex].Value = 
               Convert.ToString(
               Convert.ToInt32(TAB_Credit["Quantite", e.RowIndex].Value) * 
               Convert.ToInt32(TAB_Credit["Prix_Unitaire", e.RowIndex].Value));
               //TAB_Credit.Rows[e.RowIndex].Cells["Total"].Value = value.ToString();
 
             TXT_Brut.Text = TAB_Credit["Total", e.RowIndex].Value.ToString();
 
         }
     }
     if (TAB_Credit.CurrentCell.ColumnIndex == 6)
     {
         if (Convert.ToInt32(TAB_Credit.Rows[e.RowIndex].Cells["Quantite"].Value) > Convert.ToInt32(TXT_Stock.Text))
         {
             MessageBox.Show("Stock finished");
             TAB_Credit.Rows[e.RowIndex].Cells["Quantite"].Value = dt.Rows[0]["Quantite"].ToString();
 
         }
         else
         {
             TAB_Credit["Total", e.RowIndex].Value = 
                   Convert.ToString(
                   Convert.ToInt32(TAB_Credit["Quantite", e.RowIndex].Value) * 
                   Convert.ToInt32(TAB_Credit["Prix_Unitaire", e.RowIndex].Value));
             TXT_Brut.Text = TAB_Credit["Total", e.RowIndex].Value.ToString();
         }
     }
 }

My app is a sort of cash register for a shop so in the datagrid there is Reference ,
it is the reference of the product.
When the user types the reference directly the information about the product will be filled in the other columns and the user can choose the quantity he wants and it will do the calculation.

ALSO, I tried to put the total of the bill in the datagrid...

Reference | Product |Quantity |Price |Total |Total Bill
AB |AB |2 |1000 |2000 |5000
BC |BC |3 |1000 |3000

This is what I am trying to do... To do it, I tried this code:

private void TAB_Credit_RowValidated(object sender, DataGridViewCellEventArgs e)
{
    int sum = 0;

    for (int i = 0; i < TAB_Credit.Rows.Count; ++i)
    {

        sum += Convert.ToInt32(TAB_Credit.Rows[i].Cells["Total"].Value);
    }

    TAB_Credit.Rows[e.RowIndex].Cells["Total_Fact"].Value = sum.ToString();
}

[rproffitt]

Just one question. Since this is highly likely to be in Visual Studio, did you break on line 11 in that last code passage to see the sum of all things? You might have to put a debug line after that such as an alertbox to show the sum then break on that next line.

In short, you have code, but I didn't read where you knew how to debug.

[tinstaafl]

Another way to approach this is to use the DataTable Compute method. Here's some simple code using the Northwinds database. first we created the datable with the columns we need:

Type typeInt = Type.GetType("System.Int32");
Type typeString = Type.GetType("System.String");
Type typeDecimal = Type.GetType("System.Decimal");
ds.Tables.Add("Order");

ds.Tables["Order"].Columns.AddRange
    (new DataColumn[]
    {
        new DataColumn("ProductName",typeString),
        new DataColumn("ProductID",typeInt),
        new DataColumn("SupplierID",typeInt),
        new DataColumn("CategoryID",typeInt),
        new DataColumn("QuantityPerUnit",typeString),
        new DataColumn("UnitPrice",typeDecimal),
        new DataColumn("Quantity",typeInt),
        new DataColumn("Extension",typeDecimal)
    });
dataGridView2.DataSource = ds.Tables["Order"];

In the CellEndEdit handler, all that's needed is to decide which cell was just edited, using DataGridView2.CurrentCell, and act accordingly. My thought is to grab the appropriate line from the database add add it to the datatable with the Fill method:

private void dataGridView2_CellEndEdit(object sender, DataGridViewCellEventArgs e)
{
    if(dataGridView2.CurrentCell.OwningColumn.Name == "ProductName")
    {
        if(dataGridView2.CurrentCell.Value.ToString() != "")
        {

            OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=NWind.mdb");
            string query = $"SELECT ProductName,ProductID,SupplierID,CategoryID,QuantityPerUnit,UnitPrice FROM Products WHERE ProductName='{dataGridView2.CurrentCell.Value}'";
            OleDbCommand cmd = new OleDbCommand(query, conn);
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            dataGridView2.DataSource = null;
            da.Fill(ds.Tables["Order"]);
            for(int i = 0; i < ds.Tables["Order"].Rows.Count;i++)
            {
                if(ds.Tables["Order"].Rows[i].IsNull(1))
                {
                    ds.Tables["Order"].Rows.RemoveAt(i--);
                }
            }
            dataGridView2.DataSource = ds.Tables["order"];
        }
        else // if the user presses enter on an empty line
        {
        //Add the total row
            DataRow dr = ds.Tables["Order"].NewRow();
            dr.SetField<string>("ProductName", "Total");
            decimal total = (decimal)ds.Tables["Order"].Compute("Sum(Extension)", "");
            dr.SetField<decimal>("Extension",total) ;
            ds.Tables["Order"].Rows.Add(dr);
        }
    }
    //Calculate the extension
    else if(dataGridView2.CurrentCell.OwningColumn.Name == "Quantity")
    {
        dataGridView2.CurrentRow.Cells["Extension"].Value = (int)dataGridView2.CurrentRow.Cells["Quantity"].Value * (decimal)dataGridView2.CurrentRow.Cells["UnitPrice"].Value;
    }
}

There are more things that can be done to flesh this out, add AutoComplete collection with the product names, etc.. However this should be enough to point you in the right track.