recno() and "Record is out of range"
Hii Sing Chung
I just happened to look through several forms written by my friend, he
always has statements like this:
local recdno
select atable
recdno=recno()
...
...
..(do something)...
..(add new record)....
...
..
goto recdno
...
..(do something)....
at the point of "goto recdno" I always encounter "Record is out of range".
I believe during the adding of record, the recno() has changed, so recdno
then points to non-existence record number. The program did perform OK in
the older version of Foxpro (version 6). The author also didn't know why it
behaviour such way. Basically, the logic is to keep record of the current
record position, then do something, after that go back to the current
record. Is there a better way of achieving this?
Thanks in advance.
Sing Chung
Stefan Wuebbe
It depends - when you for example have an SQL result cursor
or View, the RecNo() may vary and you may want to use the
content of a surrogate primary-key column instead.
RecNo() would work with a filtered or ordered table alias.
In both cases, you can take care for situations where the formerly
current row is not in the result anymore, e.g. for a PK search:
If Not Seek(m.oldPkValue, 'theAlias', 'theIndexTag')
Go Top In theAlias && or Bottom if you want
Endif
Or for a RecNo search use Locate instead of Go To:
LOCATE RECORD m.recdno
If Eof('theAlias') && or If Not Found(...
Go Bottom In theAlias && or Top, whatever
Endif
hth
-Stefan
"Hii Sing Chung" <sing...@hotmail.com> schrieb im Newsbeitrag
news:DC7898B0-582B-451E...@microsoft.com...
Hii Sing Chung
Correct me if I am wrong, from the solution you provided, we are trying to
locate the recdno, if EOF encountered, then goto the top or bottom. That is
the current strategy I used, but it still hasn't solved the problem of
returning the record pointer to the original record we are working on. As I
check for EOF, if true, goto bottom, the user will always see the last
record after she keyed in a new record (or make modification of an existing
record) and if I don't check EOF, it will give "Record is out of range"
error.
How do I make sure it will return me to the position of original record (in
this case the newly created record or just modified record)?
"Stefan Wuebbe" <stefan...@gmx.de> wrote in message
news:e%23zHaVGV...@TK2MSFTNGP02.phx.gbl...
Stefan Wuebbe
> Stefan,
>
> Correct me if I am wrong, from the solution you provided, we are trying to locate the
> recdno, if EOF encountered, then goto the top or bottom.
Yes. The difference between "Go To 3" and "Locate Record 3" is
that the latter will not cause an error if there is no 3rd row in the result
anymore, in the first place.
> That is the current strategy I used, but it still hasn't solved the problem of returning
> the record pointer to the original record we are working on. As I check for EOF, if
> true, goto bottom, the user will always see the last record after she keyed in a new
> record (or make modification of an existing record) and if I don't check EOF, it will
> give "Record is out of range" error.
> How do I make sure it will return me to the position of original record (in this case
> the newly created record or just modified record)?
The problem may be that after requiring the row set, the former row
is not in the same position anymore, either because the queried table
was updated by another user, or because sequence may vary depending
on the new filter or an SQL Order By clause or Set Order To .
In that case, you may not want to use Recno() as a row identifier at all -
with any kind of SQL, it's a good idea to have a "surrogate primary-key"
column, say call it cust_id in a customers table and put some "meaningless"
unique values in there, for example a string like "025D53F4-5C29-C844-8E26-1EAEC7CD9C01"
that you'd get from the CoCreateGUID() API.
hth
-Stefan
--
|\_/| ------ ProLib - programmers liberty -----------------
(.. ) Our MVPs and MCPs make the Fox run....
- / See us at www.prolib.de or www.AFPages.de
-----------------------------------------------------------
Hii Sing Chung
Is it that by adding a surrogate key or a primary key I just keep the key in
memory first, then no matter if the record pointer has moved elsewhere, I
can move it back to its original record by reference the key in memory?
"Stefan Wuebbe" <stefan...@gmx.de> wrote in message
news:%23zW%23wsSVJ...@TK2MSFTNGP06.phx.gbl...
Stefan Wuebbe
> Stefan,
>
> Is it that by adding a surrogate key or a primary key I just keep the key in memory
> first, then no matter if the record pointer has moved elsewhere, I can move it back to
> its original record by reference the key in memory?
When you store the content of the surrogate primary-key of a particlar
row in a local variable, then you actually have its unique identifier.
Whether that particular row is still available after whatever your code
does to requery the current row set, may depend on that code.
So, if the row is still available, you will Seek() or Locate For successfully.
But if it's not, say because the user changed the filter parameters in the
meantime, then your code may want to do something useful, like Go Top.
However, the actual difference between a surrogate primary key column
and your previous approach to use RecNo() as a row identifier is that
the latter does not work when you have a Local View or do a "Select ...
Into Cursor ..." or so, because the very same row may have a different
recNo() by accident depending on Where and Order By.
swdev2
.....
select atable
goto recdno
That should fix you.
--
===================
William Sanders / EFG VFP / mySql / MS-SQL
www.efgroup.net/vfpwebhosting
www.terrafox.net www.viasqlserver.net
"Hii Sing Chung" <sing...@hotmail.com> wrote in message
news:DC7898B0-582B-451E...@microsoft.com...
Olaf Doschke
> recdno=recno()
> ...
> ...
> ..(do something)...
it depends on "do something",
eg if the record you are on is a new
record and you do tableupdate() or
just leave the record in row buffer
mode, the recdno is not be available anymore-
Check out it's value, especially for new records
it is negative, as the real recno can't yet
be determined, the record ss only buffered and
not stored. But when it is, you can't return
to it by it's provisorial negative
recno.
You need to reidentify the record by
an identifying field, like Stefan
suggested already.
Bye, Olaf.