Refresh Record Set

[Jack]

Hi,
I have a Xbrowse with a record set .

Is it possible to refresh the record set only for the current line of the xbrowse .
I"d like to make a requery only for the current record .

Thanks .

[Rick Lipkin]

Jack

If you are using the same oRs recordset to edit the record, at the end of your edit routine make sure you issue oRs:Update() .. and to refresh xBrowse, oLbx:ReFresh(). If you create a new oRs recordset to edit the record .. YES, you will need to ReSync ( not requery ) the original xBrowse recordset record with oRs:Resync( 1, 2 ), then oLbx:ReFresh(). Issuing the ReSync() method only updates the current selected record with new values and does not force the entire recordset to be refreshed.

Rick Lipkin

[AHF]

If you create a new recordset to edit the record better is to clone ex oRsNew := oRs:Clone and then you need to do nothing ADO will keep both synchronized (with same data) till you issue again :requery on the original.
Please remember that you will need to close both sets.

[Jack]

I think i will do this :

oLbx:bLDblClick = { | nRow, nCol | (EDFUNC(oRs:Fields("ID"):Value),oRs:resync(1,2),oLbx:refresh())}



function EDFUNC(pid)
local cSql
...edit fields
cSQL := "UPDATE PATREC SET NAME="+"'"+alltrim(wname)+"'" +" WHERE ID="+"'"+alltrim(pid)+"'"
TRY
oCon:Execute(cSql)
CATCH oErr
ShowAdoEr( oCon,csql )
END TRY
*
return .T.

[Enrico Maria Giordano]

oRs:resync(1,2)

It's equivalent to

Code: Select all

oRs:resync(1)

as the second parameter already defaults to 2. Anyway, Resync method never worked for me...

EMG

[AHF]

function EDFUNC(pid)
local cSql
...edit fields
cSQL := "UPDATE PATREC SET NAME="+"'"+alltrim(wname)+"'" +" WHERE ID="+"'"+alltrim(pid)+"'"
TRY
oCon:Execute(cSql)
CATCH oErr
ShowAdoEr( oCon,csql )
END TRY
*
return .T.

If ID its only one row and recordset is positioned in that row Resync will work.
If there are multiple rows with same ID resync still works on the row the set is positioned but not on all other rows. In this situation you must use requery.

Anyway, Resync method never worked for me...

Enrico in what context?

[Enrico Maria Giordano]

Enrico in what context?

Don't remember the details, sorry. I only recall that it never worked.

EMG

[Jack]

I just test it and it works .

ID is a unique value .

Thanks for this help .

[Enrico Maria Giordano]

I just test it and it works .

Great! I will retry next time.

EMG

[Rick Lipkin]

To All

I generally don't like ReSyc() and try to find another way around using it.. like Enrico, I have found Resync() to be somewhat un-reliable ( in my case )
using MS Access, but seems to work using MS Sql Server.

Don't know if it has to do with the Access database being local ( .mdb is usually in the default .exe folder ) and the timing, meaning that Access has
to compete with the OS for resources .. sometimes a SysWait() will allow Access to catch up in those situations. MS Sql server on the other hand
has a bit more horsepower whether it be remote or the free Sql Express.

Alternate work-around I would use .. goes something like this:

Code: Select all

//-------
Func _EditUm( oRs,oLbx,cSql )

Local nEId,oRs2

nEid := oRs:Fields("PrimaryKey"):Value

// do your edits with another oRs2

oRs2:CLose()
oRs:CLose() //  yes close the orig recordset assoc with oLbx

// do not redefine oRs .. just reuse
// cSql is the original query statement

TRY
   oRs:Open( cSQL,oCONNECT ) // oConnect is a defined public connection object
CATCH oErr
   MsgInfo( "Error in Opening table" )  // bad news if this happens
   RETURN(.F.)
END TRY

oRs:MoveFirst()
oRs:Find( "[PrimaryKey] = "+ltrim(str(nEid)) )

oLbx:ReFresh()

Return(.t.)
 

What this code does is re-queries the database by re-opening the same rows with the same oRs object associated with oLbx .. then goes back and finds
your original record using the primary key and this forces the database to use the new edited values and places the cursor back in the same position
then refresh your xBrowse.

A bit clumsy, but it works in all Sql Databses without using ReQuery() or ReSync() .. Antonio, I am sure you can translate this to use AdoRdd.

Rick Lipkin

[AHF]

I think Resync issues some kind of SQL query based on primary key to get back the values from source.

When you dont have the primary key included in your set you will get into problems with resync otherwise in all my tests with several dbs (MySql, PostGre, SQlite, Firebird, Access ) works ok.

In all my tests with adordd didnt get any problems because primary key (recno) its always included in the set.

resync doesnt works also with addnew when the db engine works with Sequences or Generators again because after adding the new row the set doesnt get immediately the primary key value and thus you need to either save the next Sequence or Generated key yourself with addnew or simply requery it after.

In all other situations resync seems to work ok.
I dont have experience in batchupdates.