OleDBReader and count(*) - No data exists for the row/column.

[edpatterson]

Database for testing consists of table1 with 20 records.

I figured since dr.hasrows returned true that dr.item(0) would contain the count. Just for grins I tried dr.item(1) as well thinking that (0) might contain a null column name.

No combination of []'s or '''s makes any difference.

Ideas?

Imports System.Data
Imports System.Data.OleDb

Module Module1

    Sub Main()
        Dim strConnection As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Database.mdb"
        Dim cn As OleDbConnection = New OleDbConnection(strConnection)
        cn.Open()
        Dim strSQL As String = "select count(*) from table1 where ipaddress is not null"
        Dim cmd As OleDbCommand = New OleDbCommand(strSQL, cn)
        Dim dr As OleDbDataReader = cmd.ExecuteReader
        With dr
            If .HasRows Then
                Debug.Print(.Item(0))
            Else
                Debug.Print("No data returned")
            End If
        End With
        dr.Close()
        cn.Close()
    End Sub

End Module

 

[edpatterson]

In the off chance someone else hits this looking for an answer. It helps if you read a value into the collection before trying to use/display it.

With dr
    If .HasRows Then
       .Read()
        Debug.Print(.Item(0))
    Else
        Debug.Print("No data returned")
    End If
End With