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

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!




.