datareader and empty records

[cmt9000]

I have a sql query that sometimes returns nothing...not null, just nothing.

This is causing my datareader to error out.

Is there a function, like isDbNull, but is for empty recordsets?

Thanks!

[GaryMazzone]

Is use a line like this:

If DataReader IsNot Nothing Then
   DataReader.Read()
End If

[techgnome]

I think the key is the DataReader.Read method... if there is a row to read, it will return true... if there are no rows, it returns false...

If myDataReader.Read() Then
   'You have data
Else
   'You don't
End if

-tg

[jmcilhinney]

If you want to know whether a DataReader contains any records then you can test its HasRows property. You will also generally use the Read method that tg mentioned in a While loop:

If myDataReader.HasRows Then
    While myDataReader.Read()
        'Read current row.
    End While
Else
    MessageBox.Show("No data returned.")
End If

If you didn't need to notify the user of the empty result set then you'd do away with the If...Else block.

[viper5646]

What if the datareader only has an empty row?

I'm tying to fill this Arraylist, but I get an Specified cast is not valid. error
when I get a DataReader that only has an empty row.
So how can I detect if it has data or not?
I have tried m_dr.hasrows and .isdbnull neither worked.

 Dim x As Integer
        While m_dr.Read
            ModelBuilder_Relation.Add(m_dr.GetInt32(0))
            x = x + 1
        End While

[jmcilhinney]

I don't know where that quote of viper5646's came from from but, if the data reader has an "empty" row, obviously HasRows will return True. An "empty" row is still a row. Presumably an "empty" row is one that contains a DBNull in every field, so using IsDBNull is the answer. Whoever posted that quote in the first place says that they tried IsDBNull but they obviously didn't use it properly. They would have to test every field for DBNull, which you could do by explicitly specifying every field:

If dr.IsDBNull(0) AndAlso dr.IsDBNull(1) AndAlso dr.IsDBNull(2) Then
    'The first three columns are all NULL.
End If

or you could use a loop or LINQ query.

[viper5646]

Thanks jmcilhinney for your reply

when I tried the IsDBnull I get a No data exists for the row/column Error
When it executes the If m_dr.IsDBNull(0) line
This error appears even if there is data in the datareader.
This datareader contains only one column .
This is the code I used to test it.

   If m_dr.IsDBNull(0) Then
            MsgBox("null")
        End If

[jmcilhinney]

Thanks jmcilhinney for your reply

when I tried the IsDBnull I get a No data exists for the row/column Error
When it executes the If m_dr.IsDBNull(0) line
This error appears even if there is data in the datareader.
This datareader contains only one column .
This is the code I used to test it.

   If m_dr.IsDBNull(0) Then
            MsgBox("null")
        End If

Have you called Read? The DataReader doesn't advance to the first row until you call Read.

[viper5646]

Thanks

But there is still a lot that I need to learn about vb.
With your help I have it working.