Reading a dbase file

[jccorner]

Im trying to read a dbase file using the following code:

Dim dbstr as String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Temp;Extended Properties=dBase III"

Try
  myConn = New OleDbConnection(dbstr)
  myCmd = New OleDbCommand("Select [DB1Last] From [ACC.DBF]", myConn)
  myConn.Open()
  myReader = myCmd.ExecuteReader()  <-- error occurs here
  MsgBox(CStr(myReader(0)))
  myReader.Close()
Finally
  myConn.Close()
End Try

When I try to read the database I get the error: "Unable to locate the requested xbase memo file." Can anyone please help me with this?? And yes, the dbf file does reside in the C:\Temp folder. Ive also tried changing the string to include brackets and not include brackets. Ive been looking all over the place for some working code to access a dbf file using vb.net.

 

[ZeroEffect]

Im trying to read a dbase file using the following code:

Dim dbstr as String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Temp;Extended Properties=dBase III"

Try
  myConn = New OleDbConnection(dbstr)
  myCmd = New OleDbCommand("Select [DB1Last] From [ACC.DBF]", myConn)
  myConn.Open()
  myReader = myCmd.ExecuteReader()  <-- error occurs here
  MsgBox(CStr(myReader(0)))
  myReader.Close()
Finally
  myConn.Close()
End Try

When I try to read the database I get the error: "Unable to locate the requested xbase memo file." Can anyone please help me with this?? And yes, the dbf file does reside in the C:\Temp folder. Ive also tried changing the string to include brackets and not include brackets. Ive been looking all over the place for some working code to access a dbf file using vb.net.

Here Is what I use for accessing DBase files

Imports Microsoft.Data.Odbc Download and Install the MS ODBC .Net Data Provider

    Public Function GetProjectTable() As Data.DataSet
        On Error Resume Next
        Dim Itm As ListViewItem
        Dim I As Long
        Dim strConnect, strSql As String
        Dim objDA As New OdbcDataAdapter
        Dim objDS As New Data.DataSet
        Dim intCounter As Integer

        Define a connection string with file path as parameter
        strConnect = "Provider=MSDASQL/SQLServer ODBC;Driver={Microsoft Visual FoxPro Driver};" _
          & "SourceType=DBF;SourceDB="C: BackSlash DAD BackSlash Files";InternetTimeout=300000;Transact Updates=True"
For some reason my BackSlashes are being removed
        This simple query is all u need to extract data. Make sure you specify filename 
        with extension
        strSql = "SELECT FNAME, NAME, CUTCOUNT FROM PLIST.dbf"
        objDA = New OdbcDataAdapter(strSql, strConnect)
        objDA.Fill(objDS, "PLIST") PLIST is the file name, PLIST.dbf

        ListView1.Items.Clear()

        For I = 0 To objDS.Tables("PLIST").Rows.Count - 1
            With objDS.Tables("PLIST").Rows(I)
                Itm = ListView1.Items.Add(!FNAME)
                Itm.SubItems.Add(!Name)
                Itm.SubItems.Add(!CutCount)
            End With
        Next I
        objDS.Dispose()
        ListView1.Items(1).Selected = True
        PlayListForm.ProgressBar1.Value = "0"
    End Function

You will have to install the Microsoft ODBC .Net Data Provider to get this to work, here is the link.

http://www.microsoft.com/downloads/details.aspx?FamilyID=6ccd8427-1017-4f33-a062-d165078e32b1&displaylang=en

Now if anyone else knows of a better way of doing this Im all ears... uh eyes. Especially if it make the data access even faster.

Hope this helps

ZeroEffect

 

[jccorner]

Thanks Zero for your reply. Unfortunately I am getting an error after copying your code. I get ERROR [42502][Microsoft][ODBC Visual FoxPro Driver]Not a table.

I know the data is not unreadable in the dbf file because I downloaded a dbase viewer that was written in C that shows me the data. Now Im trying to see if first I can view thedata from a vb.net program and then I want to try to add new rows to the file. Greatly appreciate any help anyone can provide.

 

[ZeroEffect]

Thanks Zero for your reply. Unfortunately I am getting an error after copying your code. I get ERROR [42502][Microsoft][ODBC Visual FoxPro Driver]Not a table.

I know the data is not unreadable in the dbf file because I downloaded a dbase viewer that was written in C that shows me the data. Now Im trying to see if first I can view thedata from a vb.net program and then I want to try to add new rows to the file. Greatly appreciate any help anyone can provide.

Are you able to post a copy of the dbf?

 

[ZeroEffect]

Thinking about it more you need to know the name of the table in the dbf file usually its the file name or atleast it has been with the dbf files I have been using. It seems to me that the table you want to look through is not in the dbf file .If you post the file or email it to me I can look and see what is going on.

ZeroEffect

 

[jccorner]

Ive found a dll that can browse dbf files and was unable to add it to my .Net project since it was written in C++. I tried to use Aximp to create a .Net wrapper using this command:

C:\>AxImp.exe C:\Temp\CDBFAI.DLL

I receive the error: Error loading type library/DLL.

I tried using the keywords source and out as well but no luck. Has anyone gotten this error and resolved it??

 

[FZelle]

which means it is a native dll, not COM nor .NET.

The errormessage in your first post shows you whats missing.

In DBase the MEMO fields where put into a second file with the name .DBT.
And this is missing.

Or you change the Select to something without the Memo-field.

 

[jccorner]

Thanks alot FZelle. When I added the dbt file to the folder it started working.

But now I get a "query too complex" message when I try to update or delete a record. This is because Im using OleDb which only allows 64k of information per record. Is there an alternative way I can use to update the record or somehow get around the error message?? I didnt understand why the delete didnt work but it gave me the same message and Im unable to change the structure of the records. Thanks for your help.

 

[FZelle]

No, there is no real solution to that.

The only way i know is to create a "real" DB and import everything.

Why do you want to stay on DBase?

 

[jccorner]

I understand I could do that, but here is the situation. I have a client who uses a third party program to process a state approved report. Now we are in the works to also get our report state approved unfortunately this can take up to a year to accomplish. The third party program is using dbase on the backend. Ive found that if I can manipulate the records in the dbase file, the client can still use their third party program to print the report.

So its either find a solution to manipulate the current dbase file or wait a year or more to get state approved.

 

[FZelle]

OK, thats a good reason.

Why is your SQL-Query so komplex?
Can you split it up so that it gets smaller?