检查数据库中的单元格是否为null的正确方法是什么

Question

我有一个员工考勤项目。这是我的桌子：

CREATE TABLE [dbo].[tbl_attendanceSheet] 
(
[Id]           INT          IDENTITY (1, 1) NOT NULL,
[memberCode]   NVARCHAR(20) NULL,
[name]         NVARCHAR(20) NULL,
[date]         NVARCHAR(20) NULL,
[clockin]      NVARCHAR(20) NULL,
[clockout]     NVARCHAR(20) NULL,
[delay]        NVARCHAR(20) NULL,
[HouresWorked] NVARCHAR(20) NULL,
[desc]         NVARCHAR (150) NULL,
PRIMARY KEY CLUSTERED ([Id] ASC)
);

我想为员工管理，每当他们在文本框中输入代码时，程序会记录他们的进入或离开时间。

出于某种原因，我必须检查一些单元格是否为空。

private void textBoxX1_KeyDown(object sender, KeyEventArgs e)
{
sqlcon.Close();
sqlcon.Open();
if (e.KeyCode == Keys.Enter)
{
string t = lbl_Time.Text;
string d = lbl_Date.Text;
string selectQueryName = "SELECT name FROM tbl_attendanceMembers where memberCode=" + "'" + textBoxX1.Text + "'";
var sqlcmdName = new SqlCommand(selectQueryName, sqlcon);
var resultName = sqlcmdName.ExecuteScalar();
string selectQueryId = "SELECT MAX(id) FROM tbl_attendanceSheet";
var sqlcmdId = new SqlCommand(selectQueryId, sqlcon);
var resultId = sqlcmdId.ExecuteScalar();
(1)if (resultId != null)
{
string selectQueryCockin = "SELECT Clockin FROM tbl_attendanceSheet where id=" + "resultId";
var sqlcmdCockin = new SqlCommand(selectQueryCockin, sqlcon);
var resultCockin = sqlcmdId.ExecuteScalar();
(2)if (resultCockin != null)
{
(3)if (resultName != null)
{
this.lbl_mmbrname.Text = resultName.ToString();
this.lbl_timestored.Text = t;
textBoxX1.Clear();                       
}    
}
}
else //if result id == null
{
sqlcon.Open();
SqlCommand sqlcmdClockin = new SqlCommand("InputClockIn", sqlcon);
sqlcmdClockin.CommandType = CommandType.StoredProcedure;
sqlcmdClockin.Parameters.AddWithValue("@InputDate", d);
sqlcmdClockin.Parameters.AddWithValue("@InputTime", t);
sqlcmdClockin.ExecuteNonQuery();
SqlDataAdapter sqlda = new SqlDataAdapter("SELECT * FROM tbl_attendanceMembers", sqlcon);
DataTable dt = new DataTable();
sqlda.Fill(dt);
dataGridView1.DataSource = dt;
}
sqlcon.Close();
}
}

只要这些点中的等号是"==">，它就会跳到其他部分。

但是，每当这些点中的等号为"！=">时，就会通过所有if。这种现象与数据库中相对单元格的无效性无关。

我做错了什么？

我已经通过断点检查程序好几次了。。。

我检查了相对单元格为空和非空的不同情况。我不知道怎么了。

第一个sqlcon.close();是因为如果我不写它，它会说连接没有关闭，这对我来说没有意义，因为我在下面写了一个sqlcon.close();。。。

Answer 1

要检查数据库值是否为空，请使用DBNull.Value

if (resultId != DBNull.Value) { 

Answer 2

您需要检查DBNull.Value，并处理可能出现的任何异常

private void textBoxX1_KeyDown(object sender, KeyEventArgs e)
{
sqlcon.Close();
sqlcon.Open();
if (e.KeyCode == Keys.Enter)
{
try{
string t = lbl_Time.Text;
string d = lbl_Date.Text;
string selectQueryName = "SELECT name FROM tbl_attendanceMembers where memberCode=" + "'" + textBoxX1.Text + "'";
var sqlcmdName = new SqlCommand(selectQueryName, sqlcon);
var resultName = sqlcmdName.ExecuteScalar();
if (resultName != DBNull.Value)
{
string selectQueryId = "SELECT MAX(id) FROM tbl_attendanceSheet";
var sqlcmdId = new SqlCommand(selectQueryId, sqlcon);
var resultId = sqlcmdId.ExecuteScalar();
if (resultId != DBNull.Value)
{
string selectQueryCockin = "SELECT Clockin FROM tbl_attendanceSheet where id=" + "resultId";
var sqlcmdCockin = new SqlCommand(selectQueryCockin, sqlcon);
var resultCockin = sqlcmdId.ExecuteScalar();
if (resultCockin != DBNull.Value
{
this.lbl_mmbrname.Text = resultName.ToString();
this.lbl_timestored.Text = t;
textBoxX1.Clear();  
}
}
}
else //if result id == null
{
sqlcon.Open();
SqlCommand sqlcmdClockin = new SqlCommand("InputClockIn", sqlcon);
sqlcmdClockin.CommandType = CommandType.StoredProcedure;
sqlcmdClockin.Parameters.AddWithValue("@InputDate", d);
sqlcmdClockin.Parameters.AddWithValue("@InputTime", t);
sqlcmdClockin.ExecuteNonQuery();
SqlDataAdapter sqlda = new SqlDataAdapter("SELECT * FROM tbl_attendanceMembers", sqlcon);
DataTable dt = new DataTable();
sqlda.Fill(dt);
dataGridView1.DataSource = dt;
}
} catch (Exception ex) {
//Do somethging with the exception if desired
} finally {
sqlcon.Close();
}
}
}