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

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!


.