IsDBNull not working for me

Navicerts
Navicerts used Ask the Experts™
on
Hello,

I am writing a program in VS 2003 with the .net compact framework.

I have a data reader that returns a value and i want to check if that value is null before trying to convert it to a string but it keeps on crashing *while* it is checking for a null value.

As you can see in the code below, the program crashes at the line "If dr.IsDBNull(0) Then" with the following error message....

"No data exists for the row/column"

I guess I could run a separate query with a count and check to see if the count returns 0 but I don't want to double the number of queries in my program :(

Any ideas?

-Navicerts
Private Function CheckNewCage(ByVal NewCageNumber As Integer) As String
        Dim cn As System.Data.SqlServerCe.SqlCeConnection
        Dim cmd As System.Data.SqlServerCe.SqlCeCommand
        Dim dr As System.Data.SqlServerCe.SqlCeDataReader
        Dim Wingband As Integer
 
        cn = New System.Data.SqlServerCe.SqlCeConnection(constring)
        cmd = New System.Data.SqlServerCe.SqlCeCommand
 
        cn.Open()
        cmd.CommandText = "SELECT ""Wing Band Number"" FROM CageInfo WHERE ""Cage Number"" = " & NewCageNumber
        cmd.Connection = cn
        dr = cmd.ExecuteReader
        dr.Read()
 
        MsgBox("before crash.")
 
        If dr.IsDBNull(0) Then
            MsgBox(".")
            CheckNewCage = "Empty"
        Else
            MsgBox(".")
            Wingband = dr.GetValue(0)
            CheckNewCage = "Full"
            MsgBox("Cage " & NewCageNumber & " already contains wing band number " & Wingband)
        End If
 
        cmd.Dispose()
 
        If cn.State <> ConnectionState.Closed Then
            cn.Close()
        End If
 
    End Function

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
try

IsDBNull(dr(0))

ie use IsDBNull(object) rather than dr.IsDBNull()

Author

Commented:
Thank you for the response.  Unfortunately I am getting the same result with the suggested change (code below).
        MsgBox("before crash.")
 
        If IsDBNull(dr(0)) Then
            MsgBox(".")
            CheckNewCage = "Empty"
        Else
            MsgBox(".")
            Wingband = dr.GetValue(0)
            CheckNewCage = "Full"
            MsgBox("Cage " & NewCageNumber & " already contains wing band number " & Wingband)
        End If

Open in new window

Author

Commented:
I found a solution for this problem in particular without using isdbnull but i still need to find a solution to how i can use isdbnull.  

For this case i found that the following worked.... "If dr.Read() = False Then" but this only works because this time I return only one value.  When I return multiple values and want to check one field for a null this will no longer work so I am still looking for a solution here.

Thanks!
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

oops, I've missed that bit with dr.Read(). you must check it before doing anything with dr

if dr.Read() then
...
end if

Author

Commented:
Yes but dr.read will give a "true" if i am returning multiple columns and leave me with the same problem when i want to check the columns individually for null's before using them.
well, how can you access the data without Read?
dr.Read() reads next row in a cursor returned by command, so you do need to use it before checking anything, otherwise datareader is empty. so once you've read the row, you can check individual fields

Author

Commented:
Ok, but i would check individual fields with isdbnull correct?  That is what I want to figure out how to use.
yes, individual fields you can check with isdbnull()

I've seen cases when something happened with sdf, so dr.Read() returned true but IsDBNull threw an exception

Author

Commented:
Ok, any ideas on the syntax I use for Isdbnull?
what do you mean? it is either dr.IsDBNull() or directly IsDBNull or Convert.IsDBNull(object)
besides, you could access dr data by name

Author

Commented:
I'm a terrible communicator :)

Ignore everything after my original post :)

how do i fix the code in the original post?
what about this

...
if dr.Read() then
        MsgBox("before crash.")
 
        If dr.IsDBNull(0) Then
            MsgBox(".")
            CheckNewCage = "Empty"
        Else
            MsgBox(".")
            Wingband = dr.GetValue(0)
            CheckNewCage = "Full"
            MsgBox("Cage " & NewCageNumber & " already contains wing band number " & Wingband)
        End If
end if

Author

Commented:
It dsoen't crash with that code but it is also impossible for it to ever reach the line "CheckNewCage = "Empty"".

Author

Commented:
"No data exists for the row/column"

See below for where it crashes.  
        cn.Open()
        cmd.CommandText = "SELECT ""Wing Band Number"" FROM CageInfo WHERE ""Cage Number"" = " & NewCageNumber
        cmd.Connection = cn
        dr = cmd.ExecuteReader
        dr.Read()
 
        MsgBox("before crash.")
 
        If dr.IsDBNull(0) Then  <<< IT CRASHES HERE!!!!!!!!!!
            MsgBox("Empty.")
            CheckNewCage = "Empty"
        Else
            MsgBox("Full.")
            Wingband = dr.GetValue(0)
            CheckNewCage = "Full"
            MsgBox("Cage " & NewCageNumber & " already contains wing band number " & Wingband)
        End If

Open in new window

Author

Commented:
The message box below returns "False" regardless of if there is no rows returned or not.  This is the root of my problem.
        cn.Open()
        cmd.CommandText = "SELECT ""Wing Band Number"" FROM CageInfo WHERE ""Cage Number"" = " & NewCageNumber
        cmd.Connection = cn
        dr = cmd.ExecuteReader
        dr.Read()
 
        MsgBox(IsDBNull((0)))

Open in new window

ok, let's look at it from different angle.

install iSql for WM (should be available in VS installation as <something>.dev.*.cab) and try to perform the same select there. besides, I assume you've checked that connection string is OK, the connection is created and opened etc :)

Author

Commented:
Yea, I have found an alternative even though i don't fully understand it the following works...  I still think there is a problem in that I can not do "dr.isdbnull(0)" and get true/false as a result but i may be able to work around it with this.

ill post back with a new question if i run into troubles again.  

Thank you so much for your help and patience!
        cn.Open()
        cmd.CommandText = "SELECT ""Wing Band Number"" FROM CageInfo WHERE ""Cage Number"" = " & NewCageNumber
        cmd.Connection = cn
        dr = cmd.ExecuteReader
 
        Dim bGotData As Boolean = False
 
        While dr.Read()
            bGotData = True
            Wingband = dr.GetValue(0)
            CheckNewCage = "Full"
            MsgBox("Cage " & NewCageNumber & " already contains wing band number " & Wingband)
        End While
 
        If bGotData = False Then
            MsgBox("Empty.")
            CheckNewCage = "Empty"
        End If
 
        cmd.Dispose()

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial