• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 360
  • Last Modified:

Can't use RecNo ... alternatives?

The RecNo function function does not work with my queries (Oracle).

I would like to be able to display to my users a "Record 1 of 9"  message as they scroll through the DBControlGrid.
I can use RecordCount to get the "of 9" part but short of a complicated algorithm (bookmarking, looping thre dataset, etc) to determine record position each time the user adds/deletes/scrolls have not been able to find a replacement for the RecNo function.  

Any suggestions?
0
djake021198
Asked:
djake021198
1 Solution
 
GreedyCommented:
Some Oracal Guru will probably know a better way but here are two ways to solve it...once again considering speed it's super important here since your giving back user input this once again implies that it only has to be as fast as a human...

this first way can be used if you can't change the structure of your Oracal table

procedure TForm1.Button1Click(Sender: TObject);
var
  MyCounter: Integer;
  Found: Boolean;
begin
  MyCurrentRecordKey := Table1.FieldByName('YourPrimaryIndexField').AsString;
  Table1.DisableControls;
  Table1.First;
  MyCounter := 1;
  Found := False;
  While (not Table1.EOF) and (not Found) do
  begin
    if MyCurrentRecordKey = Table1.FieldByName('YourPrimaryIndexField').AsString then
      Found := True
    else
    begin
      Table1.Next;
      inc(MyCounter);
    end;
  end;
  Table1.EnableControls;
  Label1.Caption := IntToStr(MyCounter);
end;

Now a better way (faster) to do this would be to add a field called RecordNumber and then just return this in your UpDateLabel routine.  You will also have to make calls to renumber all the records when they do a delete or an insert but that's not too bad just a while loop and a counter... now I keep talking about speed but please note that both of these methods take less than half a second to compleate on my database of about 30,000 records running on a small MSSQL server useing SQL Links so I would bet Oracal shaves another .1 sec off this you can also use TQueries to do this stuff for at least another .2 sec off the times.  Have you looked to see if the Oracal site has any help on this yet?

0
 
KECommented:
I don't know much of Oracle servers, but maybee you could make a stored procedure which returns your rows (of course) but also a programatically generated record number. This is just an idea !!!

0
 
vladikaCommented:
There is RowNum statement in the Oracle.
Try use it in your query

select RowNum, ..... from ....


0
 
ronit051397Commented:
You should ask this question in the Oracle forum, maybe they will tell you the correct sql statement/s.
0
 
bozo7Commented:
I got around this by using the following code (example)

function Function1:INteger;
begin
 query1.sql.add('select (count(*)) Count from table1);
 query1.prepare;
 query1.open;
 result := fieldbyname('Count').value;
end;

I ran into the same thing going from Interbase to oracle. This was the best way to do it.
Bozo
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now