when I Use SQL Select I lose my Grid Design

[mikeisvfp]

Hello Experts

when I Use sql Select I lose my Grid Design

what I mean by grid design is my header fonts are bolded, my columns are wider in size. but when I run my form everything is plain and ugly.

is there a way around this?

here's what i have in my load event

SELECT a.startdate, a.enddate, ;
       ALLTRIM(c.firstname)+" "+ALLTRIM(c.LastName) AS NAME,;
b.subtotal, b.hst, b.grandtotal FROM appointment a ;
JOIN billing b ON a.appointid = b.appointid ;
JOIN customers c ON c.customerid = a.customerid ;
ORDER BY a.startdate ;
INTO CURSOR curappointment READWRITE 
SELECT * ;  
FROM curAppointment ;  
WHERE .F. ;  
INTO CURSOR curHoldAppt

GO TOP IN curappointment

And in the Init

this.grdpricinglist.RecordSource = "curappointment"
this.grdpricinglist.refresh()

 

[Olaf Doschke]

Well, if you have a design of the grid with columns and headers (Captions) etc. then also speecify the controlsources of each column and the grid recordsource at design time.

The moment you set Reccordsource, the grid is reset, even if the fields of that cursor match the grid layout.

What helps a bit is setting form.bindcontrols = .f. and only setting it .t. at the end of form.init(), when all controlsources, rosources and recordsources are set up correctly.

But that won't hinder the grid to lose it's layout. Either you do all at designtime or all at runtime.

Bye, Olaf.

 

[Dan Freeman]

Grids are evil, but they do follow a set of rules.

The problem here is that SELECT ... INTO <cursor> first closes the original cursor, which signals the grid to completely deconstruct and revert to defaults. Its data source has closed.

Before running your query, set the grid's controlsource to an empty string ("") and re-set it to the name of the cursor after the new cursor has been built. That doesn't usually trigger the self-destruct mechanism because no cursor that the grid is "watching" is actually closed.

Another way is to always run your queries into a cursor not connected to the grid's controlsource. Zap the controlsource and append from the query result.

Either way, the point is to NOT pull the rug out from under the grid by closing its controlsource.

 

[EzLogic]

before your select sql.

put this code:
this.grdpricinglist.RecordSource = ""

Your select sql here..

then

set the record source

Ali Koumaiha
TeknoSoft Inc.
Michigan

 

[Olaf Doschke]

You have to add two facts to understand, why you alreadyreset the grid in form.init:

1. Form.init() is after Grid.init(), more general: first all form interior inits so the form init can refer to it's interior.

2. Even if grid.recordsource isn't set at all at designtime, the grid will take what it finds at its init() and bind to it.

So even though you likely do nothing in the grid.init(), the grid does bind to some open alias, perhaps it even already binds to curHoldAppt, as that is the active workarea after form.load(). You could check that by WAIT WINDOW thisform.grdpricinglist.RecordSource in form.init.

That is why you already are confronted with the grid reconstruction problem, even though you're at the init phase.

Still there is some more to say about Recordsource="": This is a solution, but not an ideal. It still does not preserve all grid/column properties. IIRC you lose code in the textbox, as that is still regenerated when you finally set the recordsource to "curappointment". Especially, because most probably the grid was bound to curHoldAppt before, even though it has the same fields, if controlsources were alread automatically inited to curHoldAppt.field1, curHoldAppt.field2 etc, setting grid.recordsource to "curappointment" invalidates all these column controlsources, so the grid loses it's layout.

If you want to stay with the advantages of designing the grid at design time with columns widths, captions etc. Then set the recordsource to curappointment at design time and, if you don't want to display all fields in order of the cursor, set the columns controlsources to the fields curappointment will have.

It is sufficient the cursor exists in the form load before all inits occur, for the grid to not reconstruct, but then just don't set the grid recordsource in form.init anymore.

form.bindcontrols =.f. also helps especially with the grid for the first init phase, as it will then not bind itself to something it finds, but wait for the moment form.bincontrols is set .t.

If you're still confused about what to do, come back. Actually, you should already have some forms with grids working, and look into them how you did things there. Most probably you didn't set recordsource to them at runtime.

Bye, Olaf.

 

[Mike Lewis]

In addition to the good advice you've already been given, I've got an article that addresses this particular issue:

Controlling Visual FoxPro grid data dynamically

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

That's a good article about the phenomenon.

Mikeisvfp, we also discussed the phenomenon in detail in MSDN Forums - "VFP recreates my cursor every time I execute SELECT"

And naomi there pointed you towards Andy Krameks article about: Safe Select

Bye, Olaf.

 

[mikeisvfp]

Hi guys

I went along with Olafs steps above and everything is working fine, although I do have a question...

form.bindcontrols =.f. also helps especially with the grid for the first init phase, as it will then not bind itself to something it finds, but wait for the moment form.bincontrols is set .t.

I totally forgot to bindcontrols, but it still works...
My question is do I really need to bindcontrols false and then true?

Maybe I should mention what I did to get this to work.

First I initially set my RecordSource to "curappointment"
then in the grid columns contolsources i did "currappointment.stardate" and so forth, obviously without the quotes. I then omitted "thisform.grdpricinglist.recordsource = curappointment" and left only my grid refresh()in the init.

please let me know if bindcontrols is necessary at this point?

 

[Olaf Doschke]

With what you did, you don't need bindcontrols anymore, yes.

I'd still keep it, as it allows to influence every controls data binding in the form init before finally "setting the form on data", so to say.

And why avoid it? Even just the time to think about doing this or not will cost you more of your life time than you loose doing this always: It does save a few nanoseconds to set or not set a property to .t..

"My question is do I really need to bindcontrols false and then true?"
What is not to understand about this? There is only true and false, would it make sense to have it true from the start on?
If it's true from the beginning, you lose that control about the moment the form controls bind to data.

If you wonder, why the default value of that property actually is true: Because that property was not existing from version 1 on. It was introduced in VFP7 I think, maybe even VFP8, before foxpro was always binding to data at first. So for downward compatibility, for code to work without setting it true, it is true by default. You can cope without it, but why do without it? It's a feature even I always use, it's not a feature just to make vfp easier for novices.

"I totally forgot"
Well, object oriented programming would solve that problem, you program once, and then can actually forget about it, as it's already coded, and can concentrate on new things:

Have your project open, then create a form class to do this, then forget about it:

Cd Justpath(_vfp.ActiveProject.Name)
If !Directory("classes")
   mkdir classes
   cd classes
Endif
Create Class baseform Of baseclasses.vcx As Form

Now in the property window set bindcontrols = .f., in the init() set bindcontrols = .t.

Now Save. Go into the menu "tools", choose "options". In the Forms tab find the section "Template classes". Tick the checkbox at "Form", then click the browse button at the right, then select the baseclasses.vcx and choose its baseform class.

From now on, every form you create will be based on that baseform class, and so every change you make there is done on ALL forms you generated.

Now there is only one more thing to know: Your forms will now do this automatic, this is called inheritance. The property window will also tell you the init coe is inherited from baseform.

If you write something in your forms init() you will overload this inherited code, to still execute it, you need to add DODEFAULT(). To set bindcontrol=.t. at the end of inits, you'd put DODEFAULT() at the end of your init() code of course.

From that perspecitve you could also do bindcontrols =.t. in all your forms instead of Dodefault(), you don't save much overhead to think about, but you can do much much more in the baseform init you always want done in all your forms. Once you get into the habit you'll get ideas enough to even have more base classes.

To change all the forms you already have to this baseform behaviour you can use the class browser, but it's not needed at this moment.

Classes are not that complicated, of course they cost a bit learning time, but they save lot's of time in not needing to redo stuff with every new form or control or whatever.

Bye, Olaf.