Refreshing xbrowse when dataset is closed and re-opened

[Carles]

James,

Can you tried to use OLE DB for ODBC Drivers ?



In the conection folder u need select dBase files and especify the path. In this example i've got the exe in c:\test and customer.dbf in c:\temp\data



Now, you can see the correct conection string if u need to use...



And finally i execute this sentence "Select * from customer where age > 80 order by first"

[James Bott]

Carles,

Yes, I did everything as you showed and I get the unknownName "open" error both with and without the WHERE clause.

James

[Carles]

James,

It's very strange, i don't know, i'm sorry .

[Armando]

Yes, curiously oRs:Refresh() does not give any error, but this is no where officially documented and I am not sure what it does or does not do. I can be sure of what is documented by Microsoft and many other books on the subject.

Friends, pls review this link

http://msdn.microsoft.com/en-us/library ... 16(v=VS.85).aspx

Regards

[nageswaragunupudi]

Yes, curiously oRs:Refresh() does not give any error, but this is no where officially documented and I am not sure what it does or does not do. I can be sure of what is documented by Microsoft and many other books on the subject.

Friends, pls review this link

http://msdn.microsoft.com/en-us/library ... 16(v=VS.85).aspx

Regards

Clicking on the above link does not work. Those who want to see this page may please copy this link "http://msdn.microsoft.com/en-us/library/ms677516(v=VS.85).aspx" and paste in the address bar.

What this page lists are ADO methods, but not only those methods applicable to Recordset object. We all know for a long time that there is one Refresh method in ADO, but that applies to Collection Objects.

Sure there is a mention of Refresh method in this page. Please click on the "Refresh method" to go to the page where Refresh method is explained. To what this method applies to and what it does.

Clicking on Refresh method takes us to this page:
"http://msdn.microsoft.com/en-us/library/ms675046(v=VS.85).aspx".
In this page it is clearly explained that Refresh method applies to collections.

At the end of this page under "Applies To" section, it is clearly mentioned to what all collection objects the Refresh method applies and we all know this information all along.

This is the link to properties, methods and events of Recordset object:
"http://msdn.microsoft.com/en-us/library/ms675841(v=VS.85).aspx"

You will see that Refresh method is NOT documented here.
So, even if it does not error out, please do not use oRs:Refresh() and keep expecting that it does something what we in our minds like it to do.


So we come back to the original point. There is no documented method Refresh() for RecordSet object.

[nageswaragunupudi]

We have seen in the earlier post that Refresh method for Recordset is not documented.

Though oRs:Refresh() does not error out, this does not read data again from the table and refresh the recordset. I humbly advise my friends not to depend on oRs:Refresh() for this purpose.

Use oRs:Requery() instead. This reads all the data based on oRs:Source again from the database and refreshes data in the recordset.

Here is a sample program to show that oRs:Refresh() does not really read the data again.

Code: Select all  Expand view  RUN

#include 'fivewin.ch'
#include 'xbrowse.ch'

#define BS Chr(92)    // BackSlash
#define FWPATH "c:" + BS + "fwh" + BS + "samples" + BS

static oCn

function Main()

   local cStr   := "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ;
                    FWPATH + "xbrtest.mdb;User Id=admin;Password=;"

   local cSql, oRs

   // Open Connection
   oCn   := TOleAuto():New( "ADODB.Connection" )
   oCn:Open( cStr )
   oCn:CursorLocation   := 3 // adUseClient

   // Open Recordset. Single record
   cSql  := 'SELECT SALARY FROM CUSTOMER WHERE FIRST = "Ali"'
   oRs   := TOleAuto():New( "ADODB.RecordSet" )
   oRs:Open( cSql, oCn )
   MsgInfo( oRs:Fields( 0 ):Value, 'SALARY' )  // 30800.00 in my case

   // Double the salary directly in the table ( Recordset does not know )
   cSql  := 'UPDATE CUSTOMER SET SALARY = 2 * SALARY WHERE FIRST = "Ali"'
   oCn:Execute( cSql )
   // Now the value in the table is 61600

   MsgInfo( oRs:Fields( 0 ):Value, 'AFTER UPDATE' ) // 30800, because recordset does not know the change

   oRs:Refresh()
   MsgInfo( oRs:Fields( 0 ):Value, 'AFTER REFRESH' ) // Still shows 30800.00 Refresh() did not read again
   oRs:ReQuery()
   MsgInfo( oRs:Fields( 0 ):Value, 'AFTER REQUERY' ) // Now shows 61800. Correct

   // Restore original value before exit
   cSql  := 'UPDATE CUSTOMER SET SALARY = SALARY / 2 WHERE FIRST = "Ali"'
   oCn:Execute( cSql )

   oRs:Close()
   oCn:Close()

return nil

There is also another method ReSync(). This method is much faster and has many options. But please use this with extreme caution. Not all providers support this method and even where supported it does not work as expected with all parameters on all kinds of recordsets in the same way.
Please thorougly test this method on the target databae before using. Not advised for cross RDMS programming.

[James Bott]

Rao,

In the original example showing oRS:Refresh(), even oRS:Requery() wasn't needed. It was right after the recordset was created.

James

[nageswaragunupudi]

This is better

Code: Select all  Expand view  RUN

WITH OBJECT oBrw
      :lScreenUpdating := .f.
      :oRs:Close()
      :oRs:Source := <revised SQL statement>
      :oRs:Open()
      :GoTop()
      :lScreenUpdating := .t.
      :Refresh()
   END