Re: Grids... simple, but I don't know...

Tech-Archive recommends: Fix windows errors by optimizing your registry

I'm not sure where "the report" comes in, but we're talking about a grid on
a form. Let's stick to that for now. <s>

OK, so your calling code is:

DO FORM alignrpt

In your init you call requery().

Where do you set the parameter you want the view to use during requery()?
And when do you tell the grid to refresh itself? <g>

Here's the rub: the form's init happens after the grid has built itself.
What you probably want, since the view is in the DE of the form, is to set
the view NODATAONLOAD=.t. (it's a property in the DE). In the init:

m.vehicle = "whatever" && set the parameter for the view
Requery("myview") && tell the view to fetch data matching the
parameter
thisform.Refresh() && update the controls

If you've been fiddling and setting other properties, you might want to
"Reset to default" on any non-default properties. (Right click an empty area
of the prop *** and choose "show non-default properties" to find these.)

Dan


43fan wrote:
Calling code?

Do Form alignrpt *g*

I'll try and explain(hopefully better than I have so far*g*) what it
is that I'm doing.

Two forms. First form(getveh) asks for vehicle number. When
entered, and leave the field, it displays info about that particular
vehicle, a description, etc... If the user is satisfied that it's
the one he/she wants, they click on the alignment button. Only code
there is DO FORM alignrpt.

In the init method of alignrpt, I do a REQUERY("myview").

On the form alignrpt, is a grid box. I used the builder to set the
order of the fields I want, using MYVIEW as the table, and also to
set the headings I want for the fields. No code on individual text
boxes, headers, etc... no code in any of the methods for the grid box.

There should be many records as we re-use the same vehicle for new
testing we do. The data is selected(and ordered) for the report by
the rptseq field. If it's blank(or zero) it's not included in the
report. It does record grouping based on the rptseq number.

I'm getting multiple records all with exactly the same data, all with
rptseq of 1. That won't be the case if it's getting the records
correctly... there'd maybe be 4 1's, 6 2's, 4 3's, etc, etc...
instead, using that same number sequence, I'm getting 14 records all
with rptseq = 1.


"Dan Freeman" <spam@xxxxxxxxxxxxx> wrote in message
news:OezW9%234cGHA.1792@xxxxxxxxxxxxxxxxxxxxxxx
OK, good. (I knew it would. <g>) So it's something in the form. I
can't see
through your eyes so I can't guess what setting you may have made
that I wouldn't have. So lets start at the beginning. Please post
the calling code.

We'll get into the called form once I'm 100% sure of the
"environment" going
in....

Dan

43fan wrote:
Dan,

Looks like that all works fine.

Shawn
"Dan Freeman" <spam@xxxxxxxxxxxxx> wrote in message
news:uSJgP%233cGHA.1260@xxxxxxxxxxxxxxxxxxxxxxx
Let's take the form business out of the way.

In the command window, open your database (but not your view, yet).

Still in the command window, set the view parameter variable to one
vehicle
and then USE yourview. Browse.

Does the data look right?

Still in the command window, change the view parameter variable to
a different vehicle, issue Requery('theview'), and then browse.

Does the data look right? If this part isn't working, the rest is
just a frustrating exercise in U/I madness.

Dan



43fan wrote:
Ok, thanks! It makes somewhat more sense now... I still don't
have it working properly though.

I guess I don't need to "create" the view even in the init of the
initial form,right? I took that part of the code out and it still
runs(still wrong, but *g*)

I did change it to use the ? so it's parameterized. My form that
actually pulls up the data that the query is getting uses a grid
box, I'm not sure if I can do that? I created it putting "myview"
in the data environment, and then used the builder to create the
grid. I have it set in the grid properties to use "myview" as the
data source, and type is table. When I had it all set that way,
I could do a requery() at the init of the data form, and it'd
actually change vehicles, but... I'd get multiples of the same
record. So I tried removing "myview" from the data environment,
and then of course it errored on the requery(). So I took that
out, and now it shows the right number of records(I think), but
the data never changes even though I change vehicle number.


"Dan Freeman" <spam@xxxxxxxxxxxxx> wrote in message
news:u61y0ttcGHA.3344@xxxxxxxxxxxxxxxxxxxxxxx
Just like a DBF, you create a view once and then USE it anywhere
you need it
in your code. You don't CREATE TABLE CUSTOMER every time you want
customer data, do you? <s> A view is a "permanent" part of a DBC,
not something like
CREATE CURSOR that you define on the fly.

The ? in a where clause makes the view parameterized. Whenever
VFP runs the
query (either on USE or Requery()), it will look for the memvar
following the ? and use that value in the query. Anything short
of this will result in
a hard-coded query.

The rest of the where clause can be whatever you like but SOME
part of it needs to be parameterized (with ?).

Dan

43fan wrote:
Dan, I thank you for the help and comments, but you're assuming
I know more than I do.

If I don't create the view each time the vehicle changes, how
does it get the new info?

What does the ? in WHERE aligndt.vehicle = ?m.vehicle mean?
And, I need the other part, the rptseq <> 0.





"Dan Freeman" <spam@xxxxxxxxxxxxx> wrote in message
news:urdrKdrcGHA.5048@xxxxxxxxxxxxxxxxxxxxxxx
Why are you creating your view each time? You should create it
ONCE and use
it when you need it.

Also, don't use ALLTRIM() in a query. Your mileage may vary.

Also, you don't have a parameterized view at all. WHERE
aligndt.vehicle = ?m.vehicle would take care of it.

Dan


43fan wrote:
Here's what I do in the click event of a button on my form.

*CREATE DATABASE MyData
* From here on in, just
*OPEN DATABASE myData
CREATE SQL VIEW myview AS SELECT * FROM aligndt;
WHERE vehicle = ALLTRIM(m.vehicle) AND rptseq <> 0
* From here on in, just
* lcVehicle = "137"
* USE myview


DO FORM alignrpt

The user enters a vehicle number on the form, when they exit
the field, it does a seek in the vehicle table, and displays
other info about it. They click on an alignment button, and
it runs the above code. M.VEHICLE is the variable attached
to the field they enter the vehicle number into.

It runs fine once, but when I change vehicle numbers, it
doesn't seem to actually "do" the sql.
"Dan Freeman" <spam@xxxxxxxxxxxxx> wrote in message
news:uwISU6IcGHA.3908@xxxxxxxxxxxxxxxxxxxxxxx
Well, since I'm unable to see the code you're running, the
view definition,
or any error message, I can't make any guesses. <shrug>

Dan

43fan wrote:
I've tried the Requery() and Thisform.Refresh in several
different places Dan, and no go. Either it errors or it
just doesn't actually requery. I even tried deleting the
view each time I exited so it'd be "fresh" the next time it
runs. It STILL comes up with the same vehicle's data.

"Dan Freeman" <spam@xxxxxxxxxxxxx> wrote in message
news:eoEU5v6bGHA.1260@xxxxxxxxxxxxxxxxxxxxxxx
When you change the parameter, use Requery() to refresh the
data, and the call the Form.Refresh() to update controls
based on the data.

As for why it isn't working, well, it's anyone's guess. It
could be as simple as the wrong SET ANSI setting to the
parameter variable not being scoped the way you think it
is.

Dan

43fan wrote:
Actually... it's not finding any "new" data, that's why it
doesn't appear to be refreshing. I ran the code in the
command window, and get a 0 records selected in 0.00
seconds message. Ran it using a literal for the vehicle,
and one that I know there are records for. I did a filter
on the dbf and it finds them that way just fine.

"43fan" <sleap13@xxxxxxxxxxxx> wrote in message
news:HeKdnb8WJdzg3MfZ4p2dnA@xxxxxxxxxxxxxxxxx
Thanks. Well, I do, and I don't. For some reason, the
view's not refreshing if I change the vehicle number.
Not sure why it's not either. I'm using a memory
variable to store the vehicle number, then using that
variable to do the select.

"Dan Freeman" <spam@xxxxxxxxxxxxx> wrote in message
news:eksoHL6bGHA.4040@xxxxxxxxxxxxxxxxxxxxxxx
Yep. Simple as that.

Glad you got it going!

Dan

43fan wrote:
Never mind... got it. I just use it as a table then.
:)

Thanks!!

"43fan" <sleap13@xxxxxxxxxxxx> wrote in message
news:iaqdnVJFAqMqp8fZ4p2dnA@xxxxxxxxxxxxxxxxx
Ok... got that part now(I think)... but then how do I
use it? Is there a control I place on the form to use
the view in? Initially was going to use a grid box to
have everything in, as I could name the tags on the
fields the way I wanted, put the fields in the order I
wanted, etc...

In the original FPD program, it just creates a window
and then does a browse within that window.

I tried using the grid box I already have on the form,
but evidently not doing something right there either,
as it doesn't show any data. But if I just run the
code, then do a "use myview" and a browse, it shows up
fine.

Thanks!!

"Dan Freeman" <spam@xxxxxxxxxxxxx> wrote in message
news:%238gX7Z5bGHA.3956@xxxxxxxxxxxxxxxxxxxxxxx
You're tripping over terminology.

For years in Fox2x and prior, we called a dbf file a
database. It isn't. It's a table. A database is a
collection of tables, relations, indexes, and
views. In VFP, it's represented by a DBC and you open
it with the OPEN DATABASE command.

You're not using any of the database features in your
Fox2x app because they're not supported, but you can
still use views from VFP. They must be stored in a
DBC.

CREATE DATABASE MyData
* From here on in, just
* OPEN DATABASE myData
CREATE SQL VIEW myview AS SELECT * FROM
alignmentdatabase; WHERE vehicle = ?lcVehicle
* From here on in, just
* lcVehicle = "137"
* USE myview

Dan


43fan wrote:
That's where I'm getting somewhat confused(more than
somewhat actually). Alignmentdatabase is not a VFP
database. It's a free table(FPD2.6 dbf file). I
"use alignment" prior to trying the create sql view
command, and still get the same error message. I'm
not understanding now to open a DB(that I won't be
using anything from, what needs to be in it?) and
also then use a free table to get the info I need.


"JWB" <none@xxxxxxxx> wrote in message
news:WNo6g.63$WP5.36@xxxxxxxxxxxxxxxxxxxxxxxx
you have to have the DB open. I use open database
and set default.

if !dbused('MyDB')
open database (cDefdata) + 'MyDB'
endif
set database to (cDefdata) + 'MyDB'


"43fan" <sleap13@xxxxxxxxxxxx> wrote in message
news:3-ydnQR2O68wYsTZnZ2dnUVZ_vudnZ2d@xxxxxxxxxxxxxxxxx
I'm still confused Dan. I'm trying to use the
command like the example shows, like this.
CREATE SQL VIEW myview AS SELECT * FROM
alignmentdatabase; WHERE vehicle = "137"

I get an error message stating no database is open
or no current database.



"Dan Freeman" <spam@xxxxxxxxxxxxx> wrote in
message
news:u9korOvbGHA.5104@xxxxxxxxxxxxxxxxxxxxxxx
You need a DBC to store the view itself, but you
can use free tables in the
view with no problem.

Your description of your situation is standing
on a table, waiving its arms,
and screaming PARAMETERIZED VIEWS!

You'll be surprised how much code you have to
delete to make it work, too.
<g>

Dan

43fan wrote:
Dan,

This would require using VFP databases, correct?
I'm using free tables(.dbf) that are currently
running in a FPD2.6 environment.

Shawn


"Dan Freeman" <spam@xxxxxxxxxxxxx> wrote in
message
news:uqEeeftbGHA.3872@xxxxxxxxxxxxxxxxxxxxxxx
See the help topic "Limiting the Scope of a
View", and everything related to
CREATE SQL VIEW.

Dan


43fan wrote:
Thanks Dan, I'll check out the alias
possibility. Um... parameterized view?

"Dan Freeman" <spam@xxxxxxxxxxxxx> wrote in
message
news:ewKP5ksbGHA.4372@xxxxxxxxxxxxxxxxxxxxxxx
1. Sounds like you've got the grid bound to
the wrong alias. If it isn't bound, it'll use
whatever alias is currently selected.

2. Don't use filters with grids prior to
VFP9. Ever. Put one hand on the desk and
slap it with the other. <g> This situation
is absolutely perfect for a parameterized
view.

Dan

43fan wrote:
VFP 8. Have a grid that's populated from a
table(free table). Initial form asks for
user to input a vehicle number, then it
does a seek in the vehicle db and displays
description, etc. User can then click a
button to display alignment data for this
vehicle. I have the dbf open in the data
environment, and have a filter set to filter
the records to the vehicle number the user
entered. When I click on the button and pull
up the form with the grid on it, I get a
list of records, all for that particular
vehicle, but, I'm getting just a list of
multiple records that are exactly the same.
For the one vehicle I'm using to test this
with, I should have approx. 10 records, all
different of course. That's not what I'm
seeing though, I'm seeing about 30 records,
all exactly the same.

Any help? I'm sure it's something simple,
but I'm very inexperienced with VFP.

Thanks!


.

Quantcast