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)
            txtRanch.Text = ""
        End If

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

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

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

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

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

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

        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?

Recommended Answers

All 2 Replies

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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.