sql - 检查列是否为空但我收到错误。(Vb.net)
问题描述
我试图在插入日期之前检查一列是否为空,但我收到一个错误,我不知道如何修复它。我读过关于ExecuteScalar()
,但在我的情况下它没有帮助。
代码:
Dim sql As String = "UPDATE [TA-Arbeitszeit] SET Ende = @ende WHERE Personal_nr = @Personal_nr AND Arbeitstag = @Arbeitstag"
Dim sql2 As String = "SELECT * FROM [TA-Arbeitszeit] WHERE Personal_nr = @Personal_Nr AND Arbeitstag = @Arbeitstag"
Using conn2 As New OleDbConnection(connectionString),
cmd2 As New OleDbCommand(sql2, conn2)
conn2.Open()
cmd2.Parameters.Add("@Personal_nr", OleDbType.VarChar).Value = tbxUserInput.Text.Trim()
cmd2.Parameters.Add("@Arbeitstag", OleDbType.VarChar).Value = DateTime.Now.ToString("d")
Dim ende As String
Using reader0 As OleDbDataReader = cmd2.ExecuteReader()
reader0.Read()
ende = reader0(3)
End Using
If String.IsNullOrEmpty(ende) Then
Using conn As New OleDbConnection(connectionString),
cmd As New OleDbCommand(sql, conn)
conn.Open()
cmd.Parameters.Add("@ende", OleDbType.VarChar).Value = DateTime.Now.ToString("G")
cmd.Parameters.Add("@Personal_nr", OleDbType.VarChar).Value = tbxUserInput.Text.Trim()
cmd.Parameters.Add("@Arbeitstag", OleDbType.VarChar).Value = DateTime.Now.ToString("d")
Dim icount As Integer = cmd.ExecuteNonQuery
End Using
frmGreetings.Label1.Text = eveninggreetingsAray()
frmGreetings.Label2.Text = "Sie haben Ihren Arbeitstag beendet."
frmGreetings.ShowDialog()
tbxUserInput.Select()
Else
frmNotificationBox.Label1.Text = "Ihren Arbeitstag ist schon beendet!"
frmNotificationBox.ShowDialog()
tbxUserInput.Select()
End If
End Using
解决方案
我相信sql2中存在逻辑错误。现有记录怎么会有现在的日期?也许这会在插入时立即进行检查。在这种情况下,为什么不将 ende 设为必填字段?
不要从服务器中提取额外的数据。您只关心此子中的 Ende,因此这是唯一要检索的字段。然后你可以使用 .ExecuteScalar。我认为 .ToString 可能会解决 null 问题。
将 Connection.Open 移到 .Execute 之前
Private connectionString As String = "Your connection string"
Private Sub OpCode()
'How can Arbeitstag be Now in an existing record?
'Dim sql2 As String = "SELECT * FROM [TA-Arbeitszeit] WHERE Personal_nr = @Personal_Nr AND Arbeitstag = @Arbeitstag"
Dim sql2 As String = "Select Ende From [TA-Arbeitszeit] WHERE Personal_nr = @Personal_Nr AND Arbeitstag = @Arbeitstag;"
Dim ende As String
Using conn2 As New OleDbConnection(connectionString),
cmd2 As New OleDbCommand(sql2, conn2)
cmd2.Parameters.Add("@Personal_nr", OleDbType.VarChar).Value = tbxUserInput.Text.Trim()
'I don't quite see how an existing record can have today's date
cmd2.Parameters.Add("@Arbeitstag", OleDbType.VarChar).Value = DateTime.Now.ToString("d")
conn2.Open() 'Move the .Open to directly befor the .Execute
ende = cmd2.ExecuteScalar.ToString
End Using
If String.IsNullOrEmpty(ende) Then
'Move this closer to where is is used
Dim sql As String = "UPDATE [TA-Arbeitszeit] SET Ende = @ende WHERE Personal_nr = @Personal_nr AND Arbeitstag = @Arbeitstag"
Using conn As New OleDbConnection(connectionString),
cmd As New OleDbCommand(sql, conn)
conn.Open()
cmd.Parameters.Add("@ende", OleDbType.VarChar).Value = DateTime.Now.ToString("G")
cmd.Parameters.Add("@Personal_nr", OleDbType.VarChar).Value = tbxUserInput.Text.Trim()
cmd.Parameters.Add("@Arbeitstag", OleDbType.VarChar).Value = DateTime.Now.ToString("d")
Dim icount As Integer = cmd.ExecuteNonQuery
End Using
frmGreetings.Label1.Text = eveninggreetingsAray()
frmGreetings.Label2.Text = "Sie haben Ihren Arbeitstag beendet."
frmGreetings.ShowDialog()
tbxUserInput.Select()
Else
frmNotificationBox.Label1.Text = "Ihren Arbeitstag ist schon beendet!"
frmNotificationBox.ShowDialog()
tbxUserInput.Select()
End If
End Sub
推荐阅读
- python - 使用无服务器框架为 AWS Lambda 构建和使用本地包
- algorithm - 为什么我的 Rust StackDFS 实现会产生不正确的结果?
- time - 提取连续几年出现的ID
- python - django中的models.ForeignKey()
- typescript - TypeScript:差异通用约束与参数类型
- r - R:MICE 和向后逐步回归
- swift - 消息气泡的双击识别器(Messagekit)
- android - Android Retrofit 2:以编程方式将超时时间增加 10 秒
- javascript - 将日期转换为 UTC 时间戳
- animation - 有没有办法让一个 Three.js 对象位置固定在某个点,而其他对象在动画结束前移动