0

I have an application (C#) which accesses data from .dbf files and populates a SQL database. This works by reading the contents of a SQL file for the query and executes this against the .dbf and populates a datatable, the contents are then inserted into the SQL database.

I have this working for various queries, apart from one:

SELECT 
b.id as LegacyAppointsID,
b.perfdate as "Date",
starttime as "time",
b2.duration as duration,
b.staffid as LegacyEmployeeId,
b.clientid as LegacyClientId,
'' as Employee,''as Client,
b.serviceid as LegacyServiceId,
'' as "Service",
0 as IsTask,
b.note as "Notes"
from Book b
inner join Blocks b2 on b.id = b2.bookid

When this one runs i get the following error displayed when executing the query against the .dbf file:

System.Data.OleDb.OleDbException: File 'strzero.prg' does not exist.

Now, if I remove the join part of the query (and the referenced columns) then the query is executed and the datatable is populated correctly, without error. This is the only query that i have, which features a join to a second .dbf

The query is quite basic, so i am unsure as to what could be wrong with the join.

Is there a certain way that the join needs to be formatted?

Stuart1044
  • 444
  • 5
  • 16
  • are they old foxpro databases? – BugFinder Apr 27 '16 at 07:36
  • Yes, they date back to 2009, so are in the process of converting to SQL – Stuart1044 Apr 27 '16 at 07:38
  • Have you read http://stackoverflow.com/questions/32901152/file-phd-prg-does-not-exists? – BugFinder Apr 27 '16 at 07:59
  • Just changed my code to connect in the same way, but i get errors when trying to open the connection. "The driver does not support the version of ODBC behavior that the application requested". Not sure if this is the problem or not, as I can connect fine to the dbf, just appears to be the join throwing the original error. – Stuart1044 Apr 27 '16 at 08:40
  • what happens if you stop renaming blocks to b2, and leave it as blocks? Im wondering if the rename is offending it - my foxpro is old (I gave up as visual foxpro kicked in) – BugFinder Apr 27 '16 at 09:00
  • If i remove the aliases from both tables and leave them as the tablename, i still get the same error – Stuart1044 Apr 27 '16 at 09:09
  • 1
    Apparently its something to do with the indexes, they've used functions in the indexing :( only real option would be to remove the indexes or copy the dbf files to perform this query – BugFinder Apr 27 '16 at 10:00
  • Currently I can work around by exporting to Excel and then into SQL, just takes a bit longer – Stuart1044 Apr 27 '16 at 10:01
  • try copying both dbfs to a temp folder (without indexes), running the query against those, it maybe quicker. – BugFinder Apr 27 '16 at 10:04

0 Answers0