Refresh Record Set

Post Reply
Jack
Posts: 249
Joined: Wed Jul 11, 2007 11:06 am

Refresh Record Set

Post by 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 .
User avatar
Rick Lipkin
Posts: 2397
Joined: Fri Oct 07, 2005 1:50 pm
Location: Columbia, South Carolina USA

Re: Refresh Record Set

Post by 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
Posts: 837
Joined: Fri Feb 10, 2006 12:14 pm

Re: Refresh Record Set

Post by 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.
Regards
Antonio H Ferreira
Jack
Posts: 249
Joined: Wed Jul 11, 2007 11:06 am

Re: Refresh Record Set

Post by 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.
User avatar
Enrico Maria Giordano
Posts: 7355
Joined: Thu Oct 06, 2005 8:17 pm
Location: Roma - Italia
Contact:

Re: Refresh Record Set

Post by Enrico Maria Giordano »

Jack wrote: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
Posts: 837
Joined: Fri Feb 10, 2006 12:14 pm

Re: Refresh Record Set

Post by 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?
Regards
Antonio H Ferreira
User avatar
Enrico Maria Giordano
Posts: 7355
Joined: Thu Oct 06, 2005 8:17 pm
Location: Roma - Italia
Contact:

Re: Refresh Record Set

Post by Enrico Maria Giordano »

AHF wrote:Enrico in what context?
Don't remember the details, sorry. I only recall that it never worked.

EMG
Jack
Posts: 249
Joined: Wed Jul 11, 2007 11:06 am

Re: Refresh Record Set

Post by Jack »

I just test it and it works .

ID is a unique value .

Thanks for this help .
User avatar
Rick Lipkin
Posts: 2397
Joined: Fri Oct 07, 2005 1:50 pm
Location: Columbia, South Carolina USA

Re: Refresh Record Set

Post by 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
Posts: 837
Joined: Fri Feb 10, 2006 12:14 pm

Re: Refresh Record Set

Post by 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.
Regards
Antonio H Ferreira
Post Reply