We have seen this with numeric data. For instance, if the data type is n(7,4), VFP allows you to programatically enter a value that is not really 7,4 but actually 7,3. An n(7,4) field should be 7 total digits, including the decimal point, so the max should be 99.9999. VFP has always allowed you to programatically provide an equal number of digits, with fewer decimal places, like this:

replace nfield with 999.999

VFP itself allows updating and accessing data from a field containing this value, but the VFP OLEDB provider does not allow either. The typing is stricter to ensure proper interoperability with ADO, ADO.Net, SQL Linked Servers, DTS, etc.

If you have a field like this, you will get the EFAIL error. The workaround is to increase the field size to properly accomodate the data it actually contains.

For example, a value of 123.456 would cause an error in the provider if the field datatype is n(7,4) but will work OK with a type of n(8,4).

I hope I have made this clear enough!


>Hi All,
>
>Is there a limit on the size a cursor can be when retrieving records using the VFP OLEDB provider?
>
>Long story short, a client is converting all their VFP data to SQL Server and using VB components via the VFP 8 SP 1 OLEDB Provider to do it (heavy politics, don't ask). A problem arose where, for a table with 79 fields and only 111748 records, the provider is returning the following error after trying to move beyond record 12337:
>
>OLE IDispatch exception code 0 from Microsoft Cursor Engine: Data provider or other service returned an E_FAIL status
>
>
>I was able to duplicate their problem using the VFP provider with VFP code...
>
>First attempts were using a client side recordset, both connected and disconnected, and converting the recordset to a cursor via the CursorAdapter. Even with a connected client recordset, the cursor created only has 12337 records. When trying to loop through the recordset directly, MoveNext throws the above-mentioned error when trying to pass record 12337, even though EOF is false (which is what they saw on the VB side). Is there something that needs to be done to get the additional rows into the client-side recordset?
>
>Next I tried using a server side recordset. In this case a loop through the recordset directly hits all 111748 records, however, when converting to a cursor with the CursorAdapter, the resulting cursor only has 12438 records. So server side fixed their problem of getting to all the records with VB, but now I'm curious as to the size limit the VFP side when it comes to converting recordsets to cursors...
>
>
>
>Thanks,
>
>- Brian
>
>
>Some Code:
>

>lcSide = "SERVER"
>*lcSide = "CLIENT"
>
>*-- Create Recordset object with some default settings
>loRS = CREATEOBJECT("ADODB.Recordset")
>loRS.CursorType       = 3  && adOpenStatic
>loRS.CursorLocation   = 3  && adUseClient
>loRS.LockType = 4  && adLockBatchOptimistic
>IF lcSide = "SERVER"
>	loRS.CursorLocation = 2  && adUseServer
>ENDIF
>
>
>*-- Create Connection object with some default settings
>loConn = CREATEOBJECT("ADODB.Connection")
>loConn.CursorLocation = 3  && adUseClient
>loConn.CommandTimeout = 0
>loConn.ConnectionTimeout = 0
>IF lcSide = "SERVER"
>	loConn.CursorLocation = 2  && adUseServer
>ENDIF
>
>*-- Set location of VFP tables as the DataSource
>lcVFPDataSourceFullPath  = "C:\Work\Dev\Test\dbfs\"
>
>*-- SQL Command to execute
>lcSQLCommand = "Select * from master"
>
>lcOLEConnString = "Provider=vfpoledb;" ;
>			+ "Data Source=" + lcVFPDataSourceFullPath + ";" ;
>			+ "Mode=ReadWrite|Share Deny None;" ;
>			+ "Collating Sequence=MACHINE;" ;
>			+ "Password=''"
>
>*-- Set the ConnectionString and Open connection
>loConn.ConnectionString = lcOLEConnString
>loConn.Open()
>
>*-- Set up the Recordset and Open
>loRS.Source = lcSQLCommand
>loRS.ActiveConnection = loConn
>loRS.Open()
>
>
>*-- Close any previous instance of our result cursor
>USE IN SELECT("csrResult")
>
>*-- Convert the Recordset to a cursor
>lCA = CREATEOBJECT("CursorAdapter")
>lCA.DataSourceType = "ADO"
>lCA.Alias = "csrResult"
>lCA.CursorFill(,,,loRS)
>
>BROWSE
>*-- Scrolling to the bottom of the cursor after fetching is complete
>*-- will show only 12337 records
>
>*-- Interesting Note:  With server-side, scrolling to the bottom shows 12438 rows
>*-- and the last row has a slew of non-printing characters in the text fields,
>*-- blanks in numeric and dates...
>
>*-- Loop through Recordset
>loRS.MoveFirst
>i = 1
>DO WHILE NOT loRS.EOF
>	WAIT WINDOW "Recordset row: " + TRANSFORM(i) NOWAIT NOCLEAR
>	i = i + 1
>	loRS.MoveNext  && Client-side will fail after record 12337, Server-side OK
>ENDDO
>
>? "Recordset Rows: " + TRANSFORM(i-1)
>
>