Search code examples
vb.netoperatorsdbnull

Compare values with 3 states Y/N as String or DbNull

I have database table with a column DECISION which can have the following values:'Y','N', or (null). There can be multiple people updating the values in this database table via an application.

I want to ensure that the DataGridView which displays this information is kept up to date for all users regularly.

I have a background thread which does the following:

For Each new_row As DataRow In dsData.Tables("Progress").Rows
    For Each cur_row As DataRow In dsData.Tables("List").Rows
        If new_row("SEQ") = cur_row("SEQ") And new_row("DECISION") <> cur_row("DECISION") Then
            cur_row("DECISION") = new_row("DECISION")
        End If
    Next
Next

Essentially dsData.Tables("Progress") is populated with the latest data and then compared to the current values in the DataGridView column, based on a sequence number SEQ.

What I want to happen is that if they are different, the cur_row value is changed to that of new_row.

However, it falls over because sometimes it tries to compare a string 'N' or 'Y' with (null) which can't be done.

What is the best way to work around this?

I do want to compare and update any (null) as it is a genuine state in this system.

Solution

  • The TryCast operator can help you here. You can try to cast as type String and a field containing DBNull.Value will simply return Nothing.

    Dim cur_val = TryCast(cur_row("DECISION"), String)
Dim new_val = TryCast(new_row("DECISION"), String)

If new_val IsNot Nothing AndAlso cur_val <> new_val Then
    cur_row("DECISION") = new_val
End If