When reading from an access database, my code is like this:

 Private Sub PaidNavigateRecords()

        If ds.Tables("Paid").Rows(inc).Item(1) <> "" Then
            txtRanch.Text = ds.Tables("Paid").Rows(inc).Item(1)
        Else
            txtRanch.Text = ""
        End If

        If IsDate(ds.Tables("Paid").Rows(inc).Item(2)) = True Then
            txtDate.Text = ds.Tables("Paid").Rows(inc).Item(2)
        Else
            txtDate.Text = ""
        End If

        If ds.Tables("Paid").Rows(inc).Item(3) <> 0 Then
            txtInvoice.Text = ds.Tables("Paid").Rows(inc).Item(3)
        Else
            txtInvoice.Text = ""
        End If

        If ds.Tables("Paid").Rows(inc).Item(4) <> 0 Then
            txtInvoiceTotal.Text = ds.Tables("Paid").Rows(inc).Item(4)
        Else
            txtInvoiceTotal.Text = ""
        End If

        If IsNumeric(ds.Tables("Paid").Rows(inc).Item(5)) = True Then
            txtAmountPaid.Text = ds.Tables("Paid").Rows(inc).Item(5)
        Else
            txtAmountPaid.Text = ""
        End If

        If IsDate(ds.Tables("Paid").Rows(inc).Item(6)) = True Then
            txtDateOfPayment.Text = ds.Tables("Paid").Rows(inc).Item(6)
        Else
            txtDateOfPayment.Text = ""
        End If

        Try
            If ds.Tables("Paid").Rows(inc).Item(7) <> "" Then
                txtCheckNumber.Text = ds.Tables("Paid").Rows(inc).Item(7)
            Else
                txtCheckNumber.Text = ""
            End If
        Catch ex As Exception
            MessageBox.show(ex.ToString)

        End Try

Its the last bit that gets me. The txtCheckNumber.text. I have it set to text in access, due to the fact that he doesn't pay with a check so AR will just write "American express"...sometimes....
When no data is in the database for this one, I get a:

'System.InvalidCastException was unhandled
Message=Operator '<>' is not defined for type 'DBNull' and string "".'

I've tried everything I can think of to make it just leave the textbox blank and not error out, and nada.
Any ideas?

The trick is to ensure that a NULL is not returned when you query the table. This can be accomplished using the NZ function. For example

SELECT lastName,middleName,firstName FROM mytable

might return a NULL for middleName when there is no entry for that field, however

SELECT lastName,NZ(middleName,""),firstName FROM mytable

will return "" when the actual value of the field is NULL and

SELECT lastName,NZ(middleName,"(none)"),firstName FROM mytable

will return the value "(none)". You can use the NZ function to map any NULL value for any field to a default value.

Thanks, I appriciate the help. I've copied that into my handy notepad file.

This article has been dead for over six months. Start a new discussion instead.