Hi Neil

I have no idea if ADO.NET optimizes or not. If it doesn't there is little you can do. If it does, what I've written might work. In either case, I think the way I did it, there would be less computation than performing a substr(upper()) and then using "like %ufrag%".

BTW Your examples suggest you only need ufrag%, not %ufrag%.

Give it a try! ;)

>Hi MIke,
>
>Remeber that I am connected to an *OLD* style FPW26 data table through ADO.net. Does ADO.net even recognize the index for a free table?
>
>Neil
>
>>Hi
>>
>>Ensure there is an index on the patients.dbf
>>
>>INDEX ON UPPER(TITLE) TAG whatever
>>
>>Then change your code to this...
>>
>>

>>  Ufrag = UPPER(tcLastNameFragment) ;
>>  SUBY = " UPPER(title) = '" ;
>>  lcSelectStatement = "SELECT * From c:\patients.dbf WHERE" + SUBY + Ufrag + "'" ;
>>

>>
>>>Hi,
>>>
>>>I have a .net applicaition whose data store are 50 old fpw26 free tables. UPDATES, INSERTS and DELETES are working just fine. One of my SELECT statements is a little slow and I am sure that it is because the VFP engine connected to ado.net does not like the lack of optimization. I build the pieces to my select statment on the fly just before I send it. tcLastNameFragment is passed to my GetListFromNameFragment method as a parameter.
>>>
>>>

>>>DataSet GetListFromNameFragment(string tcLastNameFragment)
>>>{
>>>  Ufrag = '%'+UPPER(tcLastNameFragment)+'%' ;
>>>  cnLEN = TRANSFORM(LEN(tcLastNameFragment));
>>>  SUBY = " SUBSTR(UPPER(title),1," + cnLEN + ") like '" ;
>>>
>>>  lcSelectStatement = "SELECT * From c:\patients.dbf WHERE" + SUBY + Ufrag + "'" ;
>>>
>>>   more code ........
>>>

>>>
>>>If I pass "GOR" to the method, the select statement will return 3 the following 3 names as it should:
>>>GORE
>>>GORIN
>>>GORY
>>>
>>>If I pass in "T" to the method, every name that starts with "T" is found.
>>>Select statement works but lacks opitimization.
>>>
>>>Any thoughts?
>>>Neil