Solved

TQuery EOF/BOF

Posted on 2004-11-03
1,208 Views
Last Modified: 2008-02-01
Hi,

How do I know if the current record in a dataset is the first record, the last record, or somewhere in between?

I need to implement a previous / next button scenario for a dataset and need to disable the previous button if the dataset is on the first record, and similarly disable the next button if the recordset is on the last record.

Regards



0
Question by:barnarp
    20 Comments
     
    LVL 17

    Expert Comment

    by:Wim ten Brink
    Well, you already gave the answer... Use TQuery.BOF or TQuery.EOF to determine if you're at the beginning or the end of the recordset.

    However, remember that these are recordSETs... The first and last record is just meaningless for recordsets since it just depends on the order of the records. It's a SET, not a list.

    BtnPrevious.Enabled := not YourQuery.BOF;
    BtnNext.Enabled := not YourQuery.EOF;

    Preferably you validate this by using an actionlist, but if you use an actionlist, you could just as well use the TDataSetNext and TDataSetPrior actions in the actionlist and connect them to your buttons and query. Then the whole system will manage itself.
    0
     

    Author Comment

    by:barnarp
    The EOF BOF does notr seem to work, or maybe I am implementing it incorrectly;

    If my TQuery dataset only returns one record, and the datasetis on the current record, then according to Delphi BOF and EOF is false.

    0
     
    LVL 27

    Assisted Solution

    by:kretzschmar
    usual the query must do one step (forward or back) to detect the EOF/BOF
    (even if the current record does not change or is the only one)

    meikl ;-)
    0
     
    LVL 17

    Expert Comment

    by:Wim ten Brink
    Meikl is right. The dataset does need to move one step in either direction to determine if it's at the beginning or the end. Basically, since it's a SET, it technically doesn't even have a beginning and an end. But okay, recordsets are always ordered in some way and this ordered list can have a begin and end. But Delphi only knows about this once it tries to move past the begin or the end. Otherwise, it is assumed that you're just somewhere in the set.

    Some databases support a recordcounter, that you might check. You could check if (YourQuery.RecNo=0) to check for the beginning or (YourQuery.RecNo=Pred(YourQuery.RecordCount)) for checking the end of the set. But not all databases will support these methods, thus you could end up with -1 as value for YourQuery.RecNo, indicating that recordnumbers are NOT supported.
    0
     

    Author Comment

    by:barnarp
    I am using Oracle as a backend and will check out the counter.

    If the query is first opened and i use:
    First;

    then BOF is true, but that doesnt help much because I dont know if it is also the last record in the set if there are only one record because EOF is false, even after I did move a step (first method)
    0
     
    LVL 27

    Expert Comment

    by:kretzschmar
    in case of oracle u can include rownum into your select like

    select t.*, rownum from table t

    meikl ;-)
    0
     
    LVL 17

    Expert Comment

    by:Wim ten Brink
    He could include rownum in his query but he still won't know if a row is the last row or not. Even worse, if he uses ADO to connect to the database then he could change the sorting order of the recordset and thus invalidate the rownum.
    0
     

    Author Comment

    by:barnarp
    using BDE
    0
     

    Expert Comment

    by:jonas78
    Wouldnt RecordCount work?
    0
     
    LVL 27

    Expert Comment

    by:kretzschmar
    >Wouldnt RecordCount work?
    not on a query, except you scroll to the end of the dataset,
    which causes that all records will be fetched
    0
     
    LVL 5

    Expert Comment

    by:Voodooman
    Hi

    >>I need to implement a previous / next button scenario for a dataset and need to disable the previous button if the dataset is on the first record, and similarly disable the next button if the recordset is on the last record.

    do a move previous - if there is an error disable the button in your try exept?

    Try

    MyRecset.previous;

    Except

    On E: do

    cmdPrevious.enabled:=false;

    End;

    Likewise for the next button - Just an idea.

    Also, even if there is no record count you can usually check for recordCount>0 (you cant tell how many recs there are as recordcount will often only return 0 or 1 - this is the case with SQLServer and other bigger databases as there is too much overhead/impractical to count the recs).
     

    if Myrecset.recordcount>0 then

    Begin

    cmdPrevoius.enabled:=true;
    cmdNext.enabled:=true;

    end

    else

    Begin
    cmdPrevoius.enabled:=false;
    cmdNext.enabled:=false;


    End;

    Just an idea..

    Voodooman
    0
     

    Author Comment

    by:barnarp
    Hi,

    I am tried all methods I know of using Delphi to find if the next/previous record exist from the position where I am in the recordset. These include solutions from all of you. I have tried the try except block for Prior and Next (cannot find previous method), as well as findnext/findprevious methods.

    The only solution I can think of will be to use the TQuery.Recordset with a rownum field as part od the query as per  kretzschmar's comment: select t.*, rownum from table t

    The code:

    Previous.Enabled := True;
    Next.Enabled := True;

    rownum :=  FieldByName('rownum').AsInteger;
    rowtot :=  RecordCount;

    if rowtot = 1 then
    begin
    Next.Enabled := False;
    Previous.Enabled :=  False;
    end else

    if rowtot = rownum then
    begin
    Next.Enabled := False;
    end;

    This works 100% for my query without an order by. But I need to use an order by in the query.

    I thought that the rownum field would always assign '1' to the first row it fetched regardless of an order by or not.

    Any ideas?
    0
     
    LVL 27

    Expert Comment

    by:kretzschmar
    usual the rownum is given on the fetch by oracle after oracle has ordered it,
    so it should doesn't matter if the result is sorted or not, except the client does the sort

    did you tried an order by clause?
    0
     

    Author Comment

    by:barnarp
    I tried the order by clause,and instead of getting rownums:

    1
    2
    3

    I get

    3
    1
    2

    if the order by is used. I am using Oracle 806

    No order by:

    SELECT ROWNUM,c.*,l.* FROM
    control_plant_state_change c,LOCATIONS l
    WHERE c.INCIDENT_ID = 8501
    AND c.PLANT_SLOT_ID = l.location (+)

    works fine but if I put order by at the end of the statement:

    ORDER BY l.description,c.advance,c.advancetotal

    the rownum is mixed up
    0
     
    LVL 17

    Expert Comment

    by:Wim ten Brink
    Yep. Order by will mess up the rownum in Oracle. Which is why it isn't a reliable solution...
    The problem is that Delphi never knows that it's at the first or last record of a table unless you ask it to get past one of those borders...
    About RecNo... Dataset types have a function IsSequenced of type boolean that tells if RecNo is supported or not. BDE-related datasets don't support this unless you use Paradox. ADO-based datasets do support RecNo, if the database driver used also supports it. (But it's not supported again when the dataset is filtered.)

    I know it's a bit troublesome that Delphi just can't determine that easily if it's pointing to the first or last record or somewhere in the middle. There's no simple solution for this, though. But from experience I know that many users aren't bothered by it.
    0
     

    Author Comment

    by:barnarp
    Thanks,

    I need to find a solution somehow. I was thinking of keeping an array in sync with the recordset which keeps a primary key of the record I am currently on and a counter.

    the problem is I have to keep the array in sync all the time as the user moves through the dataset which doesnt seem like a good solution.

    I just cannot think of anything else. Even if I try to move the current record previous or next to test for the last record, it doesnt seem to work, it doesnt return a error if the next record is not found, it just stays on the same record. Maybe a BDE issue.

    0
     
    LVL 5

    Accepted Solution

    by:
    Hi

    Simple!!

    If it stays on the same record then you should have the solution!!

    you said << Even if I try to move the current record previous or next to test for the last record, it doesnt seem to work, it doesnt return a error if the next record is not found, it just stays on the same record.>>

    If you have an incremented autonumber field (or any unique field) then - if as you say that going past BOF or EOF the record does not change then the auto number will not change.

    Logically if you have any field with a unique ID that does not change when moving to BOF or EOF then you are either at BOF or at EOF.

    Before you do a move, set a flag something like

    procedure TForm1.Button1Click(Sender: TObject);

    var myUniqueID:real;

    begin

    myUniqueID:=myrecset.fieldbyvalue['UniqueID'];

    myrecset.prior;

    if  myrecset.fieldbyvalue['UniqueID']:=myUniqueID then

    //do something you're at BOF

    Begin


    End;

    end;


    This has to work - if as you say <<nothing happens when you move past BOF>>.

    If this is correct (which it must be) then if you move next and the value does not change then you are at eof.

    If the value changes - you are somewhere in the middle.

    Voila! c'est bon!

    Voodooman
    0
     
    LVL 17

    Expert Comment

    by:Wim ten Brink
    One option could be that after you've opened the query then you first go to the first record and get a bookmark. Then move to the last record and get a second bookmark. Then, while you walk through the recordset you could check if you've hit one of those bookmarks. But personally I just dislike this method. I prefer to just use the TActionManager, add a TDataSetFirst, TDataSetPrior, TDataSetNext and TDataSetLast and just use these actions for the navigation. They will keep themselves completely up-to-date but with the same flaw as you noticed. When tou move to the last record, TDataSetNext and TDataSetLast will only be disabled when you try to get past this last record. Then link a button to this action and you're done...
    0
     

    Author Comment

    by:barnarp
    Thanks Voodooman, this sounds great. Will try it out.

    Alex, I am not familiar with the TActionmanager, aslo sounds like a workable solution.

    Regards
    0
     
    LVL 17

    Assisted Solution

    by:Wim ten Brink
    I'm not too familiar with the TActionManager either but I do like to use it as often as possible. Basically, you drop it to your application, add the actions you need and perhaps add some additional code. But for the DB-aware actions those are not required. Even better, if you don't assign them to any datasource then they will just work on whatever data control has the focus at that moment, which is useful if you're displaying multiple DBGrids for multiple tables on the same form. But it's often more useful to link them to a single datasource and have multiple DB-aware actions.

    The actions on their turn can be linked to menu-items, popups, buttons and even some other controls. But so far I've only done reasonable simple things with them. (I often don't have to work on GUI applications, which makes me a bit unfamiliar with them.)

    At least the TActionList and the DB aware actions do save you the trouble of implementing it yourself. And while they don't automatically discover BOF/EOF they won't generate an error the first time you try to get past it and disable it to prevent you from trying to pass BOF/EOF for the second time.
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Suggested Solutions

    A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
    Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
    This video discusses moving either the default database or any database to a new volume.
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    931 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now