C#与MySQL表单程序插入学生时遇Column 'Name' cannot be null错误求助

问题现象 #

执行插入操作时弹出错误：

Student not inserted.
Column "Name" cannot be null

将数据库字段设为允许Null后，插入的数据所有字段均为null，无法保存填写的学生信息。

问题根源 #

1. AddStudent方法参数绑定错误：代码中添加了未在SQL语句中使用的@StudentID参数，且赋值为整个Student对象，导致参数绑定混乱，实际需要的属性值未正确传递到SQL。
2. UpdateStudent方法缺少WHERE条件：UPDATE语句未指定更新的目标记录，会批量修改表中所有数据，同时未绑定ID参数。
3. 可能存在Student对象属性未正确赋值的情况：调用AddStudent时，传入的Student对象的Name、Reg等属性未从表单控件获取值。

修正后的代码 #

using MySql.Data.MySqlClient;
using System;
using System.Data;
using System.Windows.Forms;

namespace MySQL_CRUD
{
    class DbStudent
    {
        public static MySqlConnection GetConnection()
        {
            string sql = "datasource=localhost;port=3306;username=root;password=;database=student";
            MySqlConnection con = new MySqlConnection(sql);
            try 
            { 
                con.Open();
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("MySQL Connection Error! \n" + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            return con;
        }

        public static void AddStudent(Student std)
        {
            // 明确指定插入列，避免字段顺序依赖
            string sql = "INSERT INTO student_table (Name, Reg, Class, Section) VALUES (@StudentName, @StudentReg, @StudentClass, @StudentSection)";
            using (MySqlConnection con = GetConnection())
            {
                MySqlCommand cmd = new MySqlCommand(sql, con);
                cmd.CommandType = CommandType.Text;
                // 移除多余参数，绑定正确属性
                cmd.Parameters.Add("@StudentName", MySqlDbType.VarChar).Value = std.Name;
                cmd.Parameters.Add("@StudentReg", MySqlDbType.VarChar).Value = std.Reg;
                cmd.Parameters.Add("@StudentClass", MySqlDbType.VarChar).Value = std.Class;
                cmd.Parameters.Add("@StudentSection", MySqlDbType.VarChar).Value = std.Section;
                try
                {
                    cmd.ExecuteNonQuery();
                    MessageBox.Show("Added successfully.", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                catch (MySqlException ex)
                {
                    MessageBox.Show("Student not inserted. \n" + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            } // using块自动关闭连接，无需手动调用Close
        }

        public static void UpdateStudent(Student std, string id)
        {
            // 添加WHERE子句指定更新的目标记录
            string sql = "UPDATE student_table SET Name = @StudentName, Reg = @StudentReg, Class = @StudentClass, Section = @StudentSection WHERE ID = @StudentID";
            using (MySqlConnection con = GetConnection())
            {
                MySqlCommand cmd = new MySqlCommand(sql, con);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("@StudentName", MySqlDbType.VarChar).Value = std.Name;
                cmd.Parameters.Add("@StudentReg", MySqlDbType.VarChar).Value = std.Reg;
                cmd.Parameters.Add("@StudentClass", MySqlDbType.VarChar).Value = std.Class;
                cmd.Parameters.Add("@StudentSection", MySqlDbType.VarChar).Value = std.Section;
                // 绑定ID参数
                cmd.Parameters.Add("@StudentID", MySqlDbType.VarChar).Value = id;
                try
                {
                    cmd.ExecuteNonQuery();
                    MessageBox.Show("Updated successfully.", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                catch (MySqlException ex)
                {
                    MessageBox.Show("Student not updated. \n" + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }
        }

        public static void DeleteStudent(string id)
        {
            string sql = "DELETE FROM student_table WHERE ID = @StudentID";
            using (MySqlConnection con = GetConnection())
            {
                MySqlCommand cmd = new MySqlCommand(sql, con);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("@StudentID", MySqlDbType.VarChar).Value = id;
                try
                {
                    cmd.ExecuteNonQuery();
                    MessageBox.Show("Deleted successfully.", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                catch (MySqlException ex)
                {
                    MessageBox.Show("Student not deleted. \n" + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }
        }

        public static void DisplayAndSearch(string query, DataGridView dgv)
        {
            using (MySqlConnection con = GetConnection())
            {
                MySqlCommand cmd = new MySqlCommand(query, con);
                MySqlDataAdapter adp = new MySqlDataAdapter(cmd);
                DataTable tbl = new DataTable();
                adp.Fill(tbl);
                dgv.DataSource = tbl;
            }
        }
    }
}

额外检查要点 #

• 确保Student对象属性正确赋值：调用AddStudent前，必须从表单控件（如TextBox）获取输入值并赋值给Student对象，示例代码：

  Student newStudent = new Student();
  newStudent.Name = txtName.Text.Trim();
  newStudent.Reg = txtReg.Text.Trim();
  newStudent.Class = txtClass.Text.Trim();
  newStudent.Section = txtSection.Text.Trim();
  DbStudent.AddStudent(newStudent);
  

• 验证数据库表结构：确认student_table的字段名与代码中使用的一致（如代码用Name、Reg，表中不能是student_name、student_reg等）。
• 使用using块管理连接：修改后的代码通过using块自动释放数据库连接资源，避免资源泄漏问题。

内容的提问来源于stack exchange，提问作者Edvin Parmeza