none
"Data provider or other service returned an E_FAIL status" accessing particular table using ADO

    Question

  • Please can you help?

     

    I am accessing some tables using ADODB and when accessing a certain table I am getting the error message "OLE IDispatch exception code 0 from Microsoft Cursor Engine: Data provider or other service returned an E_FAIL status..."

     

    This occurs when I use

    ? loRS.RecordCount

    loRS.Sort

     

    or after 56 loops of the following code

    DO WHILE !loRS.EOF

    ? loRS.Fields("cn_ref").Value

    loRS.MoveNext()  && This is the line that causes the error on the 56th loop

    ENDDO

     

    The code works pefectly fine on all other tables and on a completely different data set that I have tried. I would therefore think this is some sort of data corruption but the table looks absolutely fine and foxfix can find nothing wrong with the table either. I have tried reindexing and packing the table but the problem persists so I am asking for help here please.

     

    My code being used is:

     

    LOCAL loRS AS ADODB.Recordset

    CLEAR

    loRS = CREATEOBJECT("ADODB.Recordset")

    loConnection = CREATEOBJECT("ADODB.Connection")

    lcConnectionString = "provider=vfpoledb;data source=c:\documents and settings\all users\application data\pegasus\server vfp\data\compm\comp_m.dbc"

    loConnection.ConnectionString = lcConnectionString

    loConnection.Open

    loRS = CREATEOBJECT("ADODB.Recordset")

    loRS.ActiveConnection = loConnection

    loRS.CursorType = 3

    loRS.Source = "cname"

    loRS.CursorLocation = 3

    loRS.Open()

    ? loRS.RecordCount && This line causes the error

    loRS.Sort = "cn_desc" && This line causes the error

    lnRecord = 0

    DO WHILE !loRS.EOF

    lnRecord = lnRecord + 1

    ? TRANSFORM(lnRecord) +" " + loRS.Fields("cn_ref").Value

    loRS.MoveNext()  && This line causes the error

    ENDDO

     

    Wednesday, August 01, 2007 10:58 AM

Answers

All replies

  • Do you have errors without "red" lines?

    Wednesday, August 01, 2007 6:08 PM
  • I get the error if any one of the red lines is present. If I remove the THIRD red line then I obviously get an infinite loop. To be fair it doesn't error but it's not great programming

     

    I came across a thread on dbforums.com where another developer had the same problem. One poster suggested using

    .CursorLocation = adUseServer

     I have done this and surprisingly I don't get the error now. Hurray!  Previously I had

    .CursorLocation = adUseClient && same as .CursorLocation = 3

     

    I have no idea why this fixed my problem though. Any ideas?

    Thursday, August 02, 2007 2:13 PM
  • You will find some samples for client cursor and server cursor at:

     

    http://msdn2.microsoft.com/en-us/library/3haz2895(VS.80).aspx

    Thursday, August 02, 2007 2:24 PM
  • Thanks for that dni.

    I have tried using the sample from the article you reference above and as soon as I change it to my dataset I get the original error.

     

    After looking at the article you referenced I found another article that I haven't seen for sometime "ADO Jumpstart for VFP Developers" http://msdn2.microsoft.com/en-us/library/ms917355.aspx. Looking at this article it says that server cursors don't support sorting, filtering or updating (which seems true in my testing) so I need a client side cursor after all. I still don't know why the client cursor fails and a server side won't support all of the features I need!

    Could it be some sort of problem with the OLEDB provider???
    Thursday, August 02, 2007 3:49 PM