Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: tom nolan
  Where is tom nolan?
 tom nolan
 To: tom nolan
  Where is tom nolan?
 tom nolan
Subject: RE: grid for cursor object
Thread ID: 15602 Message ID: 15855 # Views: 7 # Ratings: 0
Version: Unknown Category: General VFP Topics
Date: Wednesday, October 2, 2002 12:26:47 PM         

> > oh, i forgot! i'm using third party database; MySQL.
> > nop, the data doesn't had any change at all (before and afterI refresh them).
> > unfortunally it happened too for MS SQL7.
> > i still can't solve the problems, but thanks for the advice!
> >
> > >> Act like a rabbit then growl like a lion
> > << seventys@mailbox.as
> Okay. Another 'fix' I had in earlier versions was very much a sledgehammer approach, but worked. My grid was populated from a table resulting from a SELECT. So everytime I re-ran the SELECT, the Recordsource was obviously rewritten and this caused the Grid to lose all formatting and even the data - I ended up with a completely blank grid. My previous entry on this resolved the problem for me and was much cleaner than this method, but, since the that did not work for you, then maybe you could try this.
> The following is a rough example to give you the idea:
> Assume the Grid1.Recordsource="CustInv" (a subset of the Invoices table). Originally I SELECT'ed directly into the CustInv table and had the problem - so I SELECT'ed into a temporary table first and then populated the CustInv table from it as follows:
> To refresh the grid I run a method on the main form called "Updategrid"
> Method ThisForm.UpdateGrid()
> SELECT * FROM Invoices WHERE CustId="abcdef" INTO TABLE Temp   	&& Do your SELECT into a temporary table
> Sele CustInv						&& Clear out the RecordSource (CustInv)
> APPEND FROM Temp						&& Re-fill the RecordSource (CustInv) from the Temp table
> ThisForm.Grid1.Refresh()

> Because the number of records populating the grid was always small in my case, the overhead of this sledghammer approach was tiny. Note also that the CustInv table needs to be set to Exclusive use, but since it is a temporary table just for populating a grid, it is always on the local machine and exclusive to the user, so that should not pose any problem in a multi-user environment.
> It's probably worth a try.
> Tommy

Since I last replied on this issue, I came across an interesting article on Grid Reconstruction which may be of interest to you. I copied and pasted it to a file and unfortunately have lost the source (apologies to the writer for not crediting him/her with it).


"Grid reconstruction and how to avoid it
Have you ever found a situation when your grid don't want to behave as you directed in design time? Custom controls in columns lost? Code of columns, headers or controls event not running? Read this FAQ then.

Grid reconstruction behavior is a complete removing of all grid's controls and columns and creating them again using default VFP controls. This cause lose of all methods, property settings and objects in columns and all column objects, column control to display and edit data is reset to the default text control, custom headers lost. It happens in following cases. Description 1. Grid reconstructs self always when RecordSource alias closed. If this is a view, reconstruction usually do not happens when you requery view, however, there are some reports that this could happen, so test carefully your application to see if view requery do not cause the grid reconstruction. If it is SQL statement, it happens when you assing anotehr SQL statement or just close alias used to store results of the query for grid. It also happens when you use SQL Pass-Through to query data into the alias that used as a record source of grid.

To avoid reconstruction when refreshing grid's record source, you require to assign empty string (not a one space - " ", but empty string - "") to the Record source before any of grid's record source refresh actions described above. If you already do that, just check your code if you do that in correct order or any other thing does not spoil the correct order of refreshing process. After refresh assign record source to grid again. Reconstruction in such case does not happen, however, another problem arises - all grid's columns re-bound to the control sources automatically by the phisical fields order. Following is a sample of how to fix that by little of code.

* remember control sources in the column's comment field
with {grid}
local nColumnIndex
for m.nColumnIndex = 1 to .ColumnCount
.Columns(m.nColumnIndex).Comment = .Columns(m.nColumnIndex).ControlSource
* prepare grid for refreshing of the Record Source
.RecordSource = ""
* do refreshing of the record source
with {grid}
* restore record source
.RecordSource = "{RecordSourceName}"
* restore control sources
for m.nColumnIndex = 1 to .ColumnCount
.Columns(m.nColumnIndex).ControlSource = .Columns(m.nColumnIndex).Comment
In above code {grid} is a reference to the grid object, {RecordSourceName} is a name of the alias used as a record source or SQL statement.

Significant note: do not do any refreshing of the visible controls or grid on the form after statement 'RecordSource=""' and up to full restore of the control sources. Otherwise you will meet a problem with the error message like 'Type is not supported by control' in case you use costom controls in grid columns. This because after spoiling control sources incorrect field types might be used for column. For example, when you have a checkbox in the grid column, after refresh of record source column with checkbox often get a character field control source. If then you refresh a grid, you will get an error or something weird might happen like crashes or bad refresh.

With views, this is the most common situation because grid used often to display data dynamically, so it requires to be refreshed by another data. REFRESH() command for view does not cause reconstruction, however it could be used to refresh data only record-by record. For real refresh requery is needed. The main mistake here also is just requery view and leave it as is. Its a single command, so programmers often do this in many places without aware that they're doing something bad. After that, when reconstruction behavior observed for some view requery, programmer starts to find all places where this view is requeried. It might be in many places across forms and classes that starts to be a big problem. Tip: put data requery (and all other actions with data) in one place - class method or function. Always suggest that any data function might require in future some additional code, even when it is as simple as a requery of view. This way you will help youself to save a time to find all places where some action done with data in case you need to modify something. Grid reconstruction is one of such cases that you cannot avoid when it appears.

Another approach to prevent grid reconstruction is to use BeforeRowColChange event of the grid.
BeforeRowColChange event is fired each time when grid is going to be reconstructed. It happens in any case include when grid alias closed, view requeried etc. despite grid visibility, focus and grid configuration. The most amazing is that putting NODEFAULT in this event for duration of data changes prevents grid reconstruction at all! Example:

thisform.GridRefreshing = .T. && tell all grid controls that grid data going to be requeried
... do data requery
thisform.Grid.RecordSource = thisform.Grid.RecordSource
thisform.Refresh && or grid refresh
&& after this moment grid stops to reconstruct self
thisform.GridRefreshing = .F.
In the BeforeRowColChange of grid class event put following:
if PEMStatus(thisform,"GridRefreshing",5) AND thisform.GridRefreshing
You can put above code in the grid class so this functionality will be generic.

The best thing is that this method do not require to organize restoring of the control sources of all columns. However, sometimes ot is require to set focus outside of grid and set it back to grid, because the current cell in grid might show asterisks ('*******') when avoiding reconstruction this way.

Unfortunately, there are no way to know the reason why BeforeRowColChange event is called to distinguish if it is called for recornstruction or it is called for movement between cells or some other actions with grid. Just use a flag for that as in the sample.

2. Reconstruction happens when grid is initialized and record source property is empty or record source does not exists (alias is not open). In this case grid reconstructs self and use current alias as a record source if opened (or keep self empty if no alias opened in current workarea, but all columns destroyed anyway). If you need to open record source in some other event than Load event of the form (before grid initializing), use following technique.

In the Load event of form create an empty cursor with the same structure as a record source for grid; record source property of grid should use that empty cursor. Then, when you open real data, assign empty string to grid's record source, open data and then assign again real data alias as described in the paragraph 1. For case you need a generic container with grid, put an invisible custom control that will create empty cursor in its Init event. However, assure that Init event of that control fires BEFORE Init event of the grid, otherwise reconstruction will happen.

3. Grid reconstructs self when column count changed to 0 or -1. I hope you never do this, do you? ;)

4. Grid reconstructs self when alias used as record source and it goes out of scope. This usually happens when record source assigned in one data session, but grid really initialized in another
data session, so when it tries to refresh self, another data session used where record source does not exists. This may occur also in other situations when programmer uses data sessions switching extensively.

Another popular approach to eliminate the grid reconstruction problem is dynamic grid creation. Make a custom grid class with all your code and columns definitions. When requery data, remove grid control from form, requery data, then add grid to form again in run-time. This requires to handle first adding of grid, set some properties of grid etc etc.

You can also create grid object in run-time and populate it by the custom controls using code. (Note that you can define custom header class.) However, after grid reconstructs self, you need to add these custom controls to grid again. This approach used in case when grid reconstruction is not avoidable, for example, in the administrative programs - to show any table content in the same grid, but also allow some functionality in grid like editboxes to see memo fields, sorting by click on headers etc. The sample code of creating grid in run-time and add some custom controls you can see in the FAQ#721

I hope this help you to figuire out a problem with your grid. Good luck and don't be frustrated, grid is very good control in VFP with no analogue!

How to refresh grid properly after sorting
I have seen many questions about sorting of a grid by clicking on its header and proper refreshing of grid after sorting. There is a reliable way to sort (index) grid cursor and properly refresh the grid without change of the record number AND with very good looking. I made this in my grid class and tested it already 2 years so far. Description Note that the way I used has a requirement for the grid cursor - it should not contain changes or may contain, but only in table buffering mode. This is because the changes will be automatically updated when the record pointer moved for cursors in the row buffering mode. I prohibit any sorting until the user saves changes - a solution on the interface level.

Following is the code from my grid class commented and fixed to do not confuse by details of my framework:

* Grid.RefreshAfterSorting
* Used grid custom properties:
* lSorting - flag that indicates sorting refreshing, so all activity related with row
* change in grid should be disabled for the duration of this routine
* nRecno - stores record number of current row in grid. This property should be populated
* in the AfterRowColChange event by record number each time record number changed
* lInit - flag, should be .T. when Init method is running

lparameters poColumn
* column reference should be passed of column being sorted
* called internally only as event from the column header after sorting
with this
.lSorting = .T.
&& above flag used in the AfterRowColChange and other grid events to do not fire
&& any code during running of this method. This method might cause weird record
&& moving by grid when sorted, so anyway it is required. We will force record
&& moveng too for better refreshing.

local lcOldArea, lcGridAlias
m.lcOldArea = select(0)

m.lcGridAlias = this.RecordSource
select (m.lcGridAlias)

* store 'real' grid position, this is needed because it might change in the
* next chapter
local nOldRecNo
m.nOldRecNo = .nRecno && nRecNo - property that updated in AfterRowColChange event
&& of grid in AfterRowColChange event flag lSorting prevents
&& change of this property

&& restore row position - it might be changed during indexing and cell activation

&& after refresh after sorting you can see grid shows, for example, last row only,
&& when really it contains 3 rows and all fit into grid. This is a problem we going
&& to solve by following code lines

* this must be BEFORE any refresh of grid
if between(m.nOldRecNo,1,reccount())
LOCATE && FOR .T., same as 'go top', but works more quickly for
&& filtered data

&& activate cell that is appropriate to sorting column,
&& so grid will not scroll out from sorting column. When we do not do this, after
&& click on the header, grid will scroll to current cell (column) focused, that
&& might hide (scroll out) column that is just sorted - looks confusing.
if !.lInit && flag that is .T. when Init method is running.
if between(m.nOldRecNo,1,reccount())
go (m.nOldRecNo)
&& during Grid Init, however, we should not do this because SetFocus will
&& cause error. Just refresh it. This is needed to check for case when we
&& want to restore grid sorting when form loads from user preferenses
if between(m.nOldRecNo,1,reccount())
go (m.nOldRecNo)

select (m.lcOldArea)

DOEVENTS && this is required before any other actions to correctly refresh grid
&& and do not move record pointer weirdly
.lSorting = .F.
It is also recommended to use thisform.LockScreen to prevent the grid flashing - hide all grid refreshing from users, so they will see just the results.

When screen is locked, first Refresh commands are required just for repositioning current grid scrolling position. As a result, the work grid does for displaying (drawing) records gone for nothing. In cause you have really a lot of things placed in the Dynamic* properties of the grid columns to organize some fancy things, like highlighting of the current record or colour grid cells by different colours appropriate to some conditions in the data records, it will look quite slow when doing multiple refreshes like in this article. To speed it up, use very simple approach: Before first refreshes just after locking the screen, backup all Dynamic* settings of all columns in some array or additional properties, then clear them all. Just before the last refresh restore all Dynamic* property values. This speeds up the total time of above approach running. "


grid for cursor object Posted by Taz @ 9/25/2002 9:39:03 PM
RE: grid for cursor object Posted by Simon Arnold @ 9/25/2002 10:01:48 PM
RE: grid for cursor object Posted by tom nolan @ 9/26/2002 7:25:18 PM
RE: grid for cursor object Posted by Taz @ 9/27/2002 12:56:03 AM
RE: grid for cursor object Posted by tom nolan @ 9/27/2002 2:10:28 AM
RE: grid for cursor object Posted by tom nolan @ 10/2/2002 12:26:47 PM