Antonio,
OrdKeyCount and RECORDCOUNT might be a problem!
The only way to solve this is to:
If we use always adUseClient because ADO knows all rows and there ist any problem but performance decreases a lot. Not advisable!
IF we use adUseServer then with a WHERE clause ADO returns wrong RECORDCOUNT and OrdKeyCount!
The only solution we for this is :
If there is a WHERE clause we have to query the table with SELECT COUNT(*) FROM ctable WHERE ....
Thus in each RECORDCOUNT and OrdKeyCount values are correct.
We replace RECORDCOUNT by the following code:
- Code: Select all Expand view
STATIC FUNCTION ADORECCOUNT(nWA,oRecordSet) //AHF
LOCAL aAWData := USRRDD_AREADATA( nWA )
LOCAL oCon := aAWData[WA_CONNECTION]
LOCAL nCount := 0, cSql:=""
IF oRecordSet:CursorLocation == adUseClient
nCount := oRecordSet:RecordCount
ELSE
IF LEN(aAWData[WA_INDEXES]) > 0 .AND. aAWData[WA_INDEXACTIVE] > 0
oRecordSet:close()
//Making it lightning faster
oRecordSet:CursorLocation := adUseServer
oRecordSet:CursorType := adOpenForwardOnly
oRsecordSetLockType := adLockReadOnly
//LAST PARAMTER INSERTS cSql COUNT(*) MUST BE ALL FIELDS BECAUSE IF THERE IS A NULL
//FIELD COUNTS RETURNS WRONG
cSql := IndexBuildExp(aAWData[WA_INDEXACTIVE],aAWData,.T.) //INCLUDE COUNT
msginfo("adoreccount 2 "+CSQL)
//LETS COUNT IT
oRecordSet:open(cSql,oCon)
nCount := oRecordSet:Fields( 0 ):Value
msginfo("adoreccount 3 NRREC "+CVALTOCHAR(NCOUNT))
oRecordSet:close()
//RETURNING TO DEFAULT DATA
oRecordSet:CursorType := adOpenDynamic
oRecordSet:CursorLocation := adUseServer //adUseClient never use ths very slow!
oRecordSet:LockType := adLockPessimistic
cSql := IndexBuildExp(aAWData[WA_INDEXACTIVE],aAWData,.F.) //.F. DONT INCLUDE COUNT
msginfo("adoreccount 4 "+CSQL)
oRecordSet:open(cSql,oCon)
ELSE
nCount := oRecordSet:RecordCount
ENDIF
ENDIF
RETURN nCount
Do you have any other alternative? This function its called a lot and I dont know what will be effect in the overall performance. In our small trials it seems ok but .....
I miss good old DBF or ADS:(