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 ConnectionString resolves to "Provider=VFPOLEDB.1;Data
Source=C:\test\REVFILEF.DBF;Mode=Read|Share Deny None;Collating
Sequence=MACHINE"

Private Function DoDBFProcess(byVal InputFileName as String) As Long
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
Try
Conn.ConnectionString = "Provider=VFPOLEDB.1;Data Source=" &
InputFileName & ";Mode=Read|Share Deny None;Collating Sequence=MACHINE"
Conn.Open()
Cmd.CommandType = CommandType.TableDirect
Cmd.Connection = Conn
Read = Cmd.ExecuteReader
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 ConnectionString resolves to "Provider=VFPOLEDB.1;Data
Source=C:\test\REVFILEF.DBF;Mode=Read|Share Deny None;Collating
Sequence=MACHINE"

Private Function DoDBFProcess(byVal InputFileName as String) As Long
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
Try
Conn.ConnectionString = "Provider=VFPOLEDB.1;Data Source=" &
InputFileName & ";Mode=Read|Share Deny None;Collating Sequence=MACHINE"
Conn.Open()
Cmd.CommandType = CommandType.TableDirect
Cmd.Connection = Conn
Read = Cmd.ExecuteReader
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 ToolStripStatusLabel1

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.OleDb

Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click

Me.ToolStripStatusLabel1.Text = String.Empty

Dim InputFileName As String
Dim d As New OpenFileDialog
With d
.Filter = "VFP files (*.dbf)|*.dbf|All files (*.*)|*.* "
If Not d.ShowDialog.Equals(Windows.Forms.DialogResult.OK)
Then Exit Sub
InputFileName = .FileName
.Dispose()
End With

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

End Sub
Private Function DoDBFProcess(ByVal InputFileName As String, _
Optional ByVal MaxNumRecordToRead As
Integer = Integer.MaxValue) As Boolean

Dim DataTableRecords As New DataTable
Dim Connection As New OleDbConnection

Try
With Connection
.ConnectionString = "Provider=VFPOLEDB.1;Data Source="
& InputFileName & ";Mode=Read|Share Deny None;Collating
Sequence=MACHINE"
.Open()
End With
Catch ex As Exception
MsgBox(ex.Message)
Return False
End Try

Dim Sequential As Integer = 0
Try
Using OleDbCommandREAD As New OleDbCommand("Select * from
revfilef", Connection)
Using OleDbDataReader As OleDbDataReader =
OleDbCommandREAD.ExecuteReader()

DataTableRecords = New DataTable
For i As Integer = 0 To OleDbDataReader.FieldCount
- 1
DataTableRecords.Columns.Add(New
DataColumn(OleDbDataReader.GetName(i),
OleDbDataReader.GetFieldType(i)))
Next i

Dim Values(OleDbDataReader.FieldCount - 1) As
Object 'record distinti
While OleDbDataReader.Read()

Sequential += 1
If Sequential > MaxNumRecordToRead Then Exit
While

OleDbDataReader.GetValues(Values)
DataTableRecords.Rows.Add(Values)
End While
Me.ToolStripStatusLabel1.Text = Sequential & "
records read through " & Connection.Provider

End Using
End Using

Me.DataGridView1.DataSource = DataTableRecords
Return True

Catch ex As Exception
MsgBox(ex.Message & vbCrLf & vbCrLf)
Return False
Finally
Connection.Close()
End Try

End Function

End Class