Changing Data in Original Table

[FoxEgg]

Hi Folks

I am hoping to exploit the season's good will, because I fear my question is a bit basic and I probably deserve to be hauled over the coals for not knowing how to do this....
I have searched and tried to find the action I want to do.... without avail.

OK so (and no criticism yet please) .... I am converting a much loved FPDos program to run on VFP8... I AM NOT rewriting everything yet... (just replacing FORMS with print outs that I wrote in HP PCL5 escape codes... and trying to replace browse commands with Grids etc)

SO...
In my original prg, one command brings up a BROWSE with some fields editable, some not... It works... It is S-L-O-W but it works... OK so no problems so far.

To upgrade, I thought I would use SQL Select either into a CURSOR or into a BROWSE screen... I can do this... but these are READ ONLY temporary files.

So here is the question:

1. How do I change the data in the READ ONLY screens ( [READWRITE] didn't seem to do anything ) to make it editable.

and

2. If I do manage to change the data in the CURSOR or BROWSE screens... how do I then convey that data change back to and alter the original table ...


My original FPD program line was

BROW fields ENTRY_NUM :2 :H= '#', PRINT :P='!' :2 :H= 'Pr', TRANSFER :4 :P='!!!' :H= 'T/F' , percent_fe :3 :H= '%', FILE_NUM :R :18 :P='!!!!!!!!!!!!!!!!!!' :H= 'File Number',  LAST_NAME :R :12 :H= 'Last Name', item_numb :R :6 :H= 'Item #', idx_sequen :2, SERVICE :19 :R  TIMEOUT(15) NOMENU WINDOW MARY for file_num = fnum && 250299 removed -->> service :10

It still works in VFP8 but in both FPD and VFP8 it is slow....

My latest attempt is

SELECT entry_num, print, transfer, percent_fe, file_num, last_name, item_numb, idx_sequen, service from 1 ;
WHERE file_num = fnum;
INTO cursor brwser2 readwrite

I do know how to use GRIDS ... (but the calibre of my excellence is better described as 'trial and error')

OK so be nice... Its my birthday soon, so don't carve me up.

Fox Egg

 

[Olaf Doschke]

Your reports may still be driven by tables and now should be driven by the view. As parameterized it, you also have to set parameters, or your view query result is empty.

Bye, Olaf.

 

[FoxEgg]

Thanks Doc, (and other Docs)

I have to about the 9th January 2015 to effect the conversion.... Then I start seeing patients again...
In the meantime the hospital that we are building is nearing completion... and other paperwork beckons
Thanks for the tip on the Printing...
2:21 am here in sunny Sydney... gotta finish an article before I get back to VFP

Cheers
and Happy New Year

Maybe that brilliant Romanian Lass could write Auld Land Syne in VFP

John Fox

Sydney, Australia

 

[Olaf Doschke]

One more thought about reports: Did you change them at all? Since the view only is there to feed the forms/screens and the data still is stored in dbfs, the reports won't need to change. You may only need to set filter or select the table instead of the view cursor to get a report running.

Bye, Olaf.

 

[FoxEgg]

Hi Olaf,

Nope I didn't change anything... Had a whole pile of one page BLANK reports until I changed the line to

REPORT FORM t:\xray.frx TO PRINTER FOR RECNO()= 24032

Previously I used a 'WHILE' or a 'FOR' clause... They printed the blank page

For whatever reason the recno() phrase PRINTS ONE NON-BLANK Report... which is all that I want

Don't worry about the reports for now... I will sort it tomorrow (my time)

Thanks for looking at it

John Fox
2:45am

Sydney, Australia

 

[Olaf Doschke]

Well, if you dodn't changed reports to use views they would continue to work, as the tables still are used with the views querying and modifying them, you only have to SELECT sometable and then REPORT FORM... FOR your usual condition.

Bye, Olaf.

 

[Dan Freeman]

Olaf is being nice.

NEVER USE RECNO(). FOR ANYTHING. (With the usual caveat that I don't believe in "never say never")

But imagine a scenario where your library has 25 books, stored in order. You ask the librarian "bring me book #25".

The next day the librarian buys a new book and sticks it between #3 and #4. You again ask the librarian "bring me book #25" but this time you get a different book.

You may (rightly) immediately think "that's a stupid way to store books" and you'd be right! That's why they don't store books that way!

But it's what Recno() represents.

Instead, there's an entire science (called Library Science) behind cataloging books so they each have a unique identifier, and you request those books by that identifier. That's what the GUID you learned about earlier is for.

 

[Mike Lewis]

NEVER USE RECNO(). FOR ANYTHING.

Very sensible advice, and most of the time I would agree. But not always.

I use RECNO() if I need to move to a different record in a cursor, and them move back again. I save the original record number, go to the other one, do whatever I have to do, and then go back to the saved record number. Maybe that's not best practice, but it's that little bit quicker than using the primary key.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Well Mike, and that's the very thing I warned about. You can make it work with new records and their negative record numbers, too, if you apply another strict rule instead: Always use table buffering mode. Then the situation the negative recno changes to a positive recno only occurs when you actively and (hopefully) willingly commit data via TABLEUPDATE(). So there you have your never never: I agree you can do it.

As I said I inherited code that failed on the very simple nRECNO=RECNO(), do something GO nRECNO, because that "do something" went out of the scope of allowed things to let the initial record be found by it's nRECNO. You can say this is an error in doing unallowed things intermediately, but it's hard to fix all that in all the places with inherited code.

Indeed, as dan correctly points out I have a bit of an anger here, because primary key are missing all over the place in legacy applications. That's also simply just lack of knowledge, and on the language and database side lack of enforcing this best practice. It's actually not a best practice, but a must.

Here's a demo why this recno trap even is true for cursors:

Create Cursor curtest (id I autoinc)
Set Multilocks On
CursorSetProp("Buffering",5) && table buffering
Append Blank
? RECNO(), id    && -1,1
Append Blank
? RECNO(), id    && -2,2
* Assume this is the initial record we want to move back to, so we remember the recno:
nRECNO = RECNO()
Append Blank
? RECNO(), id    && -3,3
Go nRecno        && works
? RECNO(), id    && as expected, we moved to record -2, id 2: -2,2 
TableUpdate(.t.) && after this update there are no negative recnos
Go nRECNO        && errors

In comparison to the recno, the id is computed by autoinc once and the field is readonly, it never changes.

What I try to do here is set up rules, that won't cause any problems for you, if you simply follow them. Rules that save you from the need to find root causes buried deep in detail behaviour you don't know and may never stumble upon.

There's more to it, eg just COPY leaves a row buffered row and let's it get another recno invalidating the recno you remember in the nRECNO variable, as it happened here, any move from a row buffered record commits it, this is done implicitly and you have to know that. Also cursors you get from sqlexec may have a buffermode you don't expect, CURSORSETPROP("Buffering",n,0), which is applied to workarea 0 has an effect on all future used work areas, PACK may cause recno to change, etc. etc etc. Many things may happen and break GO nRECNO, not only the update.

And yes, if you're experienced enough, you know when it works and what not to do. You will find posts, where I recommended GO to a remembered recno, as it works normally. In a situation you can overview it may be simpler then instead doing nID = ID and later SEEK(nID,"curtest","id"), but that code is not only faster but also just two lines. Even better documenting in which work area you move where. And this doesn't depend on the type of the primary key, id could also be a GUID. Plus you won't need to set and reset the index order, that's all in the SEEK() function (instead of the SEEK command).

You can disregard my rules, once you know better and you two, Mike and Dan, surely do. It's a matter of taste perhaps, but you surely also have your strict rules you only break, because you know you can. Like an experienced taxi driver overrules traffic rules sometimes not only to be faster or earn more money, but to save themselves, their car and passengers from an otherwise inevitable accident. So you may think of taxi drivers as you like and statistics might prove they are involved in more accidents, but to use this metaphor once more, only young drivers drive reckless or innocent, experienced drivers are driving safer, and old drivers again may get both too cautious or fail in decisions. That has another reasoning of course...

I am verbose, precise, difficult perhaps. And I'm maybe not formulating this very nice. I'm not meaning to be angry, though. In the end it's everyones own decision.

Bye, Olaf.

 

[Dan Freeman]

Very sensible advice, and most of the time I would agree. But not always.

As I said, "never say never".