Trap Delete View (not found) error

[MikeMV]

Hello,

I am working with VPF9 SP2 pulling data from an MS SQL 2005 server. I create views and sometimes I have to delete them to recreate them with different criteria.

If the view I am trying to delete does not exist I get an error and the program stops, is there a way to trap this and deal with it? What I would like to do is to have the program continue to create the view when the old one has been deleted.

I will greatly appreciate your response.

Mike.

 

[JRB-Bldr]

If the view I am trying to delete does not exist I get an error

To test for the presence of a View before you attempt to Delete it, you might want to try something like the following:

OPEN DATABASE (MyDBC)
SET DATABASE TO (My)

DIMENSION aryViews(1)
=ADBOBJECTS(aryViews,"VIEW")
IF ASCAN(aryViews,UPPER(mcViewName)) > 0
   * --- View Exists, Delete It ---
   DELETE VIEW (mcViewName)
ELSE
   * --- View Does NOT Exist ---
   < do whatever >
ENDIF
RELEASE ALL LIKE aryViews

Good Luck,
JRB-Bldr

 

[TamarGranor]

Check the INDBC() function:

IF INDBC("MyView", "View")
* It's there
ENDIF

Tamar

 

[Dan Freeman]

And another option. Stop trying to delete it.

Use In YourView && make sure it's closed
SET SAFETY OFF
CREATE SQL VIEW YourView AS Select....

Let it overwrite anything that's already there.

 

[Mike Lewis]

Mike, you say you want to delete the view so that you can recreate it "with different criteria".

If the "different criteria" is simply a different value in the WHERE clause, it might be better to use a view parameter.

For example, if the view is something like this:

SELECT * FROM Customers WHERE City = 'Paris'

and you want to change it to:

SELECT * FROM Customers WHERE City = 'London'

my suggestion is to use this instead:

lcCity = "London" && or whatever
SELECT * FROM Customers WHERE City = ?lcCity

That way, you won't have to change the view each time you change the criterion.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

Actually my question is, are you talking about vfp views or sql server views? If you generate where clauses, you can also generate sql and use sqlexec or the SelectCmd of a cursoradapter to set up the query dynamically without storing any view, neither vfp nor sql server.

Bye, Olaf.