Jump to content




Recent Status Updates

View All Updates

Binpress - Cut your development time and costs in half
Photo
- - - - -

Show Row Number in DBGrid

delphi dbgrid row number table row number

  • Please log in to reply
No replies to this topic

#1 Luthfi

Luthfi

    CC Leader

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1,318 posts
  • Programming Language:PHP, Delphi/Object Pascal, Pascal, Transact-SQL
  • Learning:C, Java, PHP

Posted 26 February 2013 - 07:38 PM

Overview

There are often times when we need row numbering in DBGrid. Ease of reference is one of the reason. Of course you can store row number in database and use it like you would other field. However soon you will find the effort to maintain this kind of solution not worth the gain. The best solution would be to delegate this burden to database client.

In our case, we need local code in our Delphi application to show row numbers in TDBGrid.

Warning!! This is not for the faint-hearted! Really. Misusing the technique I am about to show you might teleport you to the land of Narnia. With slight possibility of landing some 2 thousands feet above the ground. Continue only if you think I was kidding or you really have the courage. You've been warned!

In this tutorial I will show you how to row numbering in DBGrid in form level. Meaning that we don't create descendant of TDBGrid that has this feature. If you have read my other articles, I believe you already knew the benefit of this approach, ie. quick implementation should you need the feature only once or twice throughout the project. When you need the feature more than twice, I really recommend to implement this feature in descendant of TDBGrid.

In the following example I will use delphi application from my other database tutorial. It's Manipulating Database Record with Codes. Go to that tutorial and download the demo project.


Implementation Example

  • Open demo project and open its main form (FormMain)
  • Select DBGrid1, right click on it and click on Columns Editor....
    DesignTime0040_ColumnsEditorMenu.png
  • On the shown Column Editor, right click on it and click on Add All Fields. Or click on the button circled in red shown below.
    DesignTime0050_ColumnsEditorAddAllColumns.png
  • Persistent columns will be added to DBGrid1 according to detected fields of its linked dataset (Table1).
    DesignTime0060_ColumnsEditorAllColumnsAdded.png
  • Add another column to DBGrid1, and move it to the top. We will use this column to show the row numbers.
    DesignTime0070_ColumnsEditorNewColumnAdded.png
  • Give this new column a title of Row No.. Use the column's Title.Caption property to do this.
    DesignTime0080_NewColumTitleCaption.png
  • Now locate DBGrid1's OnDrawColumnCell in the Object Inspector, double click in it and use the following codes for the event handler.
    procedure TFormMain.DBGrid1DrawColumnCell(Sender: TObject;
      const Rect: TRect; DataCol: Integer; Column: TColumn;
      State: TGridDrawState);
    var
      vDbGrid: TDBGrid absolute Sender;
    begin
      // only do the next line if this event generated for 
      // the first columnn (column with index 0)
      if DataCol=0 then
        vDbGrid.Canvas.TextOut(Rect.Left + 2
                               , Rect.Top + 2
                               , IntToStr(vDbGrid.DataSource.DataSet.RecNo));
    end;
    

Demo Run

Now we are ready to test our codes. Hit F9 key to execute our example project from Delphi IDE. After that, check your surrounding. Are you teleported to magical land of Narnia? If so, please hurry and find good hiding place and observe your surroundings carefully. And good luck!

If you are lucky to stay in this boring mundane world, then you probably got what shown below. Only of you haven't alter our MS-Access database example.

RunTime1000_FirstRun.png


Now you might have said, "Wait a minute! There is something fishy going on here!" right after you noticed that ID and RowNo columns have exactly the same values. If they are the same, why can't we just use values of ID column? No need for those extra stupid steps!

Well not exactly. Values in ID column came from field id which is autonumbering field. When new record inserted its autonumbering field(s) will be automatically be given integer value of the current internal counter. After that the internal counter will be incremented (usually by one) to be used by next new record. Since usually this internal counter starts from 1, we got a coincidental where our ID values match the row number.

However, autonumbering fields usually designed to not to be changed throughout their lifetime. You don't change values in autonumbering fields. So if you delete a record from your table, usually you dont change autonumbering fields of susequent records. Nor they would adjust themselves.

So for example, if we delete record #1, you will get ID column starts from 2, while RowNo still correctly starts from 1. Like shown below.

RunTime1010_AfterDeleteFirstRecord.png

Of course you can add codes to adjust ID values so they match their row numbers. But that might break data integrity, or would need a lot of codes to keep data integrity and the codes would unnecessarily slow down the process.

Actually that is not the only reason, the separation of row numbering codes from the actual database allow you to sort the data in whatever you like. Just imagine what will happen if we use ID column for row numbering and we need to sort the table by Name or LastName column? Ugly, I believe.

Full source code of the example project is attached. Feel free to use it for anything you want.

Attached File  DBGrid Row Numbering.zip   413.9KB   706 downloads

Enjoy!


Edited by LuthfiHakim, 15 March 2013 - 08:22 PM.

  • 0





Powered by binpress