C# visual foxpro dbf indices

Question

I've an old system where I have to read data from. It's stores the data an old Visual FoxPro DBF Table.

In C# I'm using adodb.dll with Provider=VFPOLEDB.1 to get the data and it works well so far.

When I select following (its an index):

select * from tableXYZ where tab_f1+STR(tab_f2,10)+STR(tab_f3,3)+STR(tab_f4,3)+STR(tab_f5,3) = "AR1234567890"

I get in Visual FoxPro a fast result (using the index), "AR" is the tab_f1, "1234567890" is the STR(tab_f2,10). The tab_f3, tab_f4 and tab_f5 seems to be "ignored", even filled in the table i get all the rows, regardless of the content of f3 to f5.

The same select in c# with the ADODB Connection returns no result set. Is there a way to let the VFPOLEDB ignore the three fields behind too or do i need an index only for tab_f1+tab_f2?

Answer 1

VFP will treat = as "starts with" based on the SET ANSI setting. If you want to use that same functionality in ADODB I would use LIKE instead:

select * 
from tableXYZ 
where tab_f1+STR(tab_f2,10)+STR(tab_f3,3)+STR(tab_f4,3)+STR(tab_f5,3) LIKE "AR1234567890%"

However note that ADODB will not use stand-alone ("IDX") index files. If your index in in a CDX associated with that table then it should be used.