Question Testing value of an empty datagridView cell?

[ccbryan]

Hi Folks. I am trying to evaluate the contents of my datagridview cells and I'm hitting some difficulties.

My Datagridview is populated on the fly, its AutoGenerateColumns property set to TRUE. Its datasource is a SQl table with four columns of index info and three columns of data. One column (NVAL) is for numeric data, CVAL for alphanumeric, and DVAL for dates. Each record of the table represents a different measurement so only one data column will be populated for a given row. After the user has edited the grid, they can save, which writes the values back to the table using this code (note: clsGenericQuery packages up the SQL call):

Private Sub btnSave_Click(sender As System.Object, e As System.EventArgs) Handles btnSave.Click

        Dim cmd As String
        Dim AC As String = ""   'Attribute Code
        Dim NV As Decimal = 0  'Numeric Value
        Dim CV As String = ""    'Character Value
        Dim DV As Date            'Date Value
        Dim VALtext As String = ""
        For Each row As DataGridViewRow In Me.DataGridView1.Rows
            'remember, cell numbers are zero-based
            AC = row.Cells(3).Value

            If row.Cells(4).Value = "" Then      '***
                NV = 0
            Else
                NV = row.Cells(4).Value
                VALtext = " NVAL = '" & NV & "'"
            End If

            If row.Cells(5).Value.ToString = vbNullString Then
                CV = ""
            Else
                CV = row.Cells(5).Value
                VALtext = " CVAL = '" & CV & "'"
            End If

            If row.Cells(6).Value.ToString = vbNullString Then

            Else
                DV = System.Convert.ToDateTime(row.Cells(6).Value)
                VALtext = "DVAL = '" & DV & "'"
            End If

            If NV = 0 And CV = "" And DV = "12:00AM" Then

            Else
                cmd = "update Attributes set " & VALtext _
                    & "where DeviceSource = '" & My.Settings.GDeviceSource & "' and " _
                    & "DeviceID = '" & My.Settings.GDeviceID & "' and " _
                    & "AttributeCode = '" & AC & "'"

                Dim gqupd As New clsGenericQuery(cmd, My.Settings.Connstring)
            End If
        Next

    End Sub

The problem arises when a table value behind the first data cell -- the NVAL on the first row -- is null. It keeps erroring out on the line marked with ***, the message complaining about casting from dbnull. I have tried numerous syntaxes here, such as

If row.Cells(4).Value = ""
If row.Cells(4).Value > 0
If row.Cells(4).Value = vbNull
If row.Cells(4).Value is vbnull
If row.Cells(4).Value = {}
If system.convert.todecimal(row.Cells(4).Value) is vbNull, > 0, etc.​

When I debug and look at the value of row.cells(4) the value appears to be {}. A null in row.cells(6) [date] in debug, on the other hand, shows as #12:00AM#.

Data-wise, I'm perfectly content for the unused data values to be null. I'd much rather see blanks in my datagridview than zeroes and 1/1/1900s in empty fields. But how can I 'read' a null value in a cell with a decimal field behind it?

Thanks for any help...

 

[jmcilhinney]

When you get data from a database using ADO.NET, null values are represented by DBNull.Value, which is quite different to Nothing, vbNull (which should never be used), an empty String or anything else. Here's an example of how you might test the Value of a nullable Integer cell:

If myCell.Value Is DBNull.Value Then
    MessageBox.Show("The value is null")
Else
    Select Case CInt(myCell.Value)
        Case 0
            MessageBox.Show("The value is zero")
        Case Else
            MessageBox.Show("The value is greater than zero")
    End Select
End If

 

[ccbryan]

Awesome. I feel like I should have gotten there but it was just outside my reach.
Thanks jlmcilhinney!

 

[Lotok]

Out of interest, is the solution you wrote identical in performance terms to ..

If IsDBNull(myvalue) Then 
' do xyz
End If

 

[jmcilhinney]

Out of interest, is the solution you wrote identical in performance terms to ..

If IsDBNull(myvalue) Then 
' do xyz
End If

Almost certainly the IsDBNull function is implemented the way I showed. It's a function so it has to have an implementation and what else would that implementation be but a comparison of the value specified to a known DBNull value? Given that the only way to get a DBNull value is using the Shared Value property, that is what it would be compared to.