Checking if Column is empty but i am receiving Error.(Vb.net)

Question

I am trying to check if a column is empty before inserting a date, but I am receiving an error and I don't know how to fix it. I read about ExecuteScalar(), but in my case it doesn't help.

Code:

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

Answer 1

I believe there is a logic error in sql2. How can an existing record have a date of Now? Maybe this is being checked immediately upon insertion. In this case why not make ende a required field?

Don't pull extra data from the server. You are only concerned with Ende in this sub so that is the only field to be retrieved. Then you can used .ExecuteScalar. I think a .ToString might sove the null problem.

Move the Connection.Open to directly before the .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