How to Refresh a DBGrid without Losing the Current Row Position

[torhenrik]

Hi forum,

I have a little problem with positioning in DBGrid after update of records.

First of all, this is a dataset where several users are working on at the same time. They might update records, but never delete or add.

I use a customized version of the sample code I have found on

http://delphi.about.com/od/delphitips2008/qt/dbgrid_row_pos.htm

 THackDBGrid = class(TDBGrid)

procedure... 
   rowDelta := -1 + THackDBGrid(DBGrid1).Row;
   with AdoQuery1 do
   begin
      row := RecNo;
      <I close, open an requery AdoQuery1 to check if someone else has done something to the records>	
      DisableControls;
      RecNo := row;
      MoveBy(-rowDelta) ;
      MoveBy(rowDelta) ;
      EnableControls;
      <I do the update I want to do to the record, post, close, open and requery>
      DisableControls;
      RecNo := row;
      MoveBy(-rowDelta) ;
      MoveBy(rowDelta) ;
      EnableControls;
   end;

this works fine when the active record > middle record. But when active record< middle record the correct record is highlighted, but it changes position to the middle of the dbgrid. Is there a way to get the dbgrid NOT to change the position for the active record to the middle if position is above the middle?

 

[DjangMan]

Are you issuing the MoveBy commands to save the record?

 

[majlumbo]

procedure ....
var
bk: TBookMark;
begin
AdoQuery1.DisableControls;
try
bk := AdoQuery1.GetBookMark;
try
{Do your processing here...}

GotoBookMark(bk);
Distance := AdoQuery1.MoveBy
(THackDBGrid(DBGrid1).VisibleRowCount-1);
Table.MoveBy(-Distance);
finally
FreeBookMark(bk);
end;
finally
AdoQuery1.EnableControls;
end;
end;

I haven't tested it, but should be a good framework to start from...

 

[torhenrik]

Thank you for your quick replies

Djangman: No I am using it to posision the active record in the dbgrid after processing of AdoQuery.

majlumbo: I tried your suggestion and the record I am updating jumps to the top of the grid.

Let me try to clarify, I see that my first post maybe was a bit unclear.

I have a database table containing many clients. It can be several hundreds. Many users can work with these clients at the same time but only one user pr client. To avoid users to accidentally try to enter the same client I update the database table and show this in the grid. This update is done by users clicking in the grid with the mouse (and described in my code from the first post).

An example:
There is 150 clients and the user sees 40 at a time (due to this is what there is room for on the screen). Right now he is looking at client 100-140 on his screen. If he clicks any client between 121-140 everythings works as I want. The client is updated so everyone else can see it is taken and nothing else moves on the screen. If he click client 100-120 the client is updated as it should BUT the client centers to the middle of the grid. I want it to update and not move. How do I do that?

 

[DjangMan]

This link discusses your problem almost exactly:

http://edn.embarcadero.com/article/28051

Positioning in a grid.
by Philippe Randour (philippe_randour@hotmail.com)

The problem
Imagine that you want to perform a search on a TTable component connected to a TDBGrid component. You will then use, for instance, the method Locate of the TTable component. The net result will be that the found record will appear in the middle of the visible rows in the grid. Now, imagine that the specifications for your application require that the found record be in top of the list of visible rows. What will you do? In fact, you will have to modify the natural behavior of the grid component to achieve this result. It will be done by scrolling down the view on the table data. And how will you do that?

The solution

The solution lies in the MoveBy method of the TTable component. The purpose of this method is to position the cursor on a record relative to the current record in the dataset, thus making the TDBGrid scroll accordingly. Its almost perfect but you also want to keep the same current record. So you will have to call this method twice. The first one to scroll down the view to make your record the first visible in the grid, the second one to make it the current record again. I can see some of you saying: "Its a great idea but we lack a property giving us the number of visible rows in a TDBGrid". You are right but a possibility exists if you dig a little bit in the VCL hierarchy for the TDBGrid. TDBGrid is descending from TCustomGrid, which has a property called VisibleRowCount. Unfortunately, this property is protected, so you cannot access it directly in your code. If you try, the compiler will give you an "Undeclared identifier: VisibleRowCount" error. Its because a protected property is only accessible by code that appears in the same unit. To turn around this problem, you will create a derived class whose sole purpose will be to make a cast of your grid to the new class and access the protected property through it. And voil`, problem solved!

You can see it applied in the following code:

type
  TGridCracker = class(TCustomGrid);


begin
  Table.Locate('Common_Name','Firefish',[]);
  { Change the current record to a record not currently visible
    and located VisibleRowCount-1 rows farther }
  Distance := Table.MoveBy(TGridCracker(DBGrid).VisibleRowCount-1);
  { Make the previously found record the active one. The active 
    record will then be the first visible in the grid }
  Table.MoveBy(-Distance);
end;

 

[majlumbo]

There is another exposed property that is available due to your 'Hack', and that is 'THackDBGrid(DBGrid1).TopRow'

Alter the code I gave you to make THackDBGrid(DBGrid1).TopRow the current record after the try and before the AdoQuery1.GetBookMark;

That should make it work correctly...

 

[djjd47130]

I have had similar issues before, and I solved those issues simply by not using the DBGrid at all, but rather a StringGrid. I built a rather large unit especially for converting a data set into a string grid, along with providing most of the same functionality as the DBGrid, and of course it doesn't have the same common issues. Most importantly, I can update the entire grid without the user even seeing one little glitch. Please let me know if you want this component, I would have to clean it up first for you, it's rather specific to my work.

JD Solutions