Cannot update logical field from view 1

[jimmygw]

I have a form with a grid that contains 6 columns. Each of the 6 column's ControlSource is from a view ('vParts_used')created with view designer. The parent table for this is 'Parts_used'. In the designer, the keyid is checked and all fields are checked to be updated and "Send SQL Updates" is checked.
In the Lostfocus event property, for each grid cell, I have the following code:

=TABLEUPDATE()
SELECT parts_used
=TABLEUPDATE()
=REQUERY('vparts_used')
thisform.Refresh

This all works fine (although I don't understand the need for 2 tableupdates() ) when I modify the fields in the grid the table 'Parts_used' is updated as it should be with no error ..... EXCEPT.
Column 4 has a checkbox in it. When the box is checked (or unchecked) I get "Table buffer for alias "VPARTS_USED" contains uncommitted changes" error.
To troubleshoot this I placed a textbox on the form and ControlSourced it to the same field as the checkbox. If I change the data in it (T/F) and requery() I get the same error. Looking at the Data Session I can see that vparts_used has the change but Parts_used does not. I have added other logical fields to the table and ControlSourced to them but still get the error.
Any idea why this field (logical) cannot be updated?
VFP9 Win 10

Jimmy

 

[Olaf Doschke]

I don't think that depends on the logical data type.

You state the problem, you have no idea about how buffering works. And how local views work. Because otherwise you'd know why you need two TABLEUPDATE()s and you'd not use the function completely without parameters and ignoring the tableupdate result.

You want every change to go back to the base table parts_used.dbf right away despite the buffered view. The first TABLEUPDATE() works on the buffer of the view cursor the grid is bound to, the second TABLEUPDATE() works on the underlying table, that also must be buffered. So you let the changes go back to the DBF file in two steps. But that can fail and you don't cover that case. To do so could be very complex depending on what quality level of conflict resolution you want. The simplest is overwrite - last change wins. And TABLEUPDATE() has a parameter for that: lForce.

As it seems you're not really interested in needing to deep dive into how to do shared data access and buffering, make use of lForce. (No, that's no Star Wars reference, actually, lForce is from the dark side, as you won't notice overwriting another users' change and the other user also won't get informed about it).

If you take this weekend or the lockdown to go deeper with your VFP knowledge, a first pointer of understanding this would be learning why OLDVAL() and CURVAL() exist and what the differences between them are. Then also how local views open the DBF in its own workarea too, and how that's buffered can depend on defaults you have once set with CURSORSETPROP for workarea 0, which means all workareas used from now on. You're buffering both view and dbf workarea, thus need two tableupdates.

To be clear, you're not seeking where you have your CURSORSETPROP of buffering, the second TABLEUPDA() usually working already is proof you're buffering the DBF workarea, too. Otherwise, you'd not just get a soft error return value .F. from an update-conflict that keeps the unsaved change in the buffer. Instead, you'd get a hard system error about not being able to use TABLEUPDATE() without buffering the workarea.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[mjcmkrsr]

Hi Jimmy,

In addition to Olaf's remarks, please check that the CheckBox may be updated? See under "Project Manger - Data - Local Views - YourView - Modify - Update Criteria" and make sure a "checked sign" is on the left of the corresponding field.

hth

MarK

 

[mjcmkrsr]

...

and make sure that both checkbox values are of the same type - sometimes checkbox initiates to .T./.F. or 0/1

hth

marK

 

[jimmygw]

Clearly, my knowledge of the inner workings of the update process is limited. But the fact that it is working for other data types ie. Character, Numeric and Date but not Logical is what baffles me. With my limited knowledge, I don't see a difference between them with respect to how they would update.
The appropriate field is checked as updatable in view designer. I have removed the grid and checkbox and attempted to modify the field from a textbox (T/F). Same error occurrred.

Jimmy

 

[Olaf Doschke]

Yes, you already said that.

If you change your code to this, what do you get?

Local lSiccess1, lSuccess2

lSuccess1=TABLEUPDATE()
If NOT lSuccess1
   Messagebox("Something went wrong committing the view buffer to the DBF workarea buffer")
   Return
Endif
SELECT parts_used
lSuccess2=TABLEUPDATE()
IF NOT lSuccess2
   Messagebox("Something went wrong committing the buffer to the DBF file")
   Return
Endif
=REQUERY('vparts_used')
thisform.Refresh

To sketch a path to a solution: Once you know which tableupdate does not work, you can concentrate on why. There are ways to find out which conflict makes the tableupdate fail using more verbose usage of TABLEUPDATE and looking into CURVAL() and OLDVAL() of any fields.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Olaf Doschke]

Another question: Do you have controls outside the grid bind to the DBF workarea? Then you're causing havoc. When you establish a view, that's not only for the grid, everything now has to bind to the view, because if you make changes to the dbf workarea buffer and the view buffer, even the first TABLEUPDETE() which saves the view cursor buffer to the dbf workarea buffer will fail. You become your own concurrent user and step on your own foot.

And to be clear once more: This is a problem of the buffer not saving before you want to requery, this has nothing to do with data types, this would be a problem with any type. The only problem you can have binding wrongly to a checkbox that also allows NULL or uses 0 and 1 as values for checked, which Mark (mjcmkrsr) already pointed out.

If you bind to field names only, realize you have an ambiguous situation here because both view cursor and dbf have the same name. And if you didn't yet realize what I pointed out twice, I'lll say it once more, opening/using a view you get a view cursor workarea AND a workarea in which the view opens the underlying table itself. So you have two workareas with the same field, really same field, not just same name, and you have two buffers. That's the reason you do two tableupdate()s. It's actually simpler to work with remote views towards remote databases, as then a view only occupies its own single workarea for the view cursor, it can't open the SQL Server or whatever remote database table in a second workarea.

The reason why a local view also opens up the DBF is even extremely simple and short to explain: It executes the SQL query that defines the view. And SQL queries open up the DBFs they query in workareas.

I think you could opt to NOT buffer the workers with the DBF, ie after you use the view, SELECT the table workarea, and set its buffering to none. Then just the TABLEUPDATE on the grid cursor = view cursor workarea should commit the changes to the DBF file right away, but I'm not sure about the implications in detail. It could still well be, that you're getting into trouble with the TABLEUPDATE when you mix data binding controlsources to both view and table fields. Because of how TABLEUPDATE detects update conflicts when the field it updates does not contain the OLDVAL() it once read, it knows another user has changed it in the meantime and that makes TABLEUPDATE return .F. and not clear the buffer, which is why you can't REQUERY(). I guess you still haven't got the concept of why requery is reluctant to do that. And you don't have a force option here to let REQUERY ignore the unsaved changes and simply overwrite them with what it reads now from the DBF.

You're still not really aware of what you're dealing with here, you're transporting data between DBF and two workareas, one of which sees the DBF, the other one sees the loaded view data. And you're dealing with those workareas and their buffers. Notice: Buffers are part of a workarea, not part of a DBF file. If you don't get a grip on this you'll never write code working under all conditions and corner cases. You can wrap your head around this, just pay a few hours of your time to get this sorted out.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[jimmygw]

SOLVED--

Thanks to Mark and Olaf for your responses and thanks for suggesting that I learn more about TABLEUPDATE(), OLDVAL(), AND CURVAL(). it helped a lot.

Ultimately, the problem was the fact that my field name for the logical field was "order". I assume that since this is a 'command word' that it confused the system. I had questioned this early on but that was before I had all of my TABLEUPDATE()s in order. Once this was changed to a different logical field name "order1" it worked as it was supposed to.

If any of you recall my post from a couple weeks ago, I am rewriting a program in VFP9 that was originally done in VFP7 ( on top of a IAS Objecttalk framework.) This didn't show as a problem in VFP7 or the framework somehow compensated for it.

Anyway, thanks to all of you.

Jimmy