无法将值NULL插入列';EmpId';
本文关键字:EmpId 插入列 NULL | 更新日期: 2023-09-27 18:21:18
我有一个Employee模型/类和一个等效的Employees数据库。当我在数据库中插入一个没有年龄值的新员工时,它不起作用。我使用的是windowsform,我已经在数据库和c#代码中定义了age和maritalStatus列为null。
- 当我在表单中填写所有4个文本框值时,它就起作用了
- 当我填写Firstname,Lastname,age&离开已婚在我的表单中,文本框为空,它有效(即它创建了一个新员工在数据库中将已婚列设置为null)
- 当我为名字、姓氏、已婚和在我的表单中保留"年龄"文本框为空,它不起作用
请帮忙,我不知道我做错了什么。
员工类别:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace NullableTypeDatabase
{
public class Employee
{
int empID;
string firstName;
string lastName;
int? married;
int? age;
public int EmpID
{
get { return empID; }
set { empID = value; }
}
public string FirstName
{
get { return firstName; }
set { firstName = value; }
}
public string LastName
{
get { return lastName; }
set { lastName = value; }
}
public int? Married
{
get { return married; }
set { married = value; }
}
public int? Age
{
get { return age; }
set { age = value; }
}
}
}
WINFORM代码:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace NullableTypeDatabase
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void ClickSaveNewUser(object sender, EventArgs e)
{
if (ValidateStringInputFields() && ValidateIntInputFields())
{
int? convertAge = int.Parse(txtAge.Text);
if(NullableTypeDB.AddNewEmployee(txtFname.Text, txtLname.Text, comBoxMaritalStatus.SelectedIndex, convertAge) == 1)
{
MessageBox.Show("A New Employee Record has been Successfully Added");
}
}
}
private bool ValidateStringInputFields()
{
var controls = new[] { txtFname, txtLname};
bool isValid = true;
foreach (var control in controls.Where(e => String.IsNullOrEmpty(e.Text)))
{
errorProviderTxtBoxes.SetError(control, "Please fill the required field");
isValid = false;
}
return isValid;
}
private bool ValidateIntInputFields()
{
bool isValid = false;
int resultAge;
if(int.TryParse(txtAge.Text, out resultAge))
{
isValid = true;
}
return isValid;
}
}
}
访问数据库的类:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
namespace NullableTypeDatabase
{
public class NullableTypeDB
{
public static SqlConnection GetConnection()
{
string connectionString = @"Data Source=(LocalDB)'v11.0;AttachDbFilename=c:'users'Z'documents'visual studio 2013'Projects'NullableTypeDatabase'NullableTypeDatabase'EmployeeInfo.mdf;Integrated Security=True";
SqlConnection connection = new SqlConnection(connectionString);
return connection;
}
public static int AddNewEmployee(string fname, string lname, int? maritalStat, int? age)
{
int returnAffectedRow;
SqlConnection con = GetConnection();
string queryStatement = "INSERT INTO Employee(firstname, lastname, married, age) VALUES (@fname, @lname, @maritalStat, @age)";
SqlCommand sqlCmd = new SqlCommand(queryStatement, con);
sqlCmd.Parameters.AddWithValue("@fname", fname);
sqlCmd.Parameters.AddWithValue("@lname", lname);
if(maritalStat == 0)
{
string convertIndex0 = "True";
sqlCmd.Parameters.AddWithValue("@maritalStat", convertIndex0);
}
else if (maritalStat == 1)
{
string convertIndex1 = "False";
sqlCmd.Parameters.AddWithValue("@maritalStat", convertIndex1);
}
else
{
sqlCmd.Parameters.AddWithValue("@maritalStat", DBNull.Value);
}
//if (age.HasValue)
//{
// sqlCmd.Parameters.AddWithValue("@age", age.Value);
//}
//else if (age.GetValueOrDefault() == 0)
//{
// sqlCmd.Parameters.AddWithValue("@age", DBNull.Value);
//}
// sqlCmd.Parameters.AddWithValue("@age", age == 0? DBNull.Value : (object)age);
sqlCmd.Parameters.AddWithValue("@age", age.HasValue? age.Value : (object)DBNull.Value);
try
{
con.Open();
returnAffectedRow = sqlCmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
return returnAffectedRow;
}
}
}
int? convertAge = int.Parse(txtAge.Text);
这条线是你的问题。您正在解析一个空字符串,但它只是失败了(我想)。在此处查找int.TryParse()如何将字符串解析为可为null的int
您说代码允许它为null,但实际上并没有。在下面的Validate方法中,如果没有在文本框中输入有效的Age,此方法将返回false,因此不会执行更新。
private bool ValidateIntInputFields()
{
bool isValid = false;
int resultAge;
if(int.TryParse(txtAge.Text, out resultAge))
{
isValid = true;
}
return isValid;
}
此外,您的按钮中的代码代码假设这一行总是有一个年龄:
int? convertAge = int.Parse(txtAge.Text);
如果你想让年龄为零,你需要这样做:
private bool ValidateIntInputFields()
{
if(string.IsNullOrEmpty(txtAge.Text))
return true;
bool isValid = false;
int resultAge;
if(int.TryParse(txtAge.Text, out resultAge))
{
isValid = true;
}
return isValid;
}
和
private void ClickSaveNewUser(object sender, EventArgs e)
{
if (ValidateStringInputFields() && ValidateIntInputFields())
{
int? convertAge = null;
if( !string.IsNullOrEmpty(txtAge.Text) )
convertAge = int.Parse(txtAge.Text);
if(NullableTypeDB.AddNewEmployee(txtFname.Text, txtLname.Text, comBoxMaritalStatus.SelectedIndex, convertAge) == 1)
{
MessageBox.Show("A New Employee Record has been Successfully Added");
}
}
}