如何在mySQL中检索数据并将其放在datagridview中的特定单元格中

本文关键字：datagridview 单元格 mySQL 检索 数据 | 更新日期: 2023-09-27 18:14:53

我想把列quantity从我的数据库在datagridview，首先有数据加载在它，5列与列quantity一起。现在我试图加载列quantity在我的数据库。下面是我的代码:

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
}

所以我的问题是我要如何把它放在datagridview不删除或覆盖前一个加载在它?

如何在mySQL中检索数据并将其放在datagridview中的特定单元格中

如果我正确理解您，您已经填充了数据的网格，并且您想要更改属于列Quantity的单元格的内容，并引用ID单元格用于查找数据库中更新值的行。

在这种情况下，您不应该再次使用数据表重新绑定网格，而只需执行命令，检索更新的值并为具有请求的ID的行设置单元格Quantity 

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


根据您的评论，现在很清楚，您有许多记录返回的查询，你想要更新许多行在DataGridView。
这可以通过以下更改来实现:

// 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;
    }
}
相关文章：