setting recordsource in a grid at runtime resets all column information in a grid

setting recordsource in a grid at runtime resets all column information in a grid

Post by ghj » Fri, 23 Sep 2005 00:43:19


I have a form with a grid where the record source was a parameterized remote
view ( called 'myremoteview'). The remote view could bring down 1000s of
rows (or the entire table) depending on the parameters. If the user were run
a query (using requery() command against the paramertized remote view ) to
request all the table rows, the query could take a long time (which is
expected).

However I needed a way to allow the user to cancel the query (escape from
the requery() ) and from what I have read here, that functionality is not
possible with remote views.

Therefore, I have tried using the SQLEXEC command , by retrieving the SQL
code from the parameterized view and naming the result set the same as
'myremoteviw':

sSqlcmd = DBGETPROP("myremoteview","view","sql")
sqlexec(myconnection,sSqlcmd,'myremoteview')

This gets the data and I think I can cancel out of the command, but my grid
disappears. When I set MyGrid.Rowsource= MyGrid.RowSource, the grid comes
back and all the columns definitions are lost and reset.

Is there any way to preserve the grid settings before updating the
rowsource, or is there a better way do to this.

Thanks
 
 
 

setting recordsource in a grid at runtime resets all column information in a grid

Post by Mike Prat » Fri, 23 Sep 2005 01:00:48

Hi,

You, my freind are a vicim of "Grid Reconstruction". The soloution is to set
the grid.controlsource="", change the data and the set grid.controlsource
back to what it was.

Regards,

Mike


remote
run
to
grid

 
 
 

setting recordsource in a grid at runtime resets all column information in a grid

Post by ghj » Fri, 23 Sep 2005 04:54:36

This does not seem to work. The grid gets reconstructed.
 
 
 

setting recordsource in a grid at runtime resets all column information in a grid

Post by Pierre Alb » Fri, 23 Sep 2005 06:33:36

Hi,

...and it can not work. As soon as you add a "new" recordsource to the
grid, it gets a "reboot".

I only know two ways to solve this problem:
- either to create a cursor and to use it as a permanent recordsource:
ZAP it and APPEND the data from the requeried view.
- or to save the grid settings; not that much of a problem with VFP8 or
higher:



* Sample Method

LOCAL lcMak, lnRunVar, lnColumnCount, loGrid

m.loGrid = THISFORM.Grid1
m.lnColumnCount = m.loGrid.ColumnCount

* First saving the column widths to a dynamic set of variables
FOR m.lnRunVar = 1 TO m.lnColumnCount
m.lcMak = "m.lnWidthCol" + TRANSFORM(m.lnRunVar) + " =
m.loGrid.Column" + TRANSFORM(m.lnRunVar) + ".Width"
&lcMak
ENDFOR

* Second, do not forget the column order
FOR m.lnRunVar = 1 TO m.lnColumnCount
m.lcMak = "m.lnOrderCol" + TRANSFORM(m.lnRunVar) + " =
m.loGrid.Column" + TRANSFORM(m.lnRunVar) + ".ColumnOrder"
&lcMak
ENDFOR

* Third, header captions come handy
FOR m.lnRunVar = 1 TO m.lnColumnCount
m.lcMak = "m.lcHeaderCol" + TRANSFORM(m.lnRunVar) + " =
m.loGrid.Column" + TRANSFORM(m.lnRunVar) + ".Header1.Caption"
&lcMak
ENDFOR

* Then sweep the grid
m.loGrid.RecordSource = ""

* Requery the data; if canceled, no need to show the data again
IF do_something_to_requery_the_data() = .F.
RETURN
ENDIF


* The Requery is done
m.loGrid.RecourdSource = "foobar" && ..or whatever

* ..so, let's redo the grid and release the local vars

* Header Captions
FOR m.lnRunVar = 1 TO m.lnColumnCount
m.lcMak = "m.loGridColumn" + TRANSFORM(m.lnRunVar) +
".Header1.Caption = m.lcHeaderCol" + TRANSFORM(m.lnRunVar)
&lcMak
m.lcMak = "RELEASE lcHeaderCol" + TRANSFORM(m.lnRunVar)
&lcMak
ENDFOR

* ColumnOrder
FOR m.lnRunVar = 1 TO m.lnColumnCount
m.lcMak = "m.loGrid.Column" + TRANSFORM(m.lnRunVar) + ".ColumnOrder =
m.lnOrderCol" + TRANSFORM(m.lnRunVar)
&lcMak
m.lcMak = "RELEASE lnOrderCol" + TRANSFORM(m.lnRunVar)
&lcMak
ENDFOR

* column widths
FOR m.lnRunVar = 1 TO m.lnColumnCount
m.lcMak = "m.loGrid.Column" + TRANSFORM(m.lnRunVar) + ".Width =
m.lnWidthCol" + TRANSFORM(m.lnRunVar)
&lcMak
m.lcMak = "RELEASE lnWidthCol" + TRANSFORM(m.lnRunVar)
&lcMak
ENDFOR




--
HTH,
Pierre

/"\ ASCII Ribbon Campaign
\ /
X Against HTML
/ \ in e-mail & news
 
 
 

setting recordsource in a grid at runtime resets all column information in a grid

Post by Jack Jacks » Fri, 23 Sep 2005 06:51:46

This has always worked fine for me.

Set the RecordSource to ''. Then rebuild the cursor. Then set the
RecordSource back to the cursor.

Are you by any chance calling either the form Refresh() or the grid
Refresh() while the RecordSource is set to ''?

On Wed, 21 Sep 2005 14:33:36 -0700, "Pierre Albisser"
 
 
 

setting recordsource in a grid at runtime resets all column information in a grid

Post by ghj » Fri, 23 Sep 2005 06:52:22

I have custom controls for the columns so I don't think method 2 will work.
I have considered method 1.

Thanks
 
 
 

setting recordsource in a grid at runtime resets all column information in a grid

Post by Dan Freema » Fri, 23 Sep 2005 07:04:04

Then you're doing it wrong. <g>

It most certainly works.

Any time you *close* a datasource that is bound to a grid (which you're
doing), you'll cause the grid to deconstruct. So remove the datasource
before running your query, and put back THE SAME datasource after running
your query.

Dan
 
 
 

setting recordsource in a grid at runtime resets all column information in a grid

Post by Pierre Alb » Fri, 23 Sep 2005 07:40:33

Custom controls... so what? ;)
I'll just need to add a few lines of codes:

* Storing custom controls
FOR m.lnRunVar = 1 TO m.lnColumnCount
lcMak = "m.lcCurrentControl"+ TRANSFORM(m.lnRunVar) + " =
m.loGrid.Column" + TRANSFORM(m.lnRunVar) + ".CurrentControl"
&lcMak

m.lcMak = "m.lnObjects"+ TRANSFORM(m.lnRunVar) + " = m.loGrid.Column"
+ TRANSFORM(m.lnRunVar) + ".ControlCount"
&lcMak
m.lcvarMak = " m.lnObjects" + TRANSFORM(m.lnRunVar)
FOR m.lnNextRunVar = 1 TO &lcvarMak
lcMak = "m.lcControl"+ TRANSFORM(m.lnRunVar) + "Name" +
TRANSFORM(m.lnNextRunVar) + " = m.loGrid.Column" +
TRANSFORM(m.lnRunVar) + ".Objects(m.lnNextRunVar).Name"
&lcMak

lcMak = "m.lcControl"+ TRANSFORM(m.lnRunVar) + "BaseClass" +
TRANSFORM(m.lnNextRunVar) + " = m.loGrid.Column" +
TRANSFORM(m.lnRunVar) + ".Objects(m.lnNextRunVar).BaseClass"
&lcMak

lcMak = "m.lcControl"+ TRANSFORM(m.lnRunVar) + "ClassLib" +
TRANSFORM(m.lnNextRunVar) + " = m.loGrid.Column" +
TRANSFORM(m.lnRunVar) + ".Objects(m.lnNextRunVar).ClassLibrary"
&lcMak

... and so on ...


ENDFOR
ENDFOR


But yes, i have to admin: it is neither the fastest, nor the most
"elegant" solution. Personally, i just see one advantage: in method 1,
you will not be able to "work the data directly" via grid controls, as
you are no longer directly linked to the data.


--
Regards,
Pierre

/"\ ASCII Ribbon Campaign
\ /
X Against HTML
/ \ in e-mail & news
 
 
 

setting recordsource in a grid at runtime resets all column information in a grid

Post by Pierre Alb » Fri, 23 Sep 2005 07:54:44

*bang head repeatedly against the keyboard*

...tried to solve it like that several times and always made the same
mistakes...
...which means: i'll have to delete all my backups, so the proofs will
be removed. ;)

*augh*
Time to go to bed, draining my cushion with my tears of stupidity... ;)

--
Regards,
Pierre

/"\ ASCII Ribbon Campaign
\ /
X Against HTML
/ \ in e-mail & news
 
 
 

setting recordsource in a grid at runtime resets all column information in a grid

Post by Pierre Alb » Fri, 23 Sep 2005 07:56:14

Sorry for keeping you on the wrong track - Dan is painfully right...
*sigh*

--
Excuses,
Pierre

/"\ ASCII Ribbon Campaign
\ /
X Against HTML
/ \ in e-mail & news
 
 
 

setting recordsource in a grid at runtime resets all column information in a grid

Post by ghj » Fri, 23 Sep 2005 08:08:07

Maybe I am doing something wrong. Here is what I have:

The grid recordsource is set in the designer (before runtime) as

thisform.formgrid1.recordsource='myviewnamer'

The grid looks good when form is first opened.

Then I have a method that is called from a button with the following click
code:

cSqlcmd = DBGETPROP("myviewname"',"VIEW","SQL") && get sql code from
remote view, but could be any sql command
thisform.formgrid1.recordsource=''
SQLEXEC(myhandle,cSqlcmd,'myviewname')
thisform.formgrid1.recordsource='myviewnamer'

Grid looks bad.

(VFP 9.0)
 
 
 

setting recordsource in a grid at runtime resets all column information in a grid

Post by Dan Freema » Fri, 23 Sep 2005 08:28:01

nless that's a message typo (not in your actual code) on resetting
recordsource, your grid SHOULD go blank.

If it's a typo in the code you've retyped here, why not paste in the REAL
code so we can see the bits you're not telling us about? <g>

Dan



ghj wrote:


 
 
 

setting recordsource in a grid at runtime resets all column information in a grid

Post by ghj » Fri, 23 Sep 2005 09:12:16

or your info-

The grid headers and widths are preserved, but the controlsource for each
column is cleared, so the data does not match the column headings. I will
try constructing the sql statement to order the fields in the same order
they are displayed in the grid to see if that works.

*- before this code runs, the grid is fine
*- with formgrid1.rowsource='v_workordr'
Local cEsc,cError
Private cSqlcmd
cEsc = Set("escape")
Set Database To SABER
Set Console Off
Set Escape On
Set Talk Off
cSqlcmd = DBGetProp('v_workordr',"VIEW","SQL")
Wait Window 'Executing Command. Press ESC to Cancel' Nowait
On Escape Do SQLCancel
* clear recordsource before sql command, v_workordr still exists
Thisform.formgrid1.RecordSource=''
* create new cursor, v_workordr is destroyed and re-created
SQLExec(gncexe,cSqlcmd,'v_workordr')
* rowsource is reset, grid column defs lost
Thisform.formgrid1.RecordSource='v_workordr'
Set Console On



"Dan Freeman" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...


 
 
 

setting recordsource in a grid at runtime resets all column information in a grid

Post by Ander » Fri, 23 Sep 2005 10:59:16

here's a function SQLCancel(connecthandle). You can get the handle from
CursorGetProp('connecthandle', 'view'). Do you have a program or procedure
called sqlcancel?
-Anders

"ghj" < XXXX@XXXXX.COM > skrev i meddelandet news:AFmYe.5242$iu5.3828@trndny04...


 
 
 

setting recordsource in a grid at runtime resets all column information in a grid

Post by ghj » Fri, 23 Sep 2005 11:46:12

ere it is:


glcancel=.t. && global flag if escape
SQLCANCEL(gncexe) && global connection number - established at program
startup


"Anders" <anders@anders> wrote in message
news:%23ark% XXXX@XXXXX.COM ...