[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

TQuery EOF/BOF

Posted on 2004-11-03
20
Medium Priority
?
1,385 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
Comment
Question by:barnarp
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 4
  • +2
20 Comments
 
LVL 17

Expert Comment

by:Wim ten Brink
ID: 12482099
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
ID: 12482142
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
kretzschmar earned 200 total points
ID: 12482274
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 17

Expert Comment

by:Wim ten Brink
ID: 12482351
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
ID: 12483509
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
ID: 12483576
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
ID: 12484082
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
ID: 12484151
using BDE
0
 

Expert Comment

by:jonas78
ID: 12485833
Wouldnt RecordCount work?
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 12486141
>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
ID: 12488685
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
ID: 12503921
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
ID: 12504286
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
ID: 12504750
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
ID: 12505414
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
ID: 12505707
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:
Voodooman earned 400 total points
ID: 12507083
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
ID: 12514234
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
ID: 12531544
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
Wim ten Brink earned 200 total points
ID: 12532951
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

649 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