?
Solved

SQL: how to include row number in result?

Posted on 2000-04-23
2
Medium Priority
?
541 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
[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
2 Comments
 
LVL 15

Accepted Solution

by:
simonet earned 300 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
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…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

777 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