Resolved Conversion from type 'DBNull' to" to type 'Integer' is not valid in OleDbDataReader

rajdh75

Active member
Joined
Mar 30, 2020
Messages
29
Programming Experience
Beginner
Hello,
I have added 6th column to my access database table named as TblInvoice.
I am retrieving data from this column in text box named as TxtRefNo
My code is
VB.NET:
Public Sub InvInfo()
        con.Close()
        con.Open()
        str = "Select * from TblInvoice where InvoiceID=@id"
        cmd = New OleDbCommand(str, con)

        cmd.Parameters.AddWithValue("@id", TxtInvoiceID.Text)
        Dim dr As OleDbDataReader = cmd.ExecuteReader()

        Try
            If dr.Read() Then
                TxtRefNo.Text = dr.GetValue(6)
                dr.Close()
            End If
        Catch ex As Exception
            MsgBox("", ex.Message)
            dr.Close()
        Finally
            con.Close()
        End Try
    End Sub
The code is running well when it has some value, but it gives error when it has no value as -
"Conversion from string "Conversion from type 'DBNull' to" to type 'Integer' is not valid."
How to solve this ?
Thanks in advance.
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,457
Location
Sydney, Australia
Programming Experience
10+
Firstly, data readers are zero-based, like any other list in .NET, so this line:
VB.NET:
TxtRefNo.Text = dr.GetValue(6)
is getting the seventh column, not the sixth.

Secondly, that code is not going to throw that exception because it is converting the value to a String, not an Integer. I'm guessing that you have actually left some code out and you've removed the wrong part.

Anyway, the specific issue is that, as the error message says, you can't just use DBNull objects as though they were other types, because they're not. If your database columns are nullable then it should be obvious that you have to check whether a field contains a value or not before trying to use that value. There are various ways to do that but, when using a data reader, it has its own dedicated method for it. If you're getting an `Integer` and want to use zero where it's NULL, you might do this:
VB.NET:
Dim number = If(myDataReader.IsDBNull(columnIndex),
                0,
                myDataReader.GetInt32(columnIndex))
 

rajdh75

Active member
Joined
Mar 30, 2020
Messages
29
Programming Experience
Beginner
Thanks jmcilhinney.
Actually it was 7th column.
I change code to -
VB.NET:
If dr.Read() Then
                Dim number = If(dr.IsDBNull(6), "", dr.GetValue(6))
                TxtRefNo.Text = number.ToString
                dr.Close()
            End If
Thanks Again.
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,457
Location
Sydney, Australia
Programming Experience
10+
It should also be noted the DBNull.ToString returns an empty String. That means that if you want the value as a String or an empty String for NULL, all you have to do is this:
VB.NET:
TxtRefNo.Text = dr.GetValue(6).ToString()
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,457
Location
Sydney, Australia
Programming Experience
10+
Also, there is absolutely no way that you should be calling dr.Close inside that If block. That means that you will either have to make the same call elsewhere or you won't be closing the reader if there's no data.

Apart from that, there should be no need to call Close because you should be creating the data reader with a Using statement, in which case it will be closed automatically at the end of the block.
VB.NET:
Using reader = command.ExecuteReader()
    If reader.Read() Then
        '...'
    End If
End Using
 
Top Bottom