Opening DBF file with ADO.NET

[pfildes]

I'm in the process of migrating from VB6 to VB.NET and need some assistance with opening a DBase IV (.DBF) file using ADO.NET and either OLEDB or ODBC .

As this appears to be quite different from VB6, what with the use of DataAdapters & DataSets (which appear to replace Recordsets), can anybody please help me and preferably provide a sample code snippet to help me along my way?.

Paul

 

[bboffin]

Actually it's not that different to ADO.

First define an ODBC DSN that points to your .xls file.

Assuming you're using Visual Studio .NET drop an ODBCConnection control onto your form and set its ConnectionString property to the appropriate DSN.

Now drop an ODBCDataAdapter onto your form and set it to use the ODBCConnection you just created.

That's the minimum you'll need to do.

In order to read data from the OdbcDataAdapter you use the Fill method. This uses the SelectCommand property of the Data Adapter to query the database so you need to set this up by creating an OdbcCommand object and setting the CommandText to something like "SELECT * FROM MyExcelTable". Then set the SelectCommand property to this OdbcCommand object.

You also need to define somewhere that the data can be retrieved to. Create a new DataTable object called say MyTable. This is broadly equivalent to a Recordset.

Get the data by calling MyDataAdapter.Fill(MyTable)

You can then access the rows and columns of the data using MyTable.

For example:
MyTable.Rows(0)("FirstCol") will return the value of the column named "FirstCol" in the first row of the data.

The big difference from ADO is that MyTable is a snapshot of the data and is completely independent of the original data source so you won't get any locks once the Fill has been completed.

This is a very basic introduction to ADO.NET. Once you've got the basics working take a look at the documentation to understand how to update and insert data.

Bob Boffin

 

[bboffin]

Whoops. I should have refered to dBase IV (or FoxPro) not Excel but the principle is the same.


Bob Boffin

 

[pfildes]

I've implemented the following source code and am getting the message "Feature is not available".

        Dim myFileInfo As System.IO.FileInfo
        Dim myConnectionString As String
        Dim myOLEDBConnection As OleDb.OleDbConnection
        Dim myOLEDBCommand As OleDb.OleDbCommand
        Dim myDataAdapter As OleDb.OleDbDataAdapter
        Dim myDataSet As New DataSet
        Dim myDataTable As New DataTable
        Dim myRow As DataRow
        Dim myColumnCollection As New Collection
        Dim theSurveyDate As Date
        Dim theStartTime As DateTime
        Dim theEndTime As DateTime
        Dim theTrimble As String
        Dim theTableName As String
        Dim aString As String

        'If no filename has been specified
        If (DBFfile.Length = 0) Then Return
        myFileInfo = New System.IO.FileInfo(DBFfile)

        'Determine the trimble form the filename
        theTrimble = myFileInfo.Name.Substring(7, 1)

        'Determine the table name
        theTableName = myFileInfo.Name.Substring(0, myFileInfo.Name.Length - 4)

        Try
            myConnectionString = "Provider=vfpoledb.1" & _
                                 ";DataSource=" & myFileInfo.DirectoryName.Trim & _
                                 ";Collating Sequence=General" & _
                                 ";Mode=Read"

            'Instatiate the database connection
            myOLEDBConnection = New OleDb.OleDbConnection(myConnectionString)

            'Create the SQL command
            myOLEDBCommand = New OleDb.OleDbCommand("SELECT * FROM " & theTableName, _
                                                    myOLEDBConnection)
            'myOLEDBConnection.Open()

            'Open an OLEDB adapter based on the SQL command and use this to
            'create a dataset
            myDataAdapter = New OleDb.OleDbDataAdapter(myOLEDBCommand)
            myDataAdapter.Fill(myDataSet)

            'Read the data in the dataset table
            For Each myDataTable In myDataSet.Tables
                If (myDataTable.TableName.Trim = myFileInfo.Name) Then
                    For Each aString In myDataTable.Columns
                        myColumnCollection.Add(aString)
                    Next
                    For Each myRow In myDataTable.Rows
                        theSurveyDate = myRow.Item("SURVDATE")
                        theStartTime = myRow.Item("STARTTIME")
                        theEndTime = myRow.Item("ENDTIME")

                        'Now determine a photograph matching this date
                        'and time range
                        Call FindPhoto(theTrimble, theSurveyDate, _
                                       theStartTime, theEndTime)
                    Next
                End If
            Next
            myDataTable = Nothing
            myDataSet = Nothing
            myDataAdapter = Nothing

        Catch ex As Exception
            MsgBox("The following error occured whilst trying to read the photograph file;" & Chr(13) & _
            Chr(34) & DBFfile.Trim & Chr(34) & Chr(13) & _
            ex.Message, MsgBoxStyle.Exclamation)
        End Try
    End Sub

Has anybody encountered this before?.

 

[mansii]

It seems that you choose an improper connection string.
The one that you use is for databases in database container.

myFileInfo.DirectoryName.Trim

Reading your code, I think you that you want to connect to a database folder (free table directory).

Here is the one that you should try

"Provider=vfpoledb.1;Data Source=" & myFileInfo.DirectoryName.Trim  & ";Collating Sequence=general"

You might want to see

http://www.connectionstrings.com

Regards,
mansii

 

[pfildes]

Thanks Mansii. I've given up with accessing DBF files and opted to convert them manually to XLS files. I can then open these no problem using the connection string from

http://www.connectionstrings.com