changes to grid ignored 1

[Akourou]

hello,

i have a form with a grid
RecordSourceType = alias
RecordSource = sResults

i populate it during runtime from an sql statement
SELECT blah blah blah INTO CURSOR sResults
I get my data into the grid.

but whatever changes i make to the grid column titles and width during design are ignored.
i can only change that during runtime.

is there a way to keep that changes i make during design?

thank you

 

[Mike Lewis]

This is a familiar problem.

When you do the SQL SELECT, the existing record source is destroyed just before the cursor is recreated. When the record source is destroyed, the grid loses its columns, along with all the properties and methods that you assigend to the columns at design time.

You can sometimes solve the problem simply by setting the record source to a blank string immediately before you do the SELECT. If that doesn't work, the solution is a little more complicated. Have a look at this page, Controlling Visual FoxPro grid data dynamically, where you will find an explanation and sample code.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Akourou]

the tip with the blank recordsource worked.

thank you

 

[Olaf Doschke]

Mike explains a wellknown problem when you rebuild the grid recordsource. But even if you only do the query once at the start of a form, notice the setting of RecordSource = sResults is not just a specification, it's a promise the sResults cursor already exists, when the grid inits.

And now you have to know about order of initialization events. Form init runs after all control inits, so you can refer to all controls within init code. It's not the first code running when you start a form.

Form load is what runs before, and at that time no controls exist yet. You can also query in grid init, your last chance, the last moment to build up that workarea, so the grid can display it. This is also because the recordsourcetype query work just at the last moment it can still work.

So also changing to that recordsourcetype and specifying the query there would help.

Side note: What Mike shows has the name "safe select". That's not a Microsoft term, but a term I think the Foxite community coined, the method may even be older than this community name, though.

The core point is that the grid dislikes even the shortest of moments the recordsource workarea isn't present, and when you have a workarea sResults and query INTO CURSOR sREsults, that causes such a moment the old sResults alias is closed before the new one is established, as Mike already said, and that triggers the grid to forget all columns.

Here a two step demo of what happens with grids. First step:

Use (_samples+"Northwind\employees.dbf")
_screen.AddObject("grid1","grid")
_screen.grid1.Visible = .t.

This also shows another aspect of grids, unlike other controls grid columns can't exist without any recordsource, so even though this code just creates a native grid class, which has no recordsource value, it displays the currently selected workarea. It won't do so, if at runtime recordsource already is predefined, but that also is something adding to unexpected behaviour, if you don't know it.

Now the second step:

Use

Simply close the grids recordsource, that makes it go blank. It's not just empty cells, it's not just gridlines turned to .f., you can examine it: _screen.grid1.ColumnCount will be 0 now, the grid doesn't just display no records, it strips off all its columns.

You can now also open the table again, or some other, and when you set the recordsource you'll get new columns. But they are new, they are at defaults for the column object aside of automatically bound to the recordsource columns. grid.ColumnN is bound to the Nth field of the recordsource.

But yes, that makes it seem all your designtime settings have no effect. Well, they would, but the objects you modified at designtime simply don't exist anymore, what you see after a query into the same workarea name is new column objects, the grid went through a stage without columns to the final state of new columns and all your design time efforts are lost.

Besides preparing an empty cursor in form load or grid init at last chance, which you then ZAP and feed with data, the query type recordsource only works once, too, you can't reinit the grid, because calling grid1.Init() only repeats doing code in it, not the native init event behavior, thus also the core reason to not call events, that just causes wrong expectations of triggering event code.

Another common and working solution is to use views, you can requery views to let data change in the view cursor workarea without it being closed at any point, you can have that same effect with cursoradapter and their cursor and REQUERY(alias) works in both cases, it's simpler to change the sql query at runtime by setting cursoradapter.selectcmd, so that's more versatile than views. and of course you can use the ZAP and APPEND method when your grid recordsource is not a DBF, If it was you'd really ZAP and therefore empty it. It always bears a risk to ZAP, even if you ZAP IN (grid.recordsource) to avoid zapping another currently selected workarea with a DBF. Obviously, once grid.recordsource is a DBF, too, you delete your backend data. Besides working with ZAP also needs SET SAFETY OFF, and I'd only recommend that temporarily.

And yet another solution for the case this is only a one time init problem: Set Form.Bindcontrols=.f. and you can delay any control data binding to a moment you like. For example you may query with parameters passed to form.init via parameters. You don't have these parameters in form.load and as I said form.init already is too late for the grid. But with Form.Bindcontrols=.f. the grid hasn't yet stripped off its columns. You can do your query and then set form.bindcontrols=.t., so this also is a nice and easy way to have control about the controls.

Other controls are less strict and forgive a non-existing controlsource, but the listbox also is sensitive to that, so it's always a good idea to first have at least empty cursors and fill them with data later or have a more relaxed way of initing as you like and then define the moment the data binding gets into effect with form.BindControls.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name