Solved

SQL: how to include row number in result?

Posted on 2000-04-23
2
490 Views
Last Modified: 2010-04-04
Is it possible to include the row number of a query result in the result?
I want to use the result in a TDBGrid with the row numbers displayed.  Even better would be to have the row numbers in the left fixed column of the grid.

thanks
bobby
0
Comment
Question by:rwv
2 Comments
 
LVL 15

Accepted Solution

by:
simonet earned 100 total points
ID: 2743042
Yes, it's possible.

First of all, you need to create a calculated column for the table. This column will hold the record number, and the rec# will be calculated at runtime.

Once you create that column, for each record, you have to run the procedure below, taken from the Borland TIs. YOu can call it in the OnCalculate event of the TTable component.

Note: if you're using Paradox, you can only do this using approaches like the one below. For a true SQL database server (like SQL Server, Oracle, etc), you simply retrieve the ROWID.

Q:  "How can I determine the current record number for a dataset?"

A:  If the dataset is based upon a Paradox or dBASE table then
the record number can be determined with a couple of calls to
the BDE (as shown below).  The BDE doesn't support record
numbering for datasets based upon SQL tables, so if your server
supports record numbering you will need to refer to its
documentation.

    The following function is given as part of a whole unit and
takes as its parameter any component derived from TDataset
(i.e. TTable, TQuery, TStoredProc) and returns the current
record number (greater than zero) if it is a Paradox or dBASE
table.  Otherwise, the function returns zero.


    NOTE: for dBASE tables the record number returned is always
the physical record number.  So, if your dataset is a TQuery or
you have a range set on your dataset then the number returned
won't necessarily be relative to the dataset being viewed,
rather it will be based on the record's physical position in
the underlying dBASE table.


uses
  DB, DBTables, DbiProcs, DbiTypes, DbiErrs;

function GetRecordNumber(Dataset: TDataset): Longint;
var
  CursorProps: CurProps;
  RecordProps: RECProps;
begin
  { Return 0 if dataset is not Paradox or dBASE }
  Result := 0;
  with Dataset do
  begin
    { Is the dataset active? }
    if State = dsInactive then
      raise EDatabaseError.Create('Cannot perform this operation '+
                                  'on a closed dataset');

    { We need to make this call to grab the cursor's iSeqNums }
    Check(DbiGetCursorProps(Handle, CursorProps));

    { Synchronize the BDE cursor with the Dataset's cursor }
    UpdateCursorPos;

    { Fill RecordProps with the current record's properties }
    Check(DbiGetRecord(Handle, dbiNOLOCK, nil, @RecordProps));

    { What kind of dataset are we looking at? }
    case CursorProps.iSeqNums of
      0: Result := RecordProps.iPhyRecNum;  { dBASE   }
      1: Result := RecordProps.iSeqNum;     { Paradox }
    end;
  end;
end;

end.


Yours,

Alex
0
 

Author Comment

by:rwv
ID: 2752199
Note:
  I found it wasn't neccessary to use the above function GetRecordNumber using a TQuery and Paradox tables.
  Simply define the OnCalcFields proc for the query and update the calculated field, in this case RecordNum:

   DataSet.FieldByName('RecordNum').AsInteger := DataSet.RecNo;
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
PDF library for Delphi 2 105
Delphi TcxGrid group footer summary 3 211
Delphi - replicating a form 8 65
Help on project with Soap 10 48
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

896 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

18 Experts available now in Live!

Get 1:1 Help Now