Updateable view problem..help please 1

[jrajesh]

Created a view in View designer. Generated a prg using GenDbc to get the view code and modified the prg. Deleted the view from the DBC and recreated it thru' this prg.
Created a form with the view in the DE, a grid using this view and buttons to add & delete records.

In Form Init:
Set multilock on
=CURSORSETPROP("Buffering",5)

I can traverse the grid without an error. Only, when I make a change and trigger a requery on the view, i get this error:

Table buffer for alias "vnatgn" contains uncommitted changes.

Could someone please advise me on where I'm going wrong?
TIA and Regards,
Rajesh

The generated + modified view prg:

* VNation.Prg
************************
*!* CREWQUOT.DBC is open
*!* SET EXCLUSIVE ON
CREATE SQL VIEW "VNATGN" AS SELECT Nation.ctrycode,Natgnpay.rankcat,Natgnpay.paycode, Natgnpay.value, Natgnpay.ctrycode ;
FROM crewquot!nation INNER JOIN crewquot!natgnpay ;
ON Nation.ctrycode = Natgnpay.ctrycode ;
ORDER BY Natgnpay.ctrycode,Natgnpay.rankcat,Natgnpay.paycode

DBSETPROP('VNATGN', 'View', 'Tables', 'Natgnpay')

DBSetProp('VNATGN.rankcat', 'Field', 'UpdateName', 'crewquot!natgnpay.rankcat')
DBSetProp('VNATGN.paycode', 'Field', 'UpdateName', 'crewquot!natgnpay.paycode')
DBSetProp('VNATGN.value', 'Field', 'UpdateName', 'crewquot!natgnpay.value')
DBSetProp('VNATGN.ctrycode_b', 'Field', 'UpdateName', 'crewquot!natgnpay.ctrycode')

DBSetProp('VNATGN.ctrycode_a', 'Field', 'KeyField', .T.)
DBSetProp('VNATGN.ctrycode_a', 'Field', 'Updatable', .F.)
DBSetProp('VNATGN.ctrycode_a', 'Field', 'DataType', "C(10)&quot

DBSetProp('VNATGN.rankcat', 'Field', 'KeyField', .F.)
DBSetProp('VNATGN.rankcat', 'Field', 'Updatable', .T.)
DBSetProp('VNATGN.rankcat', 'Field', 'DataType', "C(10)&quot

DBSetProp('VNATGN.paycode', 'Field', 'KeyField', .F.)
DBSetProp('VNATGN.paycode', 'Field', 'Updatable', .T.)
DBSetProp('VNATGN.paycode', 'Field', 'DataType', "C(10)&quot

DBSetProp('VNATGN.value', 'Field', 'KeyField', .F.)
DBSetProp('VNATGN.value', 'Field', 'Updatable', .T.)
DBSetProp('VNATGN.value', 'Field', 'DataType', "F(10,2)&quot

DBSetProp('VNATGN.ctrycode_b', 'Field', 'KeyField', .T.)
DBSetProp('VNATGN.ctrycode_b', 'Field', 'Updatable', .T.)
DBSetProp('VNATGN.ctrycode_b', 'Field', 'DataType', "C(10)&quot

DBSetProp('VNATGN', 'View', 'UpdateType', 1)
DBSetProp('VNATGN', 'View', 'SendUpdates', .T.)

 

[weedz]

Have you issued a tableupdate() or a tablerevert() when you wish to apply the changes ?

When you do not cancel or commit the changes, you'll get the message.

HTH,
Weedz (Wietze Veld)
My private project:

http://www.crowncap.demon.nl\info\crwnbase

Download the CrownBase source code !!

 

[foxdev]

when I make a change and trigger a requery on the view

Normally, with table buffering, moving from row to row, even with edits, will not cause the message. But since you are issuing a Requery(), then as Weedz says you'll need to issue a TableRevert() or TableUpdate(). -----------------
Robert Bradley
use coupon code "TEKTIPS" for 15% off at:

http://www.lowcostprints.com

http://www.foxdev.com

 

[ramani]

YOU CAN ISSUE IN THE ROWCOLCHANGE EVENT..
=TABLEUPDATE(.T.)

IF YOU WANT USER CONFIRMATION, YOU CAN PUT THAT WITHIN A LOOP..USING MESSAGEBOX(......) FUNCTION.

IN PLACE WHERE YOU VALIDATE THE DATA, IF DATA IS NOT VALID, YOU CAN DO A TABLEREVERT.

HOPE THIS HELPS ramani :-9
(Subramanian.G)
FoxAcc
ramani_g@yahoo.com
LET KNOW IF THIS HELPED. ENOUGH EXPERTS ARE HERE TO HELP YOU OUT! BEST OF LUCK

 

[jrajesh]

Weedz, foxdev and Ramani:
Thank for your help
This is the code i have in the BeforeRowColChange event:

Select Natgnpay && The table
set multilocks on
=CURSORSETPROP("Buffering",3)
SET STEP ON
tableupdate(.t.,.t.,"vnatgn&quot && Update the view
tableupdate(.t.,.t.,"NATGNPAY&quot && Update the table as well
=requery("vnatgn&quot
ThisForm.grdVnatgn.refresh()
thisform.refresh()

I still am getting the error message.
Please help.
Thanks again and regards,
Rajesh

 

[jrajesh]

In my earlier message, I left out one aspect:

I tried the same code in AfterRowColChange event as well and got the same error message. I also tried creating a button and included this code in its click event. Still the same :-(

Please help.
Thanks all again.
Rajesh

 

[ramani]

Hi Rajesh

I think you have only set the buffering for the table and not for the view. The code can be like below..
=CURSORSETPROP("Buffering",3,"vnatgn&quot
=CURSORSETPROP("Buffering",3,"natgnpay&quot
This will avoid the confusion on which table is buffered.

Also.. in the intial place where the VIEW is opened, issue the command..
=CURSORSETPROP("Buffering",3,"vnatgn&quot

Then in the AfterRowCol Change event of the Grid which has the VIEW as the record source, put the code...
tableupdate(.t.,.t.,"vnatgn&quot

There is no need to do a tableupdate on the underlying table. If the view is updatable, the records will be updated on the underlying table.

Requery() will not create the error, if this is done.

Hope this helps ramani :-9
(Subramanian.G)
FoxAcc
ramani_g@yahoo.com
LET KNOW IF THIS HELPED. ENOUGH EXPERTS ARE HERE TO HELP YOU OUT! BEST OF LUCK

 

[jrajesh]

Ramani: Thank you very much for all the help.
Still I'm unable to get it working.
I did try all that you'd suggested.
Could you perhaps help me with some sample code?
I know that I'm asking for too much, but, I am rather desperate.
Thank you very much,
Best Regards,
Rajesh

 

[jrajesh]

Ramani:
Thanks again. In your posting, you've mentioned:

Also.. in the intial place where the VIEW is opened, issue the command..
=CURSORSETPROP("Buffering",3,"vnatgn&quot

I open the view in the DE. So, where need i insert this code?
Also, is there any site which has an example on updateable views?

Regards,
Rajesh

 

[ramani]

Ji Rajesh,
If you open it in the DE then, select the view in DE(give focus to this view in the form designer DE) and then in the properties, select BUfferModeOverride property and enter the value 3.
You dont issue the buffer mode anywhere else again.
Hope this helps

ramani :-9
(Subramanian.G),FoxAcc, ramani_g@yahoo.com