Wiki Home

Vfp Views 101


Namespace: VFP
* mkVV1dbc.prg
* makes the VfpViews101 dbc

* Get access to some data
OPEN DATABASE (HOME(2)+"tastrade\data\tastrade")
* home(2) = "C:\PROGRAM FILES\MICROSOFT VISUAL STUDIO\MSDN98\98VS\1033\SAMPLES\VFP98\"

* Make a new database for the view example 
* it will read/write the sample data
CREATE DATABASE vfpviews101

* Define the view 
* both the SQL-SELECT query to get data, 
* and the set it to be updateable.
CREATE SQL VIEW "v_Cust_Some" ;
   AS SELECT * ;
   FROM tastrade!customer ;
   WHERE Customer.company_name = ?vp_cComp_Name ;
   ORDER BY Customer.company_name

DBSetProp('V_CUST_SOME', 'View', 'SendUpdates', .T.)
DBSetProp('V_CUST_SOME.customer_id', 'Field', 'KeyField', .T.)

CLOSE DATABASES



Once the view has been created, it will live forever in the dbc.

The app that uses the view looks like this:

* program appvv1.prg

OPEN DATABASE (HOME(2)+"tastrade\data\tastrade")
OPEN DATABASE vfpviews101

* Open the view, but don't ask for any data yet 
* We don't know what the user wants yet
USE v_Cust_Some nodata

DO WHILE .t.

	* Keep asking the user what they want
	* (the letter A is a good place to start)
	accept "Customers like " to lcComp
	IF EMPTY( lcComp )
		exit
	endif
	vp_cComp_Name = lcComp
	
	? lcComp
	
	* run the query
	* it will use the parameter supplied by the user
	REQUERY( 'v_Cust_Some' )	
	
	* show the user
	* allow them to edit
	SELECT v_Cust_Some
	BROWSE
	
	* commit any updates (not sure if this is needed - browse might do it automagicaly)
	TABLEUPDATE( .t., .t., 'v_Cust_Some' )
	
enddo

USE IN v_Cust_Some

Trying to demo how the various 'WhereType' settings work, but my example falls apart. could be because it doesnt work 'as hoped' on the same instance of VFP, or I could be missing something.

clear
Close Tables all
Close Databases all
Delete Database batdbc deletetables

Create Database batdbc
Create Table batdbf (;
	pk_BatDbf i primary key, ;
	cFid1 c(10), ;
	cFid2 c(10) )
	
Create sql View v_BatDbf as ;
	select * from batdbf ;
	where pk_BatDbf = ?tnX
	
DBSetProp("V_BATDBF", 'View', 'SendUpdates', .T. )

DBSetProp("V_BATDBF.pk_BatDbf",'Field','KeyField',.T.)
DBSetProp("V_BATDBF.pk_BatDbf",'Field','Updatable',.F.)

DBSetProp("V_BATDBF.cfid1",'Field','Updatable',.F.)

DBSetProp("V_BATDBF.cfid2",'Field','Updatable',.T.)
DBSetProp("V_BATDBF.cfid2",'Field','UpdateName',[batdbf.cfid2])


f(1)
f(2)
f(3)
f(4)
Return

Function f( tnX )

? "f("+Transform( tnX )+")"
* Load a rec into the base table
Insert into BatDbf ( pk_BatDbf, cFid1, cFid2 ) values ( tnX, "Bat", "Mobile" )
* Set the view's WhereType
DBSetProp("V_BATDBF", 'View', 'WhereType', tnX )

* get the new rec into the view (based on current tnX)
USE v_BatDbf

* Update the base table
Update batdbf where pk_BatDbf = tnX set cFid1 = "Carls"

* update the view:
Select v_BatDbf
replace cFid2 with "Ranchero" 
llTU = TableUpdate(.f.,.f.,"v_BatDbf")
? "TableUpdate():", llTU
If llTU
	?? "update successful"
Else 
	?? "update failed"
	AError( laEr )
	? laEr[2]
	TableRevert()
EndIf
USE in v_BatDbf

* display the record in the base table
Select BatDbf
Locate for pk_BatDbf = tnX 
? cFid1, cFid2
?

Return 

See also View Parameters Indexed Views
Contributors Carl Karsten
Category Code Samples
( Topic last updated: 2004.06.09 06:42:06 AM )