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....



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.



Persistent columns will be added to DBGrid1 according to detected fields of its linked dataset (Table1).



Add another column to DBGrid1, and move it to the top. We will use this column to show the row numbers.



Give this new column a title of Row No.. Use the column's Title.Caption property to do this.



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.









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.







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.



DBGrid Row Numbering.zip 413.9KB 2615 downloads



Enjoy!

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