DataGridView CheckBox列-显示CheckBox但存储字符串

问题描述

这是我的代码:

public partial class Form1 : Form
{
    SqlCommandBuilder cmbl;
    string con = "Data Source=localhost;Initial Catalog=db;Persist Security Info=True;User ID=sa;Password=1234";
    SqlDataAdapter sdaHFP;
    string QueryDgvHFP = "SELECT * FROM HFP";
    DataTable dtHFP;
    SqlConnection cn;

    public Form1()
    {
        InitializeComponent();
    }
    private void Form1(object sender, EventArgs e)
    {
        sdaHFP = new SqlDataAdapter(QueryDgvHFP, con);
        dtHFP = new DataTable();
        sdaHFP.Fill(dtHFP);

        foreach (DataRow item in dtHFP.Rows)
        {
            int n = dgvHFP.Rows.Add();
            dgvHFP.Rows[n].Cells[0].Value = item["HFP"].ToString();
            dgvHFP.Rows[n].Cells[1].Value = item["YearPeriod"].ToString();
            if (item["Active"].ToString() == "Y")
            {
                dgvHFP.Rows[n].Cells[2].Value = true;
            }
            else
            {
                dgvHFP.Rows[n].Cells[2].Value = false;
            };
            dgvHFP.Rows[n].Cells[3].Value = item["ID"].ToString();
        }
    }

那是将数据从Sql Query加载到DataGridView，然后我在该代码中添加了一个用于执行更新或插入的按钮:

That's was load data from Sql Query to DataGridView, And I was add a Button for execute update or insert within this code :

private void btUpdate_Click(object sender, EventArgs e)
{
    try
    {
        cmbl = new SqlCommandBuilder(sdaHFP);
        sdaHFP.Update(dtHFP);
        MessageBox.Show("Success", "Update", MessageBoxButtons.OK, MessageBoxIcon.Information);
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
}

当我在 DataGridView 中更新/插入数据并单击该更新按钮时，将显示成功消息框，但是数据库中的数据未更新或未插入

When I Update / Insert data in DataGridView and click that Update Button, a Success MessageBox appears, but the Data in Database not Updated nor Inserted

请帮助我解决此问题，为什么更新"按钮不起作用?

Please help me solve this problem, why the Update Button doesn't work?

非常感谢您.

推荐答案

您应该将 DataGridView 绑定到 DataTable ，然后在更改单元格值或添加或删除时行，这些更改将应用​​于 DataTable .然后，您可以使用 TableAdapter

You should bind the DataGridView to DataTable, then when you change a cell value or add or remove rows, the changes apply on DataTable. Then you can save data table changes using a TableAdapter:

this.dataGridView1.DataSource = dataTable;

注意:要存储 true/false 或 Yes/No 值，最好使用 bit 数据输入sql server.但是在下面的示例中，我假设您需要将值存储为 Y/N 作为 nvarchar(50)，并且我设置了 DataGridViewCheckBoxColumn 以支持编辑字符串列.

Note: To store true/false or Yes/No values, it's better to use bit data type in sql server. But in the below example I supposed you need to store values as Y/N as nvarchar(50) and I setup a DataGridViewCheckBoxColumn to support editing a string column.

示例

假设我们有一个表 Table1 :

Column1: int, primary key
Column2: nvarchar(50), Allows Null, Containing Y or N as vaule

我们要在 DataGridView 中编辑 Table1 的数据:

And we want to edit data of Table1 in a DataGridView:

以下是可用于加载，编辑和保存数据的代码:

Here is the code you can use to load and edit and save data:

private DataTable table;
private SqlDataAdapter dataAdapter;

private void sampleForm_Load(object sender, EventArgs e)
{
    var command = "SELECT * FROM Table1";
    var connection = @"Your connection string";
    table = new DataTable();
    dataAdapter = new SqlDataAdapter(command, connection);
    var commandBuilder = new SqlCommandBuilder(dataAdapter);
    dataAdapter.Fill(table);

    //Set false as default value for Column2
    table.Columns["Column2"].DefaultValue = false;

    var column1 = new DataGridViewTextBoxColumn();
    column1.Name = "Column1";
    column1.DataPropertyName = "Column1";

    var column2 = new DataGridViewCheckBoxColumn();
    column2.Name = "Column2";
    column2.DataPropertyName = "Column2";
    column2.TrueValue = "Y";
    column2.FalseValue = "N";

    this.dataGridView1.Columns.Add(column1);
    this.dataGridView1.Columns.Add(column2);

    //Bind grid to table
    this.dataGridView1.DataSource = table;
}

private void saveButton_Click(object sender, EventArgs e)
{
    //Save data
    this.dataGridView1.EndEdit();
    dataAdapter.Update(table);
}

这篇关于DataGridView CheckBox列-显示CheckBox但存储字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！