I have a client server app, in which I use SQLEXEC to run a SQL select statement against a MySQL table, and the results are saved into a cursor by VFP (as you may already know). I then create a composite index against this cursor. I never had an issue with it under VFP 7, and it ran fine for my users for months under VFP 9. But, recently for a certain selection of records, it produces the error: "Error building key for index "c:\docume~1\login_name\locals~1\temp\00004i6h000t.cdx" tag "Opt_model". I was able to reproduce this error with the same data set in my test environment. If I or the users select a different set of records it works fine. If I recompile the program, under VFP 7, it does not give me any errors for the same record set.

If anyone has run into this and/or knows how to solve this, or has a couple of good guesses, I would appreciate it.


Here is some background information. The fields are:

Name Type
option Numeric
modelno Character
pd_var Character
date_sent Date

The index str is :
INDEX ON STR(option,4)+modelno+pd_var+IIF(ISNULL(date_sent),'T','F'+DTOS(date_sent)) TAG OPT_MODEL DESC

I have noticed, if I trap for this error ignore it and use the statement again it works, strangely enough, but there has to be a better solution.

I need to sort it in this way for a report, in which this data is apart.