Hi,

I have created an ASP page that accesses a VFP database and queries it via SQL.

The SQL used to be run on the web server with an IDC file and all was well. The exact syntax of the 'WHERE' statment is part of an index and I used to get an immediate response.

Now that I use an ASP page, I still get the correct result but it no longer seems to use the index and takes several minutes to process (the 'main' table has approx 2.5 million records).

syntax is shown below and I have hard coded a parameter into the query for now.

Can anybody help ?

'Create a DataBase Connection and store it to a variable named cnn
set cnn =Server.CreateObject("ADODB.Connection")
cnn.CommandTimeout = 0

'Open a connection to the DataBase
cnn.Open "Driver={Microsoft Visual FoxPro Driver};UID=;PWD=;SourceDB=c:\inetpub\wwwroot\infonet\postcode\postcode.dbc;SourceType=DBC;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;Null=Yes;Deleted=Yes;"

'Create a Query
SQL_query =
"SELECT Postcode, Healthaut
FROM main left outer join pcg on main.pcg=pcg.pcg_code
WHERE (iif(at(' ',alltrim(postcode))=0,alltrim(upper(postcode)),alltrim(upper(substr(alltrim(postcode),1,at(' ',alltrim(postcode))-1)+alltrim(substr(postcode,5)))))) = 'PE100LD' "

'Execute Query and store it to a variable called rst
set rst = cnn.Execute (SQL_Query)