Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1445
  • Last Modified:

TQuery EOF/BOF

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
barnarp
Asked:
barnarp
  • 7
  • 6
  • 4
  • +2
3 Solutions
 
Wim ten BrinkSelf-employed developerCommented:
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
 
barnarpAuthor Commented:
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
 
kretzschmarCommented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Wim ten BrinkSelf-employed developerCommented:
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
 
barnarpAuthor Commented:
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
 
kretzschmarCommented:
in case of oracle u can include rownum into your select like

select t.*, rownum from table t

meikl ;-)
0
 
Wim ten BrinkSelf-employed developerCommented:
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
 
barnarpAuthor Commented:
using BDE
0
 
jonas78Commented:
Wouldnt RecordCount work?
0
 
kretzschmarCommented:
>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
 
VoodoomanCommented:
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
 
barnarpAuthor Commented:
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
 
kretzschmarCommented:
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
 
barnarpAuthor Commented:
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
 
Wim ten BrinkSelf-employed developerCommented:
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
 
barnarpAuthor Commented:
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
 
VoodoomanCommented:
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
 
Wim ten BrinkSelf-employed developerCommented:
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
 
barnarpAuthor Commented:
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
 
Wim ten BrinkSelf-employed developerCommented:
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 7
  • 6
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now