VFP,  Refresh grid on form

Posted on 2010-08-27
Last Modified: 2012-05-10
Working in VFP9.0 SP2.  This is the first time I'm trying to use a GRID. I thought I had this grid working, but with testing I find it is displaying old data some of the time.

I'm doing a one-to-many form using the grid for the many.
The grid is populated from a cursor ordered_lst useing a SQL statement.

SELECT ordered_lst.qty,  part_no,  name,  base_price  FROM  ordered_lst

I will run the form the first time, and everything seems fine. I can then quit and restart the form with a new data set.  The grid will still be displaying the first data set.  I can see that the cursor is correct with a browse command.

I've tried putting a THISFORM.refresh in the page refresh.

How do I get the grid to purge the old data set & refresh itself ?

Question by:jws2bay
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
  • +1

Expert Comment

ID: 33544214
Make sure your grid properties does not have an initial dataset associated with it.

Author Comment

ID: 33544269
rr miles:

I'm not sure what you are asking.  Shouldn't the grid show what ever I have in the cursor?    How do I check for this?

LVL 12

Expert Comment

ID: 33544313
Lets work through this a step at a time.

1. What is the name of the Cursor created by executing:
    SELECT ordered_lst.qty,  part_no,  name,  base_price  FROM  ordered_lst

I don't see where you have any  INTO CURSOR MyResults   line in your SQL Query statement

1. "the grid is populated from a cursor ordered_lst "

So the Grid.RecordSourceType is:  ALIAS ?

And the Grid.RecordSource is:  MyResults ?   (or whatever you have named the results cursor)

2. Do you run  
  SELECT ordered_lst.qty,;        part_no,;        name, ;        base_price;    FROM  ordered_lst ;    INTO CURSOR MyResults
in the  Grid's Load method so that each time the form is launched a new result is created?

With a bit more information we can better assist you.

Good Luck

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Expert Comment

ID: 33544500
Your problem shows that you can update the grid when changing to another dataset. The problem is only on the initial form load? This would indicate you know how to change the dataset. So, check the initial grid properties, on your form, for an initial association.

Author Comment

ID: 33544567

What I am doing is creating the cursor outside of the grid properties.
I have these statement earlier on the page.

SELECT *  FROM  remote_view1  INTO CURSOR  download WHERE remote_view1.order_number = thisform.page1.lock_up_no.displayvalue

SELECT  *  FROM  remote_view2  INTO CURSOR   ordered_list   WHERE  remote_view2.order_number = download.order_no

The first is pulling in the master order info, and the second is pulling in the item list of the order.

In the grid ROWSOURCE I have

SELECT ordered_lst.qty,  part_no,  name,  base_price  FROM  ordered_lst


Expert Comment

ID: 33544591
"In the grid ROWSOURCE I have
SELECT ordered_lst.qty,  part_no,  name,  base_price  FROM  ordered_lst"

Is this the dataset that is displayed before the second time the VFP app is loaded?
LVL 29

Expert Comment

by:Olaf Doschke
ID: 33545083
Don't SELECT FROM remote_view, but USE remote_view. To pull only one order number, paramterize the view.
With an SQL SELECT FROM the view you are creating a cursor independant of the view, you can't requery that, you would rather need to do the sql again. It's not quite the same in respect to a grid recordsource, as the grid will go blank if you recreate vs requery it's alias.

And then you don't use the recordsourcetype SQL Statement but alias.

Bye, Olaf.
LVL 29

Expert Comment

by:Olaf Doschke
ID: 33549004
In regard to the recordsourcetype SQL Statement:

This does not work without selecting data INTO CURSOR. What you will see is a browse window and the grid will not show the data. But even changing that to create a cursor, that type of recordsource is no good, as it lacks a way of returning changes to it's source.

What you need is a view with the data you want to display in the grid, then use recordsourcetype alias and set the recorsource to the name of the alias/view. The view should do the filtering and joining of underlying data the way you need it for display.

Bye, Olaf.
LVL 12

Expert Comment

ID: 33549213
Grid's do not like their RowSource 'disappearing' and then 're-appearing' even for a miniscule period of time.   When that happens the Grid will most often misbehave.

Based on the code you posted above it appears as though you might be doing just that.

You seem to be dynamically creating cursor  download   based on the value of  thisform.page1.lock_up_no.displayvalue  which, in a form might possibly be changing.

Then you dynamically create cursor  ordered_list  based on the values in download

And then finally you want the Grid  RowSource to be dynamically created and used based on the cursor   ordered_list

If that is what is happening, then it is a 'recipe' for problems.

You should create your cursors statically in your Form's LOAD method
Maybe something like:
      SELECT RV2.*,;
        FROM  Remote_View2 RV2, Remote_View1 RV1;
        WHERE RV1.Order_Number = RV2.Order_Number;
        INTO CURSOR  Ordered_list READWRITE

      SELECT Ordered_List
      INDEX Order_number TAG OrdNo

And then either use an Index (preferred method) or a Filter to modify the Grid cursor's display and represent the results of the form values changing.

In that way, you are not dynamically dropping the Grid's RowSource cursor as you build a new one.

And, as I said above and as Olaf has repeated immediately above, set your Grid's RowSourceType to ALIAS and the RowSource to the static Ordered_List.

You can always use a  ThisForm.Grid1.Refresh  when the associated form value(s) change.

Good Luck


Author Comment

ID: 33570965

You are correct in how I had set it up.   The grid would work the first time through, and then it gets stuck. I'm a little frustrated with this grid.   I have gone back and built a new remote view (remote_data)which joins three tables.   I'm doing a select to filter down to a specific order as follows

SELECT * FROM   remote_data   INTO   odr_data   WHERE

I can see I have the right info using a browse, and I can populate the general fields, but when I get to the grid it is blank.

Grid's RecordSource = ord_data
Grid's RecordSourceType=ALIAS

I'm trying to bring in order information from a web store.   I have potentially many items in one order.   I am under the impression that using a grid is the best way to do this.   Should I be looking at some other approach?   My head is pretty sore from hitting it on this grid problem.

LVL 12

Accepted Solution

jrbbldr earned 500 total points
ID: 33571776
"I'm doing a select to filter down to a specific order"

Don't do a SELECT SQL to 'filter' down to a specific order.  
Because each time you execute your query, you are dropping the previous resultant cursor and re-creating a new one.  As I said before  Grids don't like to have their RecordSource vanish and then reappear.  

Instead use an INDEX or a FILTER on the over-all results data table/cursor.

While I personally would recommend using the INDEX approach, here is one method:
NOTE:   you would be using     Grid's RecordSource = remote_data

   SELECT remote_data
   SET FILTER TO Order_No = thisform.page1.lookup.displayvalue

Alternatively in your LOAD Method where you should be building your Remote_Data cursor (READWRITE), you could do something like:

SELECT Remote_Data
INDEX ON Order_No TAG OrdNo FOR Order_No = cOrdNo

where  cOrdNo = thisform.page1.lookup.displayvalue in the Interactivechange method for the form combobox.

Good Luck

LVL 29

Expert Comment

by:Olaf Doschke
ID: 33574393
Use view parameterization to filter, you can REQUERY a view to filter with another value then, and the grid won't get blank incontrast to a SQL-Select.

Bye, Olaf.

Author Comment

ID: 33578796

Okey, I thought I was having the problem because I had a SELECT on a SELECT.
 I switched over to the SELECTINDEX and it seem to work fine. This has been a learning experience.
Thanks for the help

Author Closing Comment

ID: 33578816
Took me a little time to grab it but I appreciate that jrbbldr came back and explained the issues a second time

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Report to PDF and TIF 31 803
VFP9 Server Can't lock file 22 133
How to manipulate a memo field to extract  string contents? 3 189
VFP9 options in the cloud (and elsewhere) 3 176
Microsoft Visual FoxPro (short VFP) is a programming language with it’s own IDE and database, ranking somewhat between Access and VB.NET + SQL Server (Express). Product Description: (http://msd…
By reading this blog, MSPs will gain insight into how to improve communications with their clients as well as establish a more profitable business.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question