problem with refresh of data in listbox

[Nifrabar]

Hi!

Next problem with VFP9 running at XP-machine.

In a method I put code to change a view which is used to display some fields in a listbox.
Hereunder is the code.
Saveform() comes from "1001-things". That works fine.
ViewName(lcLookUpTable) comes as form-parameter. Paramter is put in formproperty. View is updatable and is openened shared in buffered mode in workarea 0.
lcLookupTable = thisform.tLookupTable
Problem:
lcLookuptable is not right requeried and so the listbox will not get properly refreshed.
ln returns 1

Any idea what's wrong?

KR
-Bart

with thisform
......
......
        llOK =.saveform()

	SELECT (lcLookUpTable)

	IF llOK
		ln=REQUERY()
		.lstLookUp.Requery()
	ELSE
		.revertform()	
	ENDIF
	
*	.release()
	
ENDWITH

 

[StewartUK]

Bart,

You comment "lcLookuptable is not right requeried" makes it sound like the problem is in the statement that creates the view rather than the listbox.

Do you get an actual error occurring? What happens if, after the REQUERY() you try and browse the view?

It might help if you give us the SQL statement you are using to create the view.

Stewart

 

[Nifrabar]

Hi Stewart,
It seems to deal with failing requery of view.
If I put a browse directly after the requery command I found that the view reflects the changes made by the form but does not retrieve an updated dataset from the basetable.
I just read some about FetchAsNeeded which should be'.T.?
In fact I'm unfamilair with that property.
Any suggestions?
-Bart
Here is the view code, copied from the viewmanager.

SELECT Tbank.bank_id, Tbank.banknaam, Tbank.system, Tbank.nstatus,;
  Tbank.linactive, Tbank.cuser, Tbank.tupdate,;
  IIF(Tbank.linactive,"[ Inactief ]","") AS cinactive;
 FROM ;
     db_kerkhof!tbank;
 WHERE  Tbank.nstatus = ( 1 );
 ORDER BY Tbank.banknaam

DBSetProp(ThisView,"View","SendUpdates",.T.)
DBSetProp(ThisView,"View","BatchUpdateCount",1)
DBSetProp(ThisView,"View","CompareMemo",.T.)
DBSetProp(ThisView,"View","FetchAsNeeded",.F.)
DBSetProp(ThisView,"View","FetchMemo",.T.)
DBSetProp(ThisView,"View","FetchSize",100)
DBSetProp(ThisView,"View","MaxRecords",-1)
DBSetProp(ThisView,"View","Prepared",.F.)
DBSetProp(ThisView,"View","UpdateType",1)
DBSetProp(ThisView,"View","UseMemoSize",255)
DBSetProp(ThisView,"View","Tables","db_kerkhof!tbank")
DBSetProp(ThisView,"View","WhereType",3)

DBSetProp(ThisView+".bank_id","Field","DataType","I")
DBSetProp(ThisView+".bank_id","Field","UpdateName","db_kerkhof!tbank.bank_id")
DBSetProp(ThisView+".bank_id","Field","KeyField",.T.)
DBSetProp(ThisView+".bank_id","Field","Updatable",.F.)

DBSetProp(ThisView+".banknaam","Field","DataType","C(25)")
DBSetProp(ThisView+".banknaam","Field","UpdateName","db_kerkhof!tbank.banknaam")
DBSetProp(ThisView+".banknaam","Field","Format","XXXXXXXXXX")
DBSetProp(ThisView+".banknaam","Field","KeyField",.F.)
DBSetProp(ThisView+".banknaam","Field","Updatable",.T.)

DBSetProp(ThisView+".system","Field","Comment","iSystem is .T. >> field required to be available in the application; delete not possible")
DBSetProp(ThisView+".system","Field","DataType","L")
DBSetProp(ThisView+".system","Field","UpdateName","db_kerkhof!tbank.system")
DBSetProp(ThisView+".system","Field","KeyField",.F.)
DBSetProp(ThisView+".system","Field","Updatable",.T.)

DBSetProp(ThisView+".nstatus","Field","DataType","I")
DBSetProp(ThisView+".nstatus","Field","UpdateName","db_kerkhof!tbank.nstatus")
DBSetProp(ThisView+".nstatus","Field","KeyField",.F.)
DBSetProp(ThisView+".nstatus","Field","Updatable",.T.)

DBSetProp(ThisView+".linactive","Field","DataType","L")
DBSetProp(ThisView+".linactive","Field","UpdateName","db_kerkhof!tbank.linactive")
DBSetProp(ThisView+".linactive","Field","KeyField",.F.)
DBSetProp(ThisView+".linactive","Field","Updatable",.T.)

DBSetProp(ThisView+".cuser","Field","DataType","C(25)")
DBSetProp(ThisView+".cuser","Field","UpdateName","db_kerkhof!tbank.cuser")
DBSetProp(ThisView+".cuser","Field","KeyField",.F.)
DBSetProp(ThisView+".cuser","Field","Updatable",.T.)

DBSetProp(ThisView+".tupdate","Field","DataType","T")
DBSetProp(ThisView+".tupdate","Field","UpdateName","db_kerkhof!tbank.tupdate")
DBSetProp(ThisView+".tupdate","Field","KeyField",.F.)
DBSetProp(ThisView+".tupdate","Field","Updatable",.T.)

DBSetProp(ThisView+".cinactive","Field","DataType","C(12)")
DBSetProp(ThisView+".cinactive","Field","KeyField",.F.)
DBSetProp(ThisView+".cinactive","Field","Updatable",.F.)

As this must be a generic to use form all tablenames, fieldnames etc. comes by using parameters. Later I like to convert this form into a class. For that I also do open tables programmaticly like:

LOCAL llOK
llOK = .F.
WITH thisform
	IF EMPTY(DBC())
		OPEN DATABASE (.dbcName) SHARED
		SET DATABASE TO (.dbcName)
	ENDIF
		
	
	USE (.dbcName)+'!'+(.cLookUpTable) IN 0 SHARED
	llOK = CURSORSETPROP("Buffering",5,.cLookUpTable)

	IF llOK AND LEN(ALLTRIM(.cMMTable)) <> 0
		USE (.cMMTable) IN 0 SHARED
		llOK = llOK and CURSORSETPROP("Buffering",5,.cMMTable)
	ENDIF 	

	RETURN llOK

ENDWITH

 

[TamarGranor]

If your listbox is based on a view, then to update based on a new parameter, you need to both requery the view and requery the listbox. One way is with code like:

REQUERY("the view")
ThisForm.TheList.Requery()

In general, though, I avoid this kind of problem by putting whatever needs to happen to update a listbox in the listbox's Requery method, so that requerying the list also updates its RowSource. In your case, therefore, I'd put code like:

REQUERY("the view")

in the listbox's Requery method.

Tamar

 

[Nifrabar]

Tamar,
I tried your suggestion.
The problem seems to be that the view on which the listbox relies does not get updated as it should be.
I found that after the requerey (which returned 1) in this particular situation whereas nstatus = 2 still remained in the 'updated' view.
So why was th eview not updated as it should be?
Does this has to deal with 'FetchAsNeeded'?
I don't know how to deal with that.
No back-end server just basic VFP database with VFP tables as source.
KR
-Bart

 

[TamarGranor]

It's hard to say why you're not getting the expected results from calling Requery on the view. The first thing I'd try is testing that from the Command Window. USE the view, examine the results, then change the view parameter and issue REQUERY(), and check whether you get the desired results.

If that works, start looking at what's different in the form. One issue may be whether the variable you're using for the parameter is in scope when you issue Requery.

Tamar

 

[Nifrabar]

Hello Tamar,
The view is OK. If I change the view, more precisely the field nStatus, from 1 into 2 than after requery that record should not be retrieved. But instead, after a requery in the form the record is still availbale in the view. The strange thing is that the requery returns value 1 which should mean that requery was OK.
If I close the form and reopen it than the view does not show the record with value nStatus = 2.

I found a workaround which I don't understand why it works but is does: Just before the requery this.hide and than this.show. That does the trick......
Any idea why?

KR
-Bart

 

[Olaf Doschke]

Hi Bart,

WHERE Tbank.nstatus = ( 1 );

Where are you using a parameter, you hard coded 1 in the sql. Perhaps a problem of the view designer to not show the correct view sql? What code does gendbc.prg create for the view if you run it on the database containing the view?

Bye, Olaf.

 

[Nifrabar]

Hi Olaf,

This is result of GenDbc:

-Bart

CREATE SQL VIEW "VU_BANK" ; 
   AS SELECT Tbank.bank_id, Tbank.banknaam, Tbank.system, Tbank.nstatus, Tbank.linactive, Tbank.cuser, Tbank.tupdate, IIF(Tbank.linactive,"[ Inactief ]",SPACE(12)) AS cinactive FROM  db_kerkhof!tbank WHERE  Tbank.nstatus = ( 1 ) ORDER BY Tbank.banknaam

DBSetProp('VU_BANK', 'View', 'UpdateType', 1)
DBSetProp('VU_BANK', 'View', 'WhereType', 3)
DBSetProp('VU_BANK', 'View', 'FetchMemo', .T.)
DBSetProp('VU_BANK', 'View', 'SendUpdates', .T.)
DBSetProp('VU_BANK', 'View', 'UseMemoSize', 255)
DBSetProp('VU_BANK', 'View', 'FetchSize', 100)
DBSetProp('VU_BANK', 'View', 'MaxRecords', -1)
DBSetProp('VU_BANK', 'View', 'Tables', 'db_kerkhof!tbank')
DBSetProp('VU_BANK', 'View', 'Prepared', .F.)
DBSetProp('VU_BANK', 'View', 'CompareMemo', .T.)
DBSetProp('VU_BANK', 'View', 'FetchAsNeeded', .F.)
DBSetProp('VU_BANK', 'View', 'Comment', "")
DBSetProp('VU_BANK', 'View', 'BatchUpdateCount', 1)
DBSetProp('VU_BANK', 'View', 'ShareConnection', .F.)
DBSetProp('VU_BANK', 'View', 'AllowSimultaneousFetch', .F.)

*!* Field Level Properties for VU_BANK
* Props for the VU_BANK.bank_id field.
DBSetProp('VU_BANK.bank_id', 'Field', 'KeyField', .T.)
DBSetProp('VU_BANK.bank_id', 'Field', 'Updatable', .T.)
DBSetProp('VU_BANK.bank_id', 'Field', 'UpdateName', 'db_kerkhof!tbank.bank_id')
DBSetProp('VU_BANK.bank_id', 'Field', 'DataType', "I")
* Props for the VU_BANK.banknaam field.
DBSetProp('VU_BANK.banknaam', 'Field', 'KeyField', .F.)
DBSetProp('VU_BANK.banknaam', 'Field', 'Updatable', .T.)
DBSetProp('VU_BANK.banknaam', 'Field', 'UpdateName', 'db_kerkhof!tbank.banknaam')
DBSetProp('VU_BANK.banknaam', 'Field', 'Format', "XXXXXXXXXX")
DBSetProp('VU_BANK.banknaam', 'Field', 'DataType', "C(25)")
* Props for the VU_BANK.system field.
DBSetProp('VU_BANK.system', 'Field', 'KeyField', .F.)
DBSetProp('VU_BANK.system', 'Field', 'Updatable', .T.)
DBSetProp('VU_BANK.system', 'Field', 'UpdateName', 'db_kerkhof!tbank.system')
DBSetProp('VU_BANK.system', 'Field', 'Comment', "iSystem is .T. >> field required to be available in the application; delete not possible")
DBSetProp('VU_BANK.system', 'Field', 'DataType', "L")
* Props for the VU_BANK.nstatus field.
DBSetProp('VU_BANK.nstatus', 'Field', 'KeyField', .F.)
DBSetProp('VU_BANK.nstatus', 'Field', 'Updatable', .T.)
DBSetProp('VU_BANK.nstatus', 'Field', 'UpdateName', 'db_kerkhof!tbank.nstatus')
DBSetProp('VU_BANK.nstatus', 'Field', 'DataType', "I")
* Props for the VU_BANK.linactive field.
DBSetProp('VU_BANK.linactive', 'Field', 'KeyField', .F.)
DBSetProp('VU_BANK.linactive', 'Field', 'Updatable', .T.)
DBSetProp('VU_BANK.linactive', 'Field', 'UpdateName', 'db_kerkhof!tbank.linactive')
DBSetProp('VU_BANK.linactive', 'Field', 'DataType', "L")
* Props for the VU_BANK.cuser field.
DBSetProp('VU_BANK.cuser', 'Field', 'KeyField', .F.)
DBSetProp('VU_BANK.cuser', 'Field', 'Updatable', .T.)
DBSetProp('VU_BANK.cuser', 'Field', 'UpdateName', 'db_kerkhof!tbank.cuser')
DBSetProp('VU_BANK.cuser', 'Field', 'DataType', "C(25)")
* Props for the VU_BANK.tupdate field.
DBSetProp('VU_BANK.tupdate', 'Field', 'KeyField', .F.)
DBSetProp('VU_BANK.tupdate', 'Field', 'Updatable', .T.)
DBSetProp('VU_BANK.tupdate', 'Field', 'UpdateName', 'db_kerkhof!tbank.tupdate')
DBSetProp('VU_BANK.tupdate', 'Field', 'DataType', "T")
* Props for the VU_BANK.cinactive field.
DBSetProp('VU_BANK.cinactive', 'Field', 'KeyField', .F.)
DBSetProp('VU_BANK.cinactive', 'Field', 'Updatable', .F.)
DBSetProp('VU_BANK.cinactive', 'Field', 'UpdateName', '')
DBSetProp('VU_BANK.cinactive', 'Field', 'DataType', "C(12)")
ENDFUNC

 

[Olaf Doschke]

Thanks Bart,

I see you really want to query nstatus=1 as a fixed filter condition. It's an updatable field of your view, but do you update? Do you Tableupdate() before you requery the view?

saveform should perhaps do that, but does it have any update conflicts? Have you debugged what happens in saveform()?

You may try SET SQLBUFFERING OFF, before you requery the view.

Bye, Olaf.

 

[Nifrabar]

Olaf,
SET SQLBUFFERING OFF did had no effect.

I opened the view which populates the listbox in the form with optimistic rowbuffering (5).
When I change that to pessimistic (3) than it's all OK.

    USE (.dbcName)+'!'+(.cLookUpTable) IN 0 SHARED
    llOK = CURSORSETPROP("Buffering",3,.cLookUpTable)

Weird stuff to me.....
Any idea how this might happen?

Can I leave buffering without any problems at '3'?

-Bart

 

[Olaf Doschke]

3 is optimistic row buffering, 5 is optimistic table buffering. With views you can only choose an optimistic buffering mode and can only switch from record buffering to table buffering.

I can't tell you if it's wrong or right for your case to use which buffering. With row buffering, changing the active record causes the last record to be written back automatically, just a SKIP in the view will trigger that.

With table buffering, you'd need an tableupdate() command to write back changes to the table. As I don't have 1001 Things at hand I don't know what saveform() does. Perhaps it's really intended to work with a table or view in optimistic row buffering mode, then using buffermode 3 is of course okay.

Perhaps take a look at this excellent explaination of buffering and locking:

http://weblogs.foxite.com/andykramek/archive/2005/10/18/948.aspx

Bye, Olaf.

 

[Nifrabar]

Olaf,

Tableupdate takes place in the savesorm method.
Thanks for your link to Andy's article which also is very clear.

One thing I now wonder is that while I had to change my buffermode from 3 to 5 to make things work:
What will be impact to prevent from update confilcts?
I modified the original saveform method to catch update conficts.

Will the saveform method with all it's 'extras' still work as when buffermode 5 was active..

-Bart

 

[Olaf Doschke]

depends. If it was written for buffermode 3 it was written to only update the current record and check conflict. What's the tableupdate() command? Does it update ALL or just the current record?

Bye, Olaf.