How to fill DataGridView Cells in MYSQL C#?

Question

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.
This is what I tried....

private void TAB_Credit_CellValueChanged(object sender, DataGridViewCellEventArgs e)
        {

            try
            {
                if (TAB_Credit.CurrentCell.ColumnIndex == 0)
                {

                    MySqlDataAdapter sa = new MySqlDataAdapter("SELECT * FROM table WHERE Reference='" + TAB_Credit.Rows[e.RowIndex].Cells["Reference"].Value + "'", MyConnexion);
                    DataTable dt2 = new DataTable();

                    sa.Fill(dt2);

                        double value = (double)TAB_Credit.Rows[e.RowIndex].Cells["Quantite"].Value * (double)TAB_Credit.Rows[e.RowIndex].Cells["PU"].Value;

                        TAB_Credit.Rows[e.RowIndex].Cells["Designation"].Value = dt2.Rows[0]["Designation"].ToString();
                        TAB_Credit.Rows[e.RowIndex].Cells["Quantite"].Value = dt2.Rows[0]["Quantite"].ToString();
                        TAB_Credit.Rows[e.RowIndex].Cells["PU"].Value = dt2.Rows[0]["Prix_Unitaire"].ToString();
                        TAB_Credit.Rows[e.RowIndex].Cells["Total"].Value = value.ToString();


                    }

            }
            catch
            { }
        }

So in the datagrid, when I insert in the Reference Cell nothing is appearing in the other cells. Thank you.

First: Do not write empty try/catch blocks. If anything wrong happens you will never know. Remove and check if there is any exception. Then post the error message — Steve, Apr 29 '17 at 09:58
Second: What do you think will happen if someone types: 1';DROP TABLE References;-- in your cell? Don't do it and look at this http://stackoverflow.com/questions/332365/how-does-the-sql-injection-from-the-bobby-tables-xkcd-comic-work — Steve, Apr 29 '17 at 10:00
the error is coming this `double value = (double)TAB_Credit.Rows[e.RowIndex].Cells["Quantite"].Value * (double)TAB_Credit.Rows[e.RowIndex].Cells["PU"].Value;`, I am having this error : `System.InvalidCastException` — , Apr 29 '17 at 10:05
So the error is relative to invalid values present in your cells. What values are present in the cells? Are these values convertible to a double? — Steve, Apr 29 '17 at 10:45
And that's exactly the problem. A Cell.Value property is of type object. When you write something like _object o = "2500,00"; double d = (double)o;_ you get the invalid cast exception. You need a _Convert.ToDouble(cell.value);_ and be sure that your values are conforming to your locale settings (ie use a comma as decimal separator) — Steve, Apr 29 '17 at 10:59
Thank you for your answer ! is it the best way using CellValueChanged ? Or I must use another event ? — , Apr 29 '17 at 11:04
I prefer to stay away from an event that fires every time you change the cell. This could result in a lot of work for your database and your network giving back poor performance. I would add a button or something that the user clicks to load the data when he has finished to type. But this depends on a lot of considerations that only you are aware of them — Steve, Apr 29 '17 at 11:09

score 0 · Accepted Answer · edited May 23 '17 at 12:02

There are at least two immediate problems into your code:

First: Do not write empty try/catch blocks. If anything wrong happens you will never know. Remove and check if there is any exception.
Second: What do you think will happen if someone types: 1';DROP TABLE References;-- in your cell? Don't do it and look at this

Now, after removing the empty catch we can see that the problem is caused by an invalid cast exception when you try to multiply the values (quantity*PU). This should be resolved with a proper conversion of the two object values coming from the cells.

double qty = Convert.ToDouble(TAB_Credit.Rows[e.RowIndex].Cells["Quantite"].Value);
double pu = Convert.ToDouble(TAB_Credit.Rows[e.RowIndex].Cells["PU"].Value);
double value = qty * pu;

But if these cells are typed directly from your user then also Convert.ToDouble has its drawback. It raises an exception if the input is something like "ABC". In these cases you use double.TryParse and give a message to your user if the input is not a valid double

double qty;
if(!double.TryParse(TAB_Credit.Rows[e.RowIndex].Cells["Quantite"].Value.ToString, out qty))
{
   MessageBox.Show("Invalid input for quantity");
   return;
}

And finally we need to fix the sql injection vulnerability using a parameterized query

string cmdText = @"SELECT * FROM table 
                   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();

Why it does only for the first row ? when I write for the second row nothing is filling... — , Apr 29 '17 at 19:24
No idea, probably you should ask a new question with more details about how do you call this code. More people will be able to assist — Steve, Apr 29 '17 at 19:27
Have a look http://stackoverflow.com/questions/43699944/filling-datagridview-cells-after-validating-first-row-in-c-sharp — , Apr 29 '17 at 19:58

How to fill DataGridView Cells in MYSQL C#?

1 Answers1