none
Multibye Problem with Foxpro Database files RRS feed

  • Question

  • Hello,

    I've to Foxpro database files (*.dbf). In both there are columns with Strings (First name, Familiy name)...

    If I read in file 1 (older file), I will get the correct data. If I read in file 2, I get only the first character and a blank. I think the second file has a newer format with multibyte. 

    Public DAODBEngine As New DAO.DBEngine

    Public dbDBase     As DAO.Database
    Public TabDBase    As DAO.Recordset

    dbDBase = DAODBEngine.OpenDatabase(Application.StartupPath, False, False, "dBASE IV;")
    TabDBase = dbDBase.OpenRecordset("sample.dbf")
    testValue = TabDBase(3).Value

    This are the steps like I open the file in Basic. In the variable testValue I get only one char, not the whole string.

    Can anybody tell me what is wrong? Do I need another parameter for opening the database or do I need a convertion function?

    Thanks

    Thursday, September 6, 2018 12:31 PM

All replies

  • You might try my solution (which targets dBase III, change that to dBase IV) using OleDb managed data provider.

    https://social.msdn.microsoft.com/Forums/vstudio/en-US/70b81867-20db-4468-be06-fe5b73fe0f1d/how-to-use-dbf-file-to-vbnet?forum=vbgeneral


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, September 6, 2018 1:41 PM
    Moderator
  • Hello Karen,

    thanks for the quick answer.

    I changed my code and I got the dt object of type DataTable.

    How do I get now my field values?

    Thanks in forward,

    Thursday, September 6, 2018 2:11 PM
  • Sorry for the late reply. Here is a conceptional example where I want one field, FirstName

    Public Class DataOperations
        Private FileName As String = IO.Path.Combine(Application.StartupPath, "Customer.dbf")
        Public Function LoadCustomers() As DataTable
            Dim dt As New DataTable
    
            If IO.File.Exists(FileName) Then
                Dim Builder As New OleDbConnectionStringBuilder With
                    {
                        .DataSource = Path.GetDirectoryName(FileName),
                        .Provider = "Microsoft.Jet.OLEDB.4.0"
                    }
    
                Builder.Add("Extended Properties", "dBase IV")
                Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                    Using cmd As New OleDbCommand With {.Connection = cn}
                        cmd.CommandText = "SELECT FirstName FROM " & IO.Path.GetFileName(FileName)
                        cn.Open()
                        dt.Load(cmd.ExecuteReader)
                    End Using
                End Using
            End If
    
            Return dt
    
        End Function
    End Class

    I'm using Console.WriteLine to keep things simple.

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim ops = New DataOperations
        Dim dt = ops.LoadCustomers
    
        For Each row As DataRow In dt.Rows
            Console.WriteLine(row.Field(Of String)("FirstName"))
        Next
    End Sub

    Display in a DataGridView

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim ops = New DataOperations
        DataGridView1.DataSource = ops.LoadCustomers
    End Sub


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, September 6, 2018 4:20 PM
    Moderator
  • Some things to try if you are still getting the same result.

    I think dbf files were subject to Schema.ini files? If that is true then maybe explicitly define the column types. Perhaps DAO is inferring...

    Try ADODB instead of DAO

    Try ODBC connection. Create a DSN and see if you can open it in Excel before trying to create a connection. 


    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

    Thursday, September 6, 2018 6:40 PM
  • Again thanks together.

    I tried the sample from Karen, and it's still the same problem, I get allways only the first character.

    To be honest, I'm a total newbee in Basic and I try to help a friend (his English is not as good). How I have to code it with ADODB and with the schema.ini?

    What I'm wondering, if I open the file with file open in Excel, the data will be shown correct. So Excel itselfs detects automatically the format and reads it in correct.

    Friday, September 7, 2018 5:38 AM
  • Well, forget what I said about the Schema.ini approach, that looks like its strictly for text based files.

    Try again with Karens example, but use ODBC.

    if you want you can send me the dbf


    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

    Friday, September 7, 2018 12:27 PM
  • Perhaps the field is considered a byte array, in that case try the following where I added an identity column and read one field.

    Imports System.Data.OleDb
    Imports System.IO
    
    Public Class DataOperations1
        Private FileName As String = Path.Combine(Application.StartupPath, "Customer.dbf")
        ''' <summary>
        ''' Read one byte array field, first column is a identity column.
        ''' Change someByteField in the SELECT to the field in question
        ''' </summary>
        ''' <returns></returns>
        Public Function LoadCustomers() As DataTable
            Dim dt As New DataTable
    
            dt.Columns.Add(New DataColumn With
                           {
                               .DataType = GetType(Integer),
                               .ColumnName = "Id",
                               .AutoIncrement = True
                           })
    
            dt.Columns.Add(New DataColumn With {.DataType = GetType(String), .ColumnName = "Column1"})
    
            If File.Exists(FileName) Then
                Dim Builder As New OleDbConnectionStringBuilder With
                    {
                        .DataSource = Path.GetDirectoryName(FileName),
                        .Provider = "Microsoft.Jet.OLEDB.4.0"
                    }
    
                Builder.Add("Extended Properties", "dBase IV")
    
                Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                    Using cmd As New OleDbCommand With {.Connection = cn}
    
                        cmd.CommandText = $"SELECT someByteField FROM {Path.GetFileName(FileName)}"
    
                        cn.Open()
    
                        Dim reader = cmd.ExecuteReader
                        If reader.HasRows Then
                            Dim startIndex = 0
                            Dim bufferSize As Integer = 300000
                            Dim outbyte(300000 - 1) As Byte
    
                            While reader.Read
                                ' read data into outbye
                                reader.GetBytes(0, 0, outbyte, 0, bufferSize)
                                ' convert outbye to a string
                                dt.Rows.Add(New Object() {Nothing, Text.Encoding.Unicode.GetString(outbyte)})
                            End While
                        End If
                    End Using
                End Using
            End If
    
            Return dt
    
        End Function
    End Class


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Friday, September 7, 2018 1:35 PM
    Moderator
  • Hello Karen,

    thanks, that looks good, but unfortunetely I got a compiler message "System.InvalidCastException: "Die angegebene Umwandlung ist ungültig."
    "

    In the Reader.GetBytes() function.How can I send you the sample databse?

    Thanks,

    Monday, September 10, 2018 6:29 PM
  • Hello Karen,

    thanks, that looks good, but unfortunetely I got a compiler message "System.InvalidCastException: "Die angegebene Umwandlung ist ungültig."
    "

    In the Reader.GetBytes() function.How can I send you the sample databse?

    Thanks,

    Place the database in your Microsoft OneDrive (if you don't have an account it's totally free) and provide the link here.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, September 10, 2018 6:39 PM
    Moderator
  • Hello Karen,

    thanks,

    please try it with the following sample data:

    https://my.hidrive.com/lnk/mogCHJ6N

    Thanks

    Tuesday, September 11, 2018 5:58 AM
  • Hello, Karen,
    have you had time to look at the database by now?
    Would be great, because unfortunately I can't get any further.

    Thanks,

    Monday, September 17, 2018 11:52 AM
  • Hello, Karen,
    have you had time to look at the database by now?
    Would be great, because unfortunately I can't get any further.

    Thanks,

    No I have not, been away for about a week. Have downloaded the database just now and will look at it tomorrow morning (possible tonight).

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Tuesday, September 18, 2018 12:17 AM
    Moderator
  • Okay, I used the code from this one I mentioned before.

    Imports System.Data.OleDb
    Imports System.IO
    
    Public Class Form1
        Private FileName As String = "C:\Data\Fox\Sample1.DBF"
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim dt As New DataTable
            Dim builder As New OleDbConnectionStringBuilder With
                    {
                    .DataSource = Path.GetDirectoryName(FileName),
                    .Provider = "Microsoft.Jet.OLEDB.4.0"
                    }
            builder.Add("Extended Properties", "dBase III")
            Using cn As New OleDbConnection With {.ConnectionString = builder.ConnectionString}
                Using cmd As New OleDbCommand With {.Connection = cn}
                    cmd.CommandText = "SELECT * FROM " & Path.GetFileName(FileName)
                    cn.Open()
                    dt.Load(cmd.ExecuteReader)
                    DataGridView1.DataSource = dt
                End Using
            End Using
    
            For Each col As DataColumn In dt.Columns
                Console.WriteLine($"{col.ColumnName,12}  {col.DataType.ToString().Replace("System.", "")}")
            Next
        End Sub
    End Class
    

    Columns

              ID  Double
        POSITION  Double
          NUMBER  String
            NAME  String
       FIRSTNAME  String
       SHORTNAME  String
      SALUTATION  String
          STREET  String
             ZIP  String
            TOWN  String
           PHONE  String
           EMAIL  String
           PHOTO  String
        FUNCTION  String
         ARBITR1  String
         ARBITR2  String
         ARBITR3  String
             SEX  Double
        BIRTHDAY  DateTime
        EMPSTART  DateTime
          EMPEND  DateTime
        CALCBASE  Double
          HRSDAY  Double
         HRSWEEK  Double
        HRSMONTH  Double
        HRSTOTAL  Double
        WORKDAYS  String
       DEDUCTHOL  Double
        CFGLABEL  Double
        CBKLABEL  Double
        CBKSCHED  Double
            BOLD  Double
            HIDE  Double
           NOTE1  String
           NOTE2  String
           NOTE3  String
           NOTE4  String
        RESERVED  String

    Imports System.Data.OleDb
    Imports System.IO
    
    Public Class Form1
        Private FileName As String = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Sample1.DBF")
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim dt As New DataTable
            Dim builder As New OleDbConnectionStringBuilder With
                    {
                    .DataSource = Path.GetDirectoryName(FileName),
                    .Provider = "Microsoft.Jet.OLEDB.4.0"
                    }
            builder.Add("Extended Properties", "dBase III")
            Using cn As New OleDbConnection With {.ConnectionString = builder.ConnectionString}
                Using cmd As New OleDbCommand With {.Connection = cn}
                    cmd.CommandText = "SELECT * FROM " & Path.GetFileName(FileName)
                    cn.Open()
                    dt.Load(cmd.ExecuteReader)
                    DataGridView1.DataSource = dt
                End Using
            End Using
    
            'For Each col As DataColumn In dt.Columns
            '    Console.WriteLine($"{col.ColumnName,12}  {col.DataType.ToString().Replace("System.", "")}")
            'Next
    
            For Each row As DataRow In dt.Rows
                Console.WriteLine($"{row.Field(Of Double)("id")}  {row.Field(Of String)("FirstName")}")
            Next
        End Sub
    End Class
    
    



    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    Tuesday, September 18, 2018 10:34 AM
    Moderator
  • Hello Karen,

    like you see in the screenshot of your Gridview, there is the same problem. E.g. the column Firstname you have only one character, but inside the database you have the full name. And the elements of this coulumn are mulitybyte characters.

    If you open the sample database in Excel you will get the full string.

    There must be somewhere a trick to get this columns converted?

    Wednesday, September 19, 2018 5:43 AM
  • Correct, was not even thinking about one letter data. I downloaded the Fox Pro provider and got the same results. Then I used a sample database that came with the driver and read the data perfectly.

    Imports System.Data.OleDb
    Imports System.IO
    
    Public Class Form1
        Private FileName As String = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "customers.DBF")
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim dt As New DataTable
            Dim builder As New OleDbConnectionStringBuilder With
                    {
                    .DataSource = Path.GetDirectoryName(FileName),
                    .Provider = "VFPOLEDB.1"
                    }
    
            Using cn As New OleDbConnection With {.ConnectionString = builder.ConnectionString}
                Using cmd As New OleDbCommand With {.Connection = cn}
                    cmd.CommandText = "SELECT * FROM " & Path.GetFileName(FileName)
                    cn.Open()
                    dt.Load(cmd.ExecuteReader)
                    DataGridView1.DataSource = dt
                End Using
            End Using
    
        End Sub
    End Class
    
    

    Without the purple highlighted file an exception was thrown but with it I read the data fine.

    So with that even with the Fox pro provider your database didn't read properly so I'm guessing even though OleDb and Fox Pro think the First name field is a string (and it is) there must be something else going on preventing reading all characters e.g. an invisible character of some sort. With that I don't know what to tell you.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, September 19, 2018 10:25 AM
    Moderator