Solved

SQL: how to include row number in result?

Posted on 2000-04-23
2
505 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
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…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

861 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