VFP, Refresh grid on form

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 ?

Who is Participating?

Improve company productivity with a Business Account.Sign Up

jrbbldrConnect With a Mentor Commented:
"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

Make sure your grid properties does not have an initial dataset associated with it.
jws2bayAuthor Commented:
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?

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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

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.
jws2bayAuthor Commented:

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

"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?
Olaf DoschkeSoftware DeveloperCommented:
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.
Olaf DoschkeSoftware DeveloperCommented:
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.
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

jws2bayAuthor Commented:

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.

Olaf DoschkeSoftware DeveloperCommented:
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.
jws2bayAuthor Commented:

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
jws2bayAuthor Commented:
Took me a little time to grab it but I appreciate that jrbbldr came back and explained the issues a second time
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.