HappyJoni

I'm trying to access a foxpro database and perform the following query in vb.net:

SELECT market, COUNT(cati_id) as idcnt FROM floridaaddress112 GROUP BY market

I'm getting an error that says "Error: no data exists for row/column".
I'm not sure what I'm doing wrong. When I open the database in Foxpro and run the same query it works fine.

Can anyone see where I'm going wrong?? (see code below)



Module Module1

Public Sub databaseTest1()
Dim InputFileName As String
Dim RecordsExported As Long = 0, RowNumber As Long = 0
Dim Conn As New OleDb.OleDbConnection
Dim Cmd As New OleDb.OleDbCommand
Dim Adapt As New OleDb.OleDbDataAdapter
Dim Read As OleDb.OleDbDataReader
Dim Msg As String

Msg = "Error : "
InputFileName = "c:\dev\vendorbuilderpro\vendorbuilderpro\tables\floridaaddress112.dbf "

Try
Conn.ConnectionString = "Provider=VFPOLEDB.1;Data Source=" & _
InputFileName & ";Mode=Read|Share Deny None;Collating Sequence=MACHINE"

Conn.Open()
Cmd.CommandType = CommandType.Text
Cmd.CommandText = "SELECT market, COUNT(cati_id) as idcnt FROM floridaaddress112 GROUP BY market"
Cmd.Connection = Conn
Read = Cmd.ExecuteReader
While Read.HasRows = True
Read.Read()
RowNumber += 1
Console.WriteLine(Read(0).ToString())
End While

Catch ex1 As Exception
Msg += ex1.Message

Console.WriteLine(Msg)
MsgBox(Msg)
Finally
Conn.Close()
Conn = Nothing
End Try

End Sub
End Module



Thanks!
Joni

DrPunk

Seeing as it's a DBF file then I'd suggest trying to connect to it with Microsoft.Jet.OLEDB.4.0 provider rather than FoxPro VFPOLEDB.1.

http://www.connectionstrings.com/dbf-foxpro

HappyJoni

DrPunk,

Thanks for your quick response

Though I'm sure your suggestion is valid, I don't think that the provider I"m using is the issue. Others in my office use the FoxPro VFPOLEDB and it works fine. I really think it's more of something going wrong with my code...

DrPunk

I think you might be right.

I don't use DataReaders much. I'm far more of a DataAdapters kind of guy.

But HasRows doesn't do any reading. Read does the reading...
HasRows doesn't tell it when it is at the end of the resultset, just that it contains some rows, so your While HasRows is making it read after the end of the resultset, hence the error you are seeing. I think.