如何将数据从数据网格视图更新到数据库?错误 - 无法插入值 NULL

本文关键字：数据 错误 插入 NULL 数据库 数据网 网格 更新 视图 | 更新日期: 2023-09-27 18:35:25

我正在尝试将数据从DataGridView更新到我的数据库。当我在谷歌上寻找这个问题的解决方案时，我注意到所有解决方案都是通过使用类变量（对于DataTable，SqlDataAdapter ,...）来管理的。我正在尝试仅通过使用函数变量来执行此操作。

这就是我将数据加载到DataGridView的方式：

private void LoadDataGridView(int ID)
{
    try
    {
         SqlConnection connection = new SqlConnection(connString);
         SqlCommand cmd = new SqlCommand("SELECT SENTENCE FROM Sentences WHERE CategoryID = @ID",connection);
         cmd.Parameters.Add("@ID",SqlDbType.Int).Value = ID;
         DataTable dataTable = new DataTable();
         SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
         dataAdapter.Fill(dataTable);
         DataGridView.DataSource = dataTable;
    }
    catch (Exception)
    {
         MessageBox.Show("ERROR WHILE CONNECTING TO DATABASE!");
    }
}

DataGridView只显示那些与特定 ID 匹配的句子。

这是我到目前为止尝试过的：

private void RefreshBtn_Click(object sender, EventArgs e)
{
    try
    {
          SqlConnection connection = new SqlConnection(connString);
          SqlCommand cmd = new SqlCommand("SELECT SENTENCE FROM Sentences WHERE CategoryID IN(@CategoryID)", connection);
          cmd.Parameters.Add("@CategoryID",SqlDbType.Int).Value = CategoryID;
          SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
          SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(dataAdapter);
          dataAdapter.Update(dataTable);
    }
    catch (Exception)
    {
          MessageBox.Show("ERROR WHILE CONNECTING WITH DATABASE!");
    }
}

这是出现的错误：

无法将值 NULL 插入到列类别 ID、表 ...;列不允许空值。插入失败。该语句已终止。

如何将数据从数据网格视图更新到数据库?错误 - 无法插入值 NULL

如果你的Id数据类型是int，你不需要把它放在"里面。

        private void RefreshBtn_Click(object sender, EventArgs e)
    {
        try
        {
            string connString;//WHAT EVER IT IS
            string Query=string.Format(@"SELECT QUESTION FROM Questions WHERE CategoryID = '{0}'",CategoryID);
              SqlConnection connection = new SqlConnection(connString);
              SqlCommand cmd = new SqlCommand(Query, connection);
    connection.Open();
 cmd.ExecuteNonQuery();
              SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd,connString);
              DataTable DataTable=new DataTable();
             dataAdapter.Fill(DataTable);
             datagridview1.DataSource=DataTable;
        }
        catch (Exception)
        {
              MessageBox.Show("ERROR WHILE CONNECTING WITH DATABASE!");
        }
        catch   (SqlException)
        {
        MessageBox.Show("SqL Error!");
        }
    }

我的问题的解决方案：首先，我在DataGridView中显示了数据库表的所有列，然后隐藏了Primary Key列和CategoryID列。如以下代码所示：

private void LoadDataGridView(int ID)
{
    try
    {
         SqlConnection connection = new SqlConnection(connString);
         SqlCommand cmd = new SqlCommand();
         cmd.CommandText = "SELECT * FROM Sentences WHERE CategoryID = @ID";
         cmd.Connection = connection;
         cmd.Parameters.Add("@ID",SqlDbType.Int).Value = ID;
         dataTable = new DataTable();
         SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
         dataAdapter.Fill(dataTable);
         dataGridView.DataSource = dataTable;
         dataGridView.Columns[0].Visible = false;
         dataGridView.Columns[2].Visible = false;
   }
   catch (Exception)
   {
         MessageBox.Show("ERROR WHILE CONNECTING WITH DATABASE!");
   }
}

然后，我只为CategoryID列设置了默认值，因为Primary Key列有一个选项Auto - Increment .这显示在以下代码中：

private void dataGridView_DefaultValuesNeeded(object sender, DataGridViewRowEventArgs e)
{
    e.Row.Cells["CategoryID"].Value = CategoryID;
}

我做的最后一件事是实现RefreshBtn的代码（以下代码实际上与所讨论的代码相同）：

private void RefreshBtn_Click(object sender, EventArgs e)
{
    try
    {
         SqlConnection connection = new SqlConnection(connString);
         SqlCommand cmd = new SqlCommand();
         cmd.CommandText = "SELECT * FROM Questions WHERE CategoryID = @CategoryID";
         cmd.Connection = connection;
         cmd.Parameters.Add("@CategoryID", SqlDbType.Int).Value = CategoryID;
         SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
         SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(dataAdapter);
         dataAdapter.Update(dataTable);
    }
    catch (Exception)
    {
         MessageBox.Show("ERROR WHILE CONNECTING WITH DATABASE !");
    }
}
相关文章：