Search code examples
.netvb.netms-accesserror-handlingdbnull

Handling dbnull values correctly

I have written code which retrieves an email address from an access db when the username is the same as the user who has logged into my program. I wish to display the email address value in a textbox named Email

Dim x

If ConnectionDb.State = ConnectionState.Closed Then ConnectionDb.Open()
Dim cmd As OleDbCommand = ConnectionDb.CreateCommand
cmd.CommandText = " SELECT ID, EmailAddress, TelephoneNo FROM tblContacts WHERE ID='" & NameVariable & "'"
x = cmd.ExecuteReader()
While x.Read()
    If x("EmailAddress").IsDBNull(0) Then
        Return
    Else
        Email.Text = x.GetString(0)
    End If
 . . . .

However, I receive the following error when the program is run as some users do not have a value for EmailAddress:

System.MissingMemberException: 'Public member 'IsDBNull' on type 'DBNull' not found.' - line 7

I wish to also add functionality for the TelephoneNo value to also be displayed in a textbox on the form. How can I successfully check for null values without the program running into an error?

Solution

  • Depends on you options, Dim x can be a different thing. If option infer is On, this is a reader here 

    Dim x = cmd.ExecuteReader()

    But in your case this is object because with option infer you need to declare and assign on same line. you need to do Dim x as OleDbDataReader. This way you will see intellisence error on IsDBNull. you really run into late binding error here

    you should do this

    If DbNull.Value.Equals(x("EmailAddress")) Then

    This will solve your issue. But you have more issues. Ok, if you select single row/single value, just use ExecuteScalar.

    Or, at least do

    If x.Read() AndAlso Not DbNull.Value.Equals(x("EmailAddress")) Then
    Email.Text = x("EmailAddress").ToString()
End If