VB2005 -> OLEDB Connectivity question

[Miles]

I have a VB2005 application that has the need to read FoxPro 2.5 DBF/CDX
files. I have the code (below) that opens the connection, but there are never
any records, nor errors, reported... Can anyone tell me what I'm doing
wrong?

The ConnectionStrin g resolves to "Provider=VFPOL EDB.1;Data
Source=C:\test\ REVFILEF.DBF;Mo de=Read|Share Deny None;Collating
Sequence=MACHIN E"

Private Function DoDBFProcess(by Val InputFileName as String) As Long
Dim RecordsExported As Long = 0, RowNumber As Long = 0
Dim Conn As New OleDb.OleDbConn ection
Dim Cmd As New OleDb.OleDbComm and
Dim Adapt As New OleDb.OleDbData Adapter
Dim Read As OleDb.OleDbData Reader
Try
Conn.Connection String = "Provider=VFPOL EDB.1;Data Source=" &
InputFileName & ";Mode=Read|Sha re Deny None;Collating Sequence=MACHIN E"
Conn.Open()
Cmd.CommandType = CommandType.Tab leDirect
Cmd.Connection = Conn
Read = Cmd.ExecuteRead er
While Read.HasRows = True
Read.Read()
RowNumber += 1
'<Does more processing here...>
End While

Catch ex1 As Exception
Msg += ex1.Message
MsgBox(Msg)
Finally
Conn.Close()
Conn = Nothing
End Try
Return RecordsExported
End Function

[tommaso.gastaldi]

Hi Miles,

I have never worked with FoxPro, but I am experienced with several
other RDBMS.
If you can email me just one or more of those file (zipped) to make
some trial, I will be happy to try to see what's wrong and report to
you. if bigger than 2Mb please remove some records, otherwise my mail
box may refuse it. Thanks,

-tom

Miles ha scritto:

I have a VB2005 application that has the need to read FoxPro 2.5 DBF/CDX
files. I have the code (below) that opens the connection, but there are never
any records, nor errors, reported... Can anyone tell me what I'm doing
wrong?

The ConnectionStrin g resolves to "Provider=VFPOL EDB.1;Data
Source=C:\test\ REVFILEF.DBF;Mo de=Read|Share Deny None;Collating
Sequence=MACHIN E"

Private Function DoDBFProcess(by Val InputFileName as String) As Long
Dim RecordsExported As Long = 0, RowNumber As Long = 0
Dim Conn As New OleDb.OleDbConn ection
Dim Cmd As New OleDb.OleDbComm and
Dim Adapt As New OleDb.OleDbData Adapter
Dim Read As OleDb.OleDbData Reader
Try
Conn.Connection String = "Provider=VFPOL EDB.1;Data Source=" &
InputFileName & ";Mode=Read|Sha re Deny None;Collating Sequence=MACHIN E"
Conn.Open()
Cmd.CommandType = CommandType.Tab leDirect
Cmd.Connection = Conn
Read = Cmd.ExecuteRead er
While Read.HasRows = True
Read.Read()
RowNumber += 1
'<Does more processing here...>
End While

Catch ex1 As Exception
Msg += ex1.Message
MsgBox(Msg)
Finally
Conn.Close()
Conn = Nothing
End Try
Return RecordsExported
End Function

[tommaso.gastaldi]

hi Miles,

thank you for the file.

Assume VB2005, Visual Fox Pro OleDb Driver
Free download:
http://www.microsoft.com/downloads/d...displaylang=en

[install the exe, minimal ok, no reboot needed]

Assume a Form1 with DataGridView1 and ToolStripStatus Label1

Posting here (slightly improved version) so that other can propose
improvements (very welcome). If need VB2003 or have problems just let
me know....

-tommaso
[Datatime Free Project]

'----------------------------------------------------------------------------

Imports System.Data.Ole Db

Public Class Form1

Private Sub Button1_Click(B yVal sender As System.Object, ByVal e As
System.EventArg s) Handles Button1.Click

Me.ToolStripSta tusLabel1.Text = String.Empty

Dim InputFileName As String
Dim d As New OpenFileDialog
With d
.Filter = "VFP files (*.dbf)|*.dbf|A ll files (*.*)|*.* "
If Not d.ShowDialog.Eq uals(Windows.Fo rms.DialogResul t.OK)
Then Exit Sub
InputFileName = .FileName
.Dispose()
End With

If Me.DoDBFProcess (InputFileName) Then
Me.ToolStripSta tusLabel1.Text = "Records read OK"

End Sub
Private Function DoDBFProcess(By Val InputFileName As String, _
Optional ByVal MaxNumRecordToR ead As
Integer = Integer.MaxValu e) As Boolean

Dim DataTableRecord s As New DataTable
Dim Connection As New OleDbConnection

Try
With Connection
.ConnectionStri ng = "Provider=VFPOL EDB.1;Data Source="
& InputFileName & ";Mode=Read|Sha re Deny None;Collating
Sequence=MACHIN E"
.Open()
End With
Catch ex As Exception
MsgBox(ex.Messa ge)
Return False
End Try

Dim Sequential As Integer = 0
Try
Using OleDbCommandREA D As New OleDbCommand("S elect * from
revfilef", Connection)
Using OleDbDataReader As OleDbDataReader =
OleDbCommandREA D.ExecuteReader ()

DataTableRecord s = New DataTable
For i As Integer = 0 To OleDbDataReader .FieldCount
- 1
DataTableRecord s.Columns.Add(N ew
DataColumn(OleD bDataReader.Get Name(i),
OleDbDataReader .GetFieldType(i )))
Next i

Dim Values(OleDbDat aReader.FieldCo unt - 1) As
Object 'record distinti
While OleDbDataReader .Read()

Sequential += 1
If Sequential > MaxNumRecordToR ead Then Exit
While

OleDbDataReader .GetValues(Valu es)
DataTableRecord s.Rows.Add(Valu es)
End While
Me.ToolStripSta tusLabel1.Text = Sequential & "
records read through " & Connection.Prov ider

End Using
End Using

Me.DataGridView 1.DataSource = DataTableRecord s
Return True

Catch ex As Exception
MsgBox(ex.Messa ge & vbCrLf & vbCrLf)
Return False
Finally
Connection.Clos e()
End Try

End Function

End Class