Re: Serious errors with 'Create view' command

Thanks Anders again.

I think I've worked round the problem with your help!

You posted the version of the 'Create View' that was generated by my 'code'
if it's run through VFP9's version of 'Create View'. If I cut and paste THAT
version into my code for 'Create View' in VFP7, it DOES produce an 'sql view'
that looks like what I requested in 'Create View' and the tools using the
combined view now work as required!

It seems that Fox knew about this problem and have modified the performance
of Create View between VFP7 and VFP9! Nice!

As always, without you guys help, I would have thrown in the towel with
Visual Fox and moved to something else!

Ian
Greyer and balder than before!

"AA" wrote:


"Barley Man" <BarleyMan@xxxxxxxxxxxxxxxxxxxxxxxxx> skrev i meddelandet
news:068A7473-3899-4BEC-B69E-14716158ABAF@xxxxxxxxxxxxxxxx
Thanks for trying anders but I am using VFP7 which has a version of
ViewSql
that is read only!

I can't use the technique of writing 16 possible views as I can't seem to
find a way of persuading them to 'fill' the 'grid' that I am using in a
form
to display the selected SKUs!

Either you open each view as needed with a different alias and assign this
alias as Grid.RecordSource and Refresh the grid,
or you set the RecordSource to '', then open the next view with the same
alias and again set the Recordsource back as it was.

IF NOT USED View15
USE view15 ALIAS View15 IN 0
Thisform.Grid1.Recordsource='view15'
Thisform.Grid.Refresh && may not be needed

or
x = Thisform.grid1.Recordssource
Thisform.Grid1.RecordSource=''
USE IN (x)
USE View15 ALIAS x
Thisform.Grid1.RecordSource= x


I was persuaded by people on this Forum to USE this technique of a View
'driving' a grid against my better judgement! If there is a way of
Creating a
form with a 'grid that can be 'fed' by multiple 'views' I am damned if I
can
see how to do it!

Either you open the each view with a different alias and assign a the new
alias as Grid.RecordSource and Refresh the grid,
or you set the RecordSource to '', then open the next view with the same
alias and then set the Recordsource back as it was.
If you don't change views but just the parameter arguments, you
REQUERY(Thisform.Grid1.Recordsource) and the query is run again and the grid
is updated automatically.

What on EARTH does the option of 'Edit Stored procedures' do? It sesm that
I
should be able to paste my WORKING code into that 'section' and it should
regenerate the view each time I open the database. If it IS supposed to do
that, why DOESN'T it?
Why do you generate views each time? The view is already there, permanently
stored in the database, it doesn't need a procedure.

Edit Stored Procedures opens a code editing window and saves the code you
write in the database, just like MODIFY COMMAND creates a .PRG file for your
procedures. Start each code black with PROCEDURE xxx or FUNCTION yyy.
The third way to store a procedure is as methods in class.

The more I use Visual Fox, the less I am impressed with it as a product!
Microsoft have taken a RELIABLE and WORKABLE version of Fox and made it
increasingly unstable, non-intuitive and unusable in my opinion!

Your views will clear I'm sure.

"AA" wrote:

In VFP9 the view sql window is readwrite. And in VFP9 the brackets VFP
inserts are different from what you showed.

A possible solution is to let the user create ad hoc views in a temporary
database. That way you aren't forced into creating a single view that can
handle any possible combination of 1, 2 ,3 or 4 columns. If you want
classes=25 create the view
Create view Combinedview AS SELECT * FROM Inventory WHERE classes=25.
It' also possible to create 16 views to cover all contingences and then
always open one with USE viewX ALIAS Combinedview
-Anders

"Barley Man" <BarleyMan@xxxxxxxxxxxxxxxxxxxxxxxxx> skrev i meddelandet
news:B18419CD-0642-487F-BAA5-7814AB25481E@xxxxxxxxxxxxxxxx
Hi Tom

I can't get the view designer to create the coding that I need, either!
It
doesn't come out with the same brackets. How are you suggesting that I
cut
and paste it? It's the 'text' in the view designer 'sql view' that's
WRONG
in
the first place and thus cutting and pasting that would just repeat the
problem. To where are you suggesting tyhat I 'paste' the cut-out
anyway? I
can't paste it INTO the view designer sql view window as that's Read
Only!

There is the facility to 'edit stored procedures' but I can't seem to
make
that do ANYTHING!

ian



"tom knauf" wrote:

Hi,

I would create the view in the viewdesigner and cut/paste the code
generated
by the viewdesigner (right click - view sql)

HTH
Tom


"Barley Man" <BarleyMan@xxxxxxxxxxxxxxxxxxxxxxxxx> schrieb im
Newsbeitrag
news:A4DB29BB-1787-4E90-83A6-2209790A74EA@xxxxxxxxxxxxxxxx
I have used the following 'create view' statment:-

Create Sql View combinedview;
AS Select inventory.inumbr, inventory.divno, inventory.deptno,
inventory.subdeptno, inventory.classno, ;
inventory.istyl;
FROM inventory ;
WHERE (?mydivno <= 0 Or divno = ?mydivno);
AND (?mydeptno <= 0 Or deptno = ?mydeptno);
AND (?mysubdeptno <= 0 Or subdeptno = ?mysubdeptno);
AND (?myclassno <= 0 Or classno = ?myclassno)

However, when I 'inspect' (modify) the created View by looking at
the
sql,
the 'view' created is NOT the same as the one I requtested and
appears
as
follows:-

SELECT Inventory.inumbr, Inventory.divno, Inventory.deptno,;
Inventory.subdeptno, Inventory.classno, Inventory.istyl;
FROM drilldown2!inventory;
WHERE ?mydivno <= 0;
OR (Inventory.divno = ?mydivno;
AND ?mydeptno <= 0);
OR (Inventory.deptno = ?mydeptno;
AND ?mysubdeptno <= 0);
OR (Inventory.subdeptno = ?mysubdeptno;
AND ?myclassno <= 0);
OR (Inventory.classno = ?myclassno)

The change is not immediately obvious to see but it IS there. The
positioning (and thus the effects) of the brackets have changed
SUBSTANTIALLY
and the routine simply fails to perform properly. I need the EXACT
functionality of the statement contained within the 'create
view'instructions
but I simply can't get it to 'transfer' into the database as a
'view'!

It does not appear to be a simple error in the 'view sql' routine as
the
view BEHAVES EXACTLY in line with the version being displayed by
'view
sql'
and that's NOT what I want, nor what I requested!

What on EARTH am I supposed to do about THAT?

Ian









.

Relevant Pages

  • Re: Concurrent database access in SQL 2005 Mobile
    ... What version of SQL CE are you using? ... Are you accessing the database from an app written in C++? ... then opens his work forms and it's in those ... It wouldn't seem to me that you'd need a new connection to ...
    (microsoft.public.sqlserver.ce)
  • Re: Concurrent database access in SQL 2005 Mobile
    ... opening one connection for the lifetime of the app and keeping it open seems ... What version of SQL CE are you using? ... Are you accessing the database from an app written in C++? ... then opens his work forms and it's in those ...
    (microsoft.public.sqlserver.ce)
  • Re: Very slow MDE & MDB response... suggestions?
    ... > application to sql sever until the client outgrows this. ... users should always get a MDE. ... no the users share the database. ... Then the form opens, ...
    (microsoft.public.access.gettingstarted)
  • Re: sql :table does not exist or alias is not currently open
    ... Opening albe and specifying:PRIV: inside the SQL comment should make ... alias as the opened database-variable. ... Specifying a different database in the SQL-comment has never come to my ...
    (comp.databases.paradox)
  • RE: Copy table from open database to a closed database
    ... ' Opens the database ... ' sets the SQL String ... Utter Access VIP ...
    (microsoft.public.access.modulesdaovba)