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

You want to update `Ende` only if the value in the databse is null? — laancelot, May 12 '20 at 13:23
Re "I am receiving an error," what is the specific error you are getting? Which line? — Craig, May 12 '20 at 13:47
I want to update the Ende when is null. If there is already something i don't want to update it and say to the user that the Column is filled. The Error is by the ende=reader0(3) line. — BK201, May 12 '20 at 14:10
It's kind of incredible that we have to ask this at all, never mind twice, but what is the error message? — jmcilhinney, May 12 '20 at 15:57
Does this answer your question? [Conversion from type 'DBNull' to type 'String' is not valid](https://stackoverflow.com/questions/23283360/conversion-from-type-dbnull-to-type-string-is-not-valid) — Craig, May 12 '20 at 18:19
(If that's the problem, please put some effort into searching for an existing answer before asking your question. I found that in the first page of results for "[vb.net] dbnull" and I probably would have found it as the top answer if I'd added "string" as a search term.) — Craig, May 12 '20 at 18:21
Well that's the Error but i think my search was wrong. I searched for Column empty post so that i can understand how the others are doing it. — BK201, May 13 '20 at 06:07

Mary · Accepted Answer · 2020-05-13T18:50:27.720

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

Well i want to check if they have already log out. I am trying to check Today Day with their Personal Number. Ende means end in our DB and i want to see if they have endet their work day. — BK201, May 13 '20 at 06:10
It worked, i never thought taking only the Ende column and checking it. Thanks. — BK201, May 13 '20 at 06:49

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

1 Answers1