Re: recno() and "Record is out of range"

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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?

"Stefan Wuebbe" <stefan.wuebbe@xxxxxx> wrote in message news:%23zW%23wsSVJHA.3908@xxxxxxxxxxxxxxxxxxxxxxx

"Hii Sing Chung" <singchung@xxxxxxxxxxx> schrieb im Newsbeitrag news:1FC10069-534D-4FEB-ABC0-6FBC9D71729C@xxxxxxxxxxxxxxxx
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
-----------------------------------------------------------


.

Relevant Pages

  • Re: Why use a composite PK ever?
    ... Your ideas around surrogate key usage is completely wrong. ... we might actually need to use their DNA sequence. ... if you have used the primary key as the access through to the data from the ... completed set is presented to Foobar all at once, not a row at a time. ...
    (comp.databases)
  • Re: candidate keys in abstract parent relations
    ... strongly that a lot of the time the database designer does not consider the ... With the primary key change, I was more thinking of all the locking and page ... Whether they send back a small surrogate key or an entire ... I'd say the DBMS would be doing ...
    (comp.databases.theory)
  • Re: OIDs vs Relational Keys?
    ... You are getting confused as to the purpose and usefulness of a surrogate key, we use auto-numbering schemes like the IDENTITY 'property' to easily create a surrogate key - the natural 'primary key' is just 'meta' data within the table, it should not be duplicated around the database schema because if it should change you run into all sorts of serious situations within the database and also within the application, here are some issues.... ... Serious locking throughout the schema which will most likely cause deadlocks and severe locking contention ... If the sector_code should ever change you need only tackle one table, the rest remains as is; the application will use sector.id internally in drop downs and for reference back to the database; the suer will never see this 'id' instead the meta data of the primary key will be displayed. ...
    (comp.databases.theory)
  • Re: Primary Keys
    ... This surrogate key allows you more flexibility down the road. ... > make a concatenated primary key. ... > And how would you query the database when joining tables? ... So I guess efficiency really isn't my question, ...
    (microsoft.public.sqlserver.server)
  • Re: looping throw all values of a field to find match
    ... I do have an index file performa_invoice.cdx, the index on pinvoice_n is a primary key. ... These are the expressions I tried, ... If you do not want to move the record pointer in the alias you are ...
    (microsoft.public.fox.vfp.forms)