how to retrieve data in mySQL and put it in specific cell in the datagridview

Question

I want to put the column quantity from my database in the datagridview which in the first place has data loaded in it, 5 columns together with the column quantity. Now I tried to load the column quantity in my database. Here is my code:

using (MySqlConnection con = new MySqlConnection(serverstring))
{
    string query = @"SELECT quantity 
                    FROM tblOrder_Products
                    WHERE order_ID=@ID";

    con.Open();
    using (MySqlCommand cmd = new MySqlCommand(query, con))
    {
        DataTable dt = new DataTable();

        cmd.Parameters.AddWithValue("@ID", txtboxID.Text);
        MySqlDataReader dr = cmd.ExecuteReader();
        dt.Load(dr);

        dr.Close();


        dataGridView2.DataSource = dt;
        // I want to change this line or this part of code because 
        // I want to put only the column `quantity` which means
        //retaining the data loaded previously in the datagridview
}

So my question is how am I going to put it in the datagridview without deleting or overwriting the previous one loaded in it?

Steve · Accepted Answer · 2013-05-23T10:08:12.210

If I understand you correctly, you have already the grid filled with data and you want to change the content of a cell belonging to the column Quantity and referencing a row where the ID cell is used to find the updated value in the database.

In this case you shouldn't rebind the grid again using the datatable, but just execute the command, retrieve the updated value and set the cell Quantity for the row with the ID requested

using (MySqlCommand cmd = new MySqlCommand(query, con))
{
    cmd.Parameters.AddWithValue("@ID", txtboxID.Text);
    object result = cmd.ExecuteScalar();
    if(result != null)
    {
        int quantity = Convert.ToInt32(result);
        // Now you need to find the row that contains the ID passed 
        DataGridViewRow row = grid.Rows
                             .Cast<DataGridViewRow>()
                             .Where(r => r.Cells["ID"].Value.ToString().Equals(txtBoxID.Text))
                             .First();
        row.Cells["Quantity"].Value = quantity;
    }
}

UPDATE
Following your comment, now it is clear that you have many records returned by the query, and you want to update many rows in the DataGridView.
This could be achieved with the following changes:

// The query returns also the Variant column from the database
// The column is needed to identify the corresponding row to update on the datagridview
// Also I am supposing that the variant column is from the same table (JOIN required otherwise)
string query = @"SELECT variant, quantity 
                FROM tblOrder_Products
                WHERE order_ID=@ID";

con.Open();
using (MySqlCommand cmd = new MySqlCommand(query, con))
{
    cmd.Parameters.AddWithValue("@ID", txtboxID.Text);

    // Cannot use a ExecuteScalar, we need a SqlDataReader to loop over the results
    SqlDataReader reader = cmd.ExecuteReader();
    while(reader.Read())
    {
        int quantity = reader.GetInt32(1);

        // Now I am supposing the the Variant column is of string type, change the Get 
        // accordingly if it is not 
        string v = reader.GetString(0);

        // Use the value retrieved from the database to identify the row to update
        DataGridViewRow row = grid.Rows
                             .Cast<DataGridViewRow>()
                             .Where(r => r.Cells["variant"].Value.ToString().Equals(v))
                             .First();
        row.Cells["Quantity"].Value = quantity;
    }
}

Yes that is what I want to do. Thank you. I'll try your code. — Harvey, May 23 '13 at 09:35
I'm trying to understand the code but when I read the comment `Now you need to find the row that contains the ID passed` it means that the datagridview belongs to many but in my case it isn't because that datagridview belongs only to one ID. Like for example in my case I have a column named `variant` and each variant name has its `quantity` but that belongs only in one ID. — Harvey, May 23 '13 at 09:53
But this means that your query returns more than one DataRow. If this is true then you need to change the query to return also an identifier (the variant column) that could help to find the DataGridViewRow to update with the new quantity loaded from the database (and you cannot use ExecuteScalar) — Steve, May 23 '13 at 10:00
Yes it returns many rows. Okay...ahmm so what is the right query?Am I going to use a join? to match the variant name loaded in the datagridview to its quantity from the database? — Harvey, May 23 '13 at 10:08
In this line `DataGridViewRow row = grid.Rows` grid.Rows isn't working. What I mean is it didn't appear in the intellisense. — Harvey, May 23 '13 at 10:18
Hello! It is me again Sir. By the way, I just want to change the code that you posted, instead of using `DataGridViewRow` I want to use `DataRow` because I also change my mind that instead of using `DataGridView` I want to use `DataTable`. I tried to change your code a little bit but it gives me an error. — Harvey, May 26 '13 at 14:55

how to retrieve data in mySQL and put it in specific cell in the datagridview

1 Answers1