Order By not working as Expected

[stanlyn]

Hi,

I am expecting 'All Clerks' to be at the top of the list, instead of at the bottom. It appears that records added after the select query is not under of the control of the "order by" clause.

Select ClerkName, SystemID 	;
	FROM 'rv_Clerk' ;
	ORDER By ClerkName ;
	INTO Cursor 'curClerk' ;
	readwrite

Select 'curClerk'
Insert Into 'curClerk' (ClerkName, SystemID) Values ('All Clerks', '*****')

So, how is this handled? VFPA

Thanks,
Stanley

 

[bborissov]

Nope,
ORDER BY works only for SQL result set.
It didn't works for the records you add after.
If you want ordered curosr with all inserted records you must index the resulted cursor.
Also you can use UNION and then ORDER BY but that will not work if you add more records after you get the result.

Borislav Borissov
VFP9 SP2, SQL Server

 

[Dan Olsson]

Well, you expect wrong The order remains the order from the Select. If you want it otherwise, you have to INDEX before the INSERT

 

[Chris Miller]

The simplest surely is to index the result cursor on Clerkname.

Let aside I wonder why you would query a view, which already is a query you could design to have that order by. It would have the advantage to use the remote database index for sorting. The way you first query the list unsorted and then order the result is not using an index. Not that it matters much with such a short list, but just in principle this all is backwards.

The reason why "All Clerks" end up at the end is easy to see, new records always go to the end. Just because you created the result with an order by, that's not making the resultset ordered forever, even if new records are inserted.

If you use that list for a listbox or combobox, you could also transfer the records with additem after you first add the "All Clerks", that could even be done in case you would have an Adair County Clerk that would sort before "All Clerks".

One thing you actually can do with such short lists is INSERT BLANK BEFORE:

Create Cursor demo (words c(5))
Insert Into demo Values ('world')
Insert Blank before
Replace words with 'hello'
Browse

In general, it's not a good idea as it means to rewrite the whole dbf with one further record.

Chriss

 

[stanlyn]

Hi Dan,

Well, you expect wrong The order remains the order from the Select. If you want it otherwise, you have to INDEX before the INSERT

Obviously... And that raises another question. If I change the values in the "order by" column, would they NOT respect the 'order by' in the select statement? Should the 'order by' in the query be seen as a 1-off, and only applies to the initial selection? Reading between the lines suggest that.

Thanks,
Stanley

 

[Chris Miller]

Should the 'order by' in the query be seen as a 1-off, and only applies to the initial selection?

Exactly that.

The order remains the order from the Select

That's misleading, if you interpret it as saying the column remembers its status as the order by column and that remains even when you add new data. No, it doesn't.

REsults of SQL are the sets of data in the order by the query and they are neither indexed nor do they have any inherited properties like the sort order or such. So adding data is appending it, just as normal, not sorting it in. Just think of the basic DBF structure, ou always add to the end of the file, and since the result has no index that's why new records always just go to the end.

Chriss

 

[stanlyn]

Hi Chris,

Let aside I wonder why you would query a view, which already is a query you could design to have that order by. It would have the advantage to use the remote database index for sorting. The way you first query the list unsorted and then order the result is not using an index. Not that it matters much with such a short list, but just in principle this all is backwards.

I'm coming to this with the dbf mindset where all the data is available. So far, I do not create any views via code, only with designer and with all rows and no order set and all fields update-able. The same as "use dbf in 0 shared" which presents all the data with no order set and readwrite. Many have suggested that remote views are good for starting out as they behave much the same as native dbfs. I'm also aware of the result set size and performance issues associates with RVs.

Doesn't make much sense to keep so many remote views around, one for this and one for that and have similar functionality. However, I have started experimenting with remote creation in code, on the fly when needed. The issue here is I lose drag & drop from the DE onto forms for textbox and grid creations. Currently the quickest way to propagate a backend schema change through everything is:
1. make the schema change
2. delete the RV
3. recreate a plain Jane RV with no order, no filter, all rows, all update-able and name it the same as the original.
4. open form
5. open de and delete RV
6. add RV back in
7. change controlsource of any controls that were affected where the schema was changed
8. save and done

I'm sure there is a better and simplier way and I'm all ears.

Before everyone starts screaming that cursoradapters and spt is far better and flexable, and probably are, in theory and practice. One issue, after hours and hours, I could not get cursoradapters to do what I needed them to do as there are just to many before* and after* stuff to deal with.

I have much better experience with SPT and that takes far too long as everything has to be hand-coded and as far from RAD as one can get.

What I need and would pay for is a hands on training with someone that knows this stuff on a real world project.

You have answered the questions I asked Dan, and thanks.

Insert Blank before

I've never seen this before, ever. I need to play with this one as well as your additem suggestion.

Thanks,
Stanley

 

[Steve Meyerson]

What I need and would pay for is a hands on training with someone that knows this stuff on a real world project.

Stanley,

I'm not your guy. But it reminds me of something I'd like to share anyway (FWIW)

My first PC was a Heathkit H89 (yes I'm that old) which I built (I had built several other Heathkit products). I didn't even know it needed an operating system (hence I don't criticize my clients for dumb questions).

Now I had to program something. I bought a course in Microsoft Basic which used a blackjack program as the teaching example (this REALLY attracted me - another subject).

ANYWAY, each line of the code was numbered and the explanation was keyed to these numbers. And the code was for an actual working application.

My point here is I wish some method like this was used nowadays so I dont get lost half the time.

Steve

 

[stanlyn]

Hi Steve,

Your about my age, 70 soon... And I too had a Heathkit weather station. My first programming was on a Model 2 Dandy Tandy with basic where we coded by numbers. My first little basic 20 line program hooked me as when I saw it do exactly as my logic said for it to do, that was my ah-ah moment and a chair pushback and instantly saw the possibilities, and I'm still doing it today. Next was SCO Unix programming with the bourne scripting language, then the c-shell.

Only problem is I cannot recall what I know very quickly, hence more scattered notes. All the way until about 58, I never wrote anything down and could remember a long list of items to pickup for weeks. Now a short piece of paper and pen is my friend.

The point of this is, enjoy your memory recall abilities, as they will probably be a passing memory. I hope you memory never goes away or gets diminished. It would be horrible for your loved ones to endure we not knowing them and the good and bad times we've had.

Enough rambling,
Stanley

 

[mjcmkrsr]

Hi Stanley,

Did you consider using a parameterized view?

Create SQL View, Delete View

These commands add and remove both local and remote views from a database. CREATE SQL VIEW is rather long-winded, but it must be, because CREATE VIEW is an old (useless) Xbase command.

Usage
CREATE SQL VIEW [ ViewAlias ] [ REMOTE ]
[ CONNECTION Connection [ SHARE ]
| CONNECTION DataSource ]
[ AS SQLSelect ]

Parameter
Value
Meaning

ViewAlias
Name
The name to assign the new view.

Omitted
If the AS clause is also omitted, open the View Designer for a new view. Otherwise, FoxPro prompts for a name for the new view.

Connection
Name
The name of an existing connection in the database that should be used to access remote data.

DataSource
Name
The name of an existing ODBC data source that should be used to access remote data.

SQLSelect
Included
Define the view to use the specified query.

Omitted (along with the AS keyword)
Open the View Designer for a new view.


This command has two modes. In one, it opens the View Designer so you can specify the view visually. In the other, it lets you define a view programmatically (and invisibly). There are so many other ways to open the View Designer, and CREATE SQL VIEW is such a long command (15 characters, counting embedded blanks) that we think its importance lies in programmatic use. Behind the scenes, you can specify either a local or remote view and have it added to the open database.

Omitting both the REMOTE keyword and the CONNECTION clause results in a local view. Including either gives you a remote view. You can specify CONNECTION without REMOTE and still keep it behind the scenes. If you specify REMOTE and don't indicate a connection, you'll be prompted to choose one.

In VFP 7, the connection information specified when you define a remote view can be overridden when you open the view; see Use for details.

SQLSelect can be any valid query. It doesn't get an INTO clause, though, because view data is put in a cursor automatically. Don't wrap the query in quotes. Strange as it seems to us, too, you explicitly issue the query as part of this command.

One warning on queries for remote views: Because they'll be interpreted by the server, make sure you use only standard SQL syntax and functions understood by the server, and not VFP's extensions or functions.

The View Designer actually does more than CREATE SQL VIEW. To specify the items on the Update Criteria tab of the View Designer, use DBSETPROP().

Don't confuse CREATE SQL VIEW with the lame Xbase CREATE VIEW, which attempts to save all current environmental settings to a VUE file.


Although CREATE VIEW FileName creates FileName.VUE, CREATE VIEW alone brings up the View Designer. So, don't fret about being confused over these two commands; it appears Microsoft is confused, too!


[highlight #FCE94F]One of the really cool things about Visual FoxPro is the parameterized view. A parameter is simply a variable that belongs to the view. You can use it in the WHERE clause of the query that defines the view—just precede it with "?" to make it a parameter. If the variable exists when you USE (or REQUERY()) the view, its current value is substituted. If the variable doesn't exist, the user is prompted (with a pretty decent-looking dialog) to supply a value.

Coolest of all, if you change the parameter's value and REQUERY(), the view is re-created with the records that now match the condition. Parameterized views let you write a query once, but use it for a wide range of conditions.[/highlight]


Parameterized views can't be accessed from the VFP OLE DB provider, new in VFP 7. If you try to open a parameterized view through the provider, you get a "SQL column parameter name not found" error. Creating an ADO Parameter object with the proper name and value doesn't help. The reason for this problem is that the way VFP handles parameters is incompatible with the way OLE DB handles them. The only workaround is to issue the SQL SELECT command that the view uses as a query to the provider.


Example
CREATE SQL VIEW EmployeesByBirthDate ;
AS SELECT * FROM TasTrade!Employee ;
WHERE Birth_Date >= ?Birthdate

The example creates a parameterized view called EmployeesByBirthDate that chooses employees from TasTrade's employee table who were born on or after a specified date. When you open the view with USE EmployeesByBirthDate, if the variable Birthdate exists, its current value is substituted into the query. If it doesn't exist, a dialog appears, prompting for a value.


[highlight #FCE94F] This one's really cool. When you use a parameterized view as the source for a control in a form (say, a grid or list), the parameter can be something like "ThisForm.SomeProperty." You can set up the view just as you want it on the form and not have to use a separate variable for the parameter. When you open the view outside the form, you get prompted for the parameter just as you do in any other case. This is the only place we know of in Visual FoxPro where you can use ThisForm notation in something other than method code or a property definition. Here's an example, letting you filter Employee on country.
[/highlight]



CREATE SQL VIEW EmpsByCountry AS ;
SELECT First_Name,Last_Name FROM TasTrade!Employee ;
WHERE Country = ?ThisForm.Country
In the form that displays this view, you'd create a Country property and, perhaps, have a combo box with ThisForm.Country for a ControlSource. The combo's InteractiveChange method could REQUERY() the view and Refresh the form.

In VFP 7, the CREATE SQL VIEW command fires the database's BeforeCreateView and AfterCreateView events if database events are turned on.

Usage
DELETE VIEW [ ViewName ]

This command is just what it sounds like. It removes an existing view from a database. If database events are turned on, the BeforeDropView and AfterDropView events are fired.


Although Help says otherwise, you can omit ViewName and you're prompted with a list of views in the current database. Help also states that DELETE VIEW requires exclusive access to the database, but that's not true.

hth

MarK

 

[mjcmkrsr]

and some demo code:

A database is created with a table and records are added. Then the parameterized view is created. A timer adds records to the table in defined time laps and the view is REQUERYed. The first record is shown after 3 seconds - so don't panic! You may raise the values of the two spinners and see the effect.

Enjoy

PUBLIC go_Form 

go_Form = CreateObject("frmForm")
go_Form.Visible = .T.
go_Form.Show

READ Events

CLOSE ALL
CLEAR ALL

*****

DEFINE CLASS frmForm As Form

	iInterval = 60
	
	Width = 570
	Height = 360
	MinWidth = This.Width
	MaxWidth = This.Width
	MinHeight = This.Height
	AutoCenter = .T.
	Themes = .F.
	
	ADD OBJECT lblTimer as Label WITH ;
		Top = 18, ;
		Left = 18, ;
		Autosize = .T., ;
		Caption = "Timer Interval: "
		
	ADD OBJECT spnTimerInterval as Spinner WITH ;
		Value = 3, ;
		KeyBoardLowValue = 0, ;
		SpinnerLowValue = 0, ;
		Increment = 3, ;
		Left = 18 + 84 + 6, ;
		Top = 18, ;
		Width = 60
		
		PROCEDURE spnTimerInterval.InterActiveChange()
			ThisForm.timRequery.Interval = This.Value * 1000
			
		ENDPROC

	ADD OBJECT lblSeconds as Label WITH ;
		Top = 18, ;
		Left = 18 + 84 + 6 + 60 + 6, ;
		Autosize = .T., ;
		Caption = "Seconds between first and last record: "

	ADD OBJECT spnInterval as Spinner WITH ;
		Value = 60, ;
		KeyBoardLowValue = 60, ;
		SpinnerLowValue = 60, ;
		Increment = 6, ;
		Left = 18 + 84 + 6 + 60 + 6 + 210 + 6, ;
		Top = 18, ;
		Width = 60
		
		PROCEDURE spnInterval.InterActiveChange()
			ThisForm.iInterval = This.Value
			
		ENDPROC

*!*		ADD OBJECT cmdFit as CommandButton WITH ;
*!*			Top = 18, ;
*!*			Left = 18 + 84 + 6 + ThisForm.spnTimerInterval.Width + 6 + 210 + 6 + ThisForm.spnInterval.Width + 6, ;
*!*			Height = 24, ;
*!*			Caption = "Autofit"
*!*			
*!*			PROCEDURE cmdFit.Click()
*!*				FOR i = 1 to ThisForm.grdATM.ColumnCount
*!*					WITH ThisForm.grdATM
*!*						.Columns(i).Autofit
*!*						.Columns(i).Width = .Columns(i).Width + 12
*!*					ENDWITH 
*!*				ENDFOR

*!*			ENDPROC  
		
	ADD OBJECT timRequery as Timer WITH ;
		Interval = 3000
		
[highlight #8AE234]		PROCEDURE timRequery.Timer()
			INSERT INTO tblATM (cName, tDateTime, cDateTime) VALUES ("N" + SYS(2015), DATETIME(), CHRTRAN(TTOC(DATETIME(), 3),"-:", "") )
			REQUERY("vueATM")
			ThisForm.Refresh()
[/highlight]
		ENDPROC 
	
	Add Object grdATM as Grid with;
		Top = 66, ;
		Left = 18, ;
		Height = ThisForm.Height - 96, ;
		Width = ThisForm.Width - 36, ;
		RowHeight = 24, ;
		AllowRowSizing = .F., ;
		HeaderHeight = 21, ;
		AllowHeaderSizing = .F., ;
		DeleteMark = .F., ;
		Anchor = 15, ;
		Visible = .T., ;
		ColumnCount = -1, ;
		RecordSource = "vueATM"
    	
		PROCEDURE grdATM.Init()
			WITH This 
				.Column1.Header1.Caption = "Auto"
				.Column1.Header1.FontBold = .T.
				.Column1.Width = 60

				.Column2.Header1.Caption = "Name"
				.Column2.Header1.FontBold = .T.
				.Column2.Width = 120

				.Column3.Header1.Caption = "Date and Time"
				.Column3.Header1.FontBold = .T.
				.Column3.Width = 150

				.Column4.Header1.Caption = "C-DateTime"
				.Column4.Header1.FontBold = .T.
				.Column4.Width = 150

			ENDWITH 
		ENDPROC
		 
	PROCEDURE Load()	

	[highlight #8AE234]	IF FILE("ATM.DBC")
			OPEN DATABASE ATM

		ELSE 
			CREATE DATABASE "ATM"
			
			CREATE TABLE "tblATM" (iAutoInc i AUTOINC NEXTVALUE 1, cName C(20), tDateTime t, cDateTime C(20) )

			FOR i = 1 TO 1000
				INSERT INTO tblATM (cName, tDateTime, cDateTime) VALUES ("N" + SYS(2015), DATETIME(), CHRTRAN(TTOC(DATETIME(), 3),"-:", "") )
			ENDFOR 
			
			CREATE SQL VIEW vueATM AS ;
				Select * from tblATM where DATETIME() - tDateTime < ?(ThisForm.iInterval) Order by iAutoInc DESC 

		ENDIF

		IF USED("vueATM")
			SELECT vueATM
		ELSE 
			USE vueATM IN 0
		ENDIF 
	ENDPROC[/highlight] 

	PROCEDURE Destroy()
		CLEAR Events
		ThisForm.Release
	ENDPROC
ENDDEFINE

*****

hth

MarK

 

[Steve Meyerson]

Your about my age, 70 soon...

I'm way ahead of ya (84 next month ). I didn't get started til I retired from US Air Force.

I've done some pretty big projects, but I might need another hundred years to reach the knowledge level of people like Mike, Chris, Tamar & a few others (thanks "guys" for sharing).

Steve

 

[stanlyn]

Thanks Mark,

Thanks for the article...

How is it made updateable all from within a prg that creates the view?

I tried copying the code as shown in the "view sql" part of the view into a .prg using a text/endtext block and that fails at the 1st "ThisView" in the DBSetProp line. I tried all the variations I could think of and all failed. I was using this as the starting point to keep from typing the long sql statement the designer built.

I would assume that every updatable field needs to be defined as updatable with its own DBSetProp setting.

How do I address the naming of the "ThisView" object in a consistent way for usage in a program, form or report?

What is best practice on placement (where it lives)? I know this may be a "depends" question. So, lets pretend it a native table and how that would be called in a .prg, form or report.

1. Within a control's method,
2. a form custom method
3. a procedure in the masterprocs .prg
4. its own .prg
5. Is there any others, yes... bad, bad global var.

Thanks,
Stanley

TEXT to myvar NOSHOW FLAGS 1 PRETEXT 15
	SELECT Clerk.Pk, Clerk.SystemId FROM dbo.Clerk Clerk;
		ORDER BY Clerk.ClerkName
	DBSetProp(ThisView,"View","SendUpdates",.T.)
	DBSetProp(ThisView,"View","BatchUpdateCount",1)
	DBSetProp(ThisView,"View","CompareMemo",.T.)
	DBSetProp(ThisView,"View","FetchAsNeeded",.F.)
	DBSetProp(ThisView,"View","FetchMemo",.T.)
	DBSetProp(ThisView,"View","FetchSize",100)
	DBSetProp(ThisView,"View","MaxRecords",-1)
	DBSetProp(ThisView,"View","Prepared",.F.)
	DBSetProp(ThisView,"View","ShareConnection",.T.)
	DBSetProp(ThisView,"View","AllowSimultaneousFetch",.F.)
	DBSetProp(ThisView,"View","UpdateType",1)
	DBSetProp(ThisView,"View","UseMemoSize",255)
	DBSetProp(ThisView,"View","Tables","dbo.Clerk")
	DBSetProp(ThisView,"View","WhereType",3)

	DBSetProp(ThisView+".pk","Field","DataType","I")
	DBSetProp(ThisView+".pk","Field","UpdateName","dbo.Clerk.Pk")
	DBSetProp(ThisView+".pk","Field","KeyField",.T.)
	DBSetProp(ThisView+".pk","Field","Updatable",.F.)
ENDTEXT

Create Sql View 'rv_ViewAlias'  Remote ;
	CONNECTION eFileIt Share ;
	As ;
	(myvar)

 

[stanlyn]

Hi Steve,

I'm way ahead of ya (84 next month

Congratulations... And very, very encouraging, and maybe, just maybe we can close the knowledge gap that separates us (at least me) from those you mention and many others in the next hundred years.

Now the big question... Can VFP last, or will M$'s next OS kill it. ?

Thanks Steve,
Stanley

 

[mjcmkrsr]

Hi Stan

Below the update code of a local code I use. The name of the database is "Wunnraum", the tables have the following names: "Houses", "Students", "Stagiaires". Maybe this code snippet can give some hints - you have of course to adapt it to your needs.

DBSetProp(ThisView,"View","SendUpdates",.F.)
DBSetProp(ThisView,"View","BatchUpdateCount",1)
DBSetProp(ThisView,"View","CompareMemo",.T.)
DBSetProp(ThisView,"View","FetchAsNeeded",.F.)
DBSetProp(ThisView,"View","FetchMemo",.T.)
DBSetProp(ThisView,"View","FetchSize",100)
DBSetProp(ThisView,"View","MaxRecords",-1)
DBSetProp(ThisView,"View","Prepared",.F.)
DBSetProp(ThisView,"View","UpdateType",1)
DBSetProp(ThisView,"View","UseMemoSize",255)
DBSetProp(ThisView,"View","Tables","wunnraum!stagiaires,wunnraum!houses")
DBSetProp(ThisView,"View","WhereType",3)

DBSetProp(ThisView+".hcode","Field","DataType","C(7)")
DBSetProp(ThisView+".hcode","Field","UpdateName","wunnraum!houses.hcode")
DBSetProp(ThisView+".hcode","Field","KeyField",.F.)
DBSetProp(ThisView+".hcode","Field","Updatable",.F.)

DBSetProp(ThisView+".name","Field","DataType","C(25)")
DBSetProp(ThisView+".name","Field","UpdateName","wunnraum!houses.name")
DBSetProp(ThisView+".name","Field","KeyField",.F.)
DBSetProp(ThisView+".name","Field","Updatable",.F.)

DBSetProp(ThisView+".room","Field","DataType","C(3)")
DBSetProp(ThisView+".room","Field","UpdateName","wunnraum!houses.room")
DBSetProp(ThisView+".room","Field","KeyField",.F.)
DBSetProp(ThisView+".room","Field","Updatable",.F.)

DBSetProp(ThisView+".dentry","Field","DataType","D")
DBSetProp(ThisView+".dentry","Field","UpdateName","wunnraum!students.dentry")
DBSetProp(ThisView+".dentry","Field","KeyField",.F.)
DBSetProp(ThisView+".dentry","Field","Updatable",.F.)

DBSetProp(ThisView+".dexit","Field","DataType","D")
DBSetProp(ThisView+".dexit","Field","UpdateName","wunnraum!students.dexit")
DBSetProp(ThisView+".dexit","Field","KeyField",.F.)
DBSetProp(ThisView+".dexit","Field","Updatable",.F.)

DBSetProp(ThisView+".cname","Field","DataType","C(40)")
DBSetProp(ThisView+".cname","Field","UpdateName","wunnraum!stagiaires.cname")
DBSetProp(ThisView+".cname","Field","KeyField",.F.)
DBSetProp(ThisView+".cname","Field","Updatable",.T.)

DBSetProp(ThisView+".rent","Field","DataType","N(5)")
DBSetProp(ThisView+".rent","Field","UpdateName","wunnraum!houses.rent")
DBSetProp(ThisView+".rent","Field","KeyField",.F.)
DBSetProp(ThisView+".rent","Field","Updatable",.F.)

DBSetProp(ThisView+".pkey","Field","DataType","C(14)")
DBSetProp(ThisView+".pkey","Field","UpdateName","wunnraum!houses.pkey")
DBSetProp(ThisView+".pkey","Field","KeyField",.F.)
DBSetProp(ThisView+".pkey","Field","Updatable",.F.)

DBSetProp(ThisView+".minfo","Field","DataType","M")
DBSetProp(ThisView+".minfo","Field","UpdateName","wunnraum!houses.minfo")
DBSetProp(ThisView+".minfo","Field","KeyField",.F.)
DBSetProp(ThisView+".minfo","Field","Updatable",.T.)

hth

MarK

 

[stanlyn]

Thanks Mark,

OK, I see how you address the field objects EXCEPT for the very first line after my select statement. Look at the 1st line in your snippet. You are using the same as mine that is erroring on that first line and never get to the field items below.

I'm trying to make the RV updatable via code and errors on 1st line after select.

The error state my select is invalid, but it works if all the DBSetProps lines are removed. How is the "ThisView" addressed (objectwise)?

What would change in my text/endtext rendition above to make it work?

DBSetProp(ThisView,"View","SendUpdates",.F.)

Is there a benefit of defining the RVs in separate .prgs or proceduers as opposed to in the remote view section of the vfp database? I have had bad experiences relying on the rv at the database level expecially after making a lot of backend changes. Cannot access and ? I would never lose access to the .prg no matter I changed the backend. Creating a clone for modifications appears to be easier when as .prg.

Your thoughts,
Stanley

 

[Steve Meyerson]

Now the big question... Can VFP last, or will M$'s next OS kill it. ?

I may be older but I'm no prophet (some of my predictions=.T., most .F.)

However, one thing of which I'm pretty certain is that M$ will make that decision based purely on the better result for their stockholders. $$$. Ever hear of "Capitalism"? {meant Only rhetorical].

Steve

 

[mjcmkrsr]

Hi Stan,

You have to replace "ThisView" with the name of the view and please don't forget to update the underlying table (see also the help file)

CREATE SQL VIEW emp_cust_view AS ;
SELECT employee.emp_id, ;
employee.phone, customer.cust_id, ;
customer.emp_id, customer.contact, ;
customer.company ;
FROM employee, customer ;
WHERE employee.emp_id = customer.emp_id

*!* Create a view that accesses fields from two tables.

DBSETPROP('emp_cust_view', 'View', 'Tables', 'employee, customer')

*!* Set the tables to be updated.

DBSETPROP('emp_cust_view.emp_id', 'Field', 'UpdateName', 'employee.emp_id')
DBSETPROP('emp_cust_view.phone', 'Field', 'UpdateName', 'employee.phone')
DBSETPROP('emp_cust_view.cust_id', 'Field', 'UpdateName', 'customer.cust_id')
DBSETPROP('emp_cust_view.emp_id1', 'Field', 'UpdateName', 'customer.emp_id')
DBSETPROP('emp_cust_view.contact', 'Field', 'UpdateName', 'customer.contact')
DBSETPROP('emp_cust_view.company', 'Field', 'UpdateName', 'customer.company')

** Set update names.

DBSETPROP('emp_cust_view.emp_id', 'Field', 'KeyField', .T.)

*!* Set a single-field unique key for the Employee table.

DBSETPROP('emp_cust_view.cust_id', 'Field', 'KeyField', .T.)
DBSETPROP('emp_cust_view.emp_id1', 'Field', 'KeyField', .T.)

*!* Set a two-field unique key for the Customer table.

DBSETPROP('emp_cust_view.phone', 'Field', 'Updatable', .T.)
DBSETPROP('emp_cust_view.contact', 'Field', ; 'Updatable', .T.)
DBSETPROP('emp_cust_view.company', 'Field', ; 'Updatable', .T.)

*!* Set the updatable fields. Typically, key fields are not updatable.

DBSETPROP('emp_cust_view', 'View', 'SendUpdates', .T.)

*!* Activate the update functionality.

GO TOP
REPLACE employee.phone WITH "(206)111-2222"
REPLACE customer.contact WITH "John Doe"

*!* Modify data in the view.

TABLEUPDATE()

*!* Commit the changes by updating both the Employee and Customer base tables.

hth

MarK

 

[mjcmkrsr]

...

and some demo code

PUBLIC go_Form 

go_Form = CreateObject("frmForm")
go_Form.Visible = .T.
go_Form.Show

READ Events

CLOSE ALL
CLEAR ALL

*****

DEFINE CLASS frmForm As Form

	Width = 570
	Height = 360
	MinWidth = This.Width
	MinHeight = This.Height
	AutoCenter = .T.
	Themes = .F.
	
	ADD OBJECT lblTable as Label WITH ;
		Left = 18, Top = 18, Caption = "Data in Table", FontItalic = .T., FontBold  = .T.
		
	ADD OBJECT lblName as Label WITH ;
		Left = 126, Top = 18, Caption = "Enter name :"
		
	ADD OBJECT txtName as TextBox WITH ;
		Left = 210, Top = 15, Value = ""
		
	ADD OBJECT cmdRequery as CommandButton WITH ;
		Left = 330, Top = 15, Height = 24, Caption = "Requery view"

		PROCEDURE cmdRequery.Click()
			IF EMPTY(ALLTRIM(ThisForm.txtName.Value))
				= MESSAGEBOX("Please enter name", 48, "Names")

			ELSE
				REQUERY("vueNames")
			
			ENDIF
			
			ThisForm.Refresh()

		ENDPROC 

	ADD OBJECT cmdSave as CommandButton WITH ;
		Left = 450, Top = 15, Height = 24, Caption = "Save to table"
		
		PROCEDURE cmdSave.Click()
			= TABLEUPDATE(0, .T., "vueNames")
			
		ENDPROC 

	ADD OBJECT lblView as Label WITH ;
		Left = 18, Top = 180, Caption = "Data in View", FontItalic = .T., FontBold  = .T., Anchor = 90

	Add Object grdATM as Grid WITH ;
		ReadOnly = .T., ;
		Top = 48, ;
		Left = 18, ;
		Height = 120, ;
		Width = ThisForm.Width - 36, ;
		RowHeight = 24, ;
		AllowRowSizing = .F., ;
		HeaderHeight = 21, ;
		AllowHeaderSizing = .F., ;
		DeleteMark = .F., ;
		Anchor = 75, ;
		Visible = .T., ;
		ColumnCount = -1, ;
		RecordSource = "tblNames"
    	
		PROCEDURE grdATM.Init()
			WITH This 
				.Column1.Header1.Caption = "Auto"
				.Column1.Header1.FontBold = .T.
				.Column1.Width = 60

				.Column2.Header1.Caption = "Name"
				.Column2.Header1.FontBold = .T.
				.Column2.Width = 120

				.Column3.Header1.Caption = "Street"
				.Column3.Header1.FontBold = .T.
				.Column3.Width = 150

				.Column4.Header1.Caption = "City"
				.Column4.Header1.FontBold = .T.
				.Column4.Width = 150

				.Column5.Header1.Caption = "ZipCode"
				.Column5.Header1.FontBold = .T.
				.Column5.Width = 150

			ENDWITH 
		ENDPROC
		
	Add Object grdATMVUE as Grid with;
		Top = 204, ;
		Left = 18, ;
		Height = 120, ;
		Width = ThisForm.Width - 36, ;
		RowHeight = 24, ;
		AllowRowSizing = .F., ;
		HeaderHeight = 21, ;
		AllowHeaderSizing = .F., ;
		DeleteMark = .F., ;
		Anchor = 30, ;
		Visible = .T., ;
		ColumnCount = -1, ;
		RecordSource = "vueNames"
		
		PROCEDURE grdATMVUE.Init()
			WITH This 
				.Column1.Header1.Caption = "Auto"
				.Column1.Header1.FontBold = .T.
				.Column1.Width = 60
				.Column1.ReadOnly = .T.

				.Column2.Header1.Caption = "Name"
				.Column2.Header1.FontBold = .T.
				.Column2.Width = 120

				.Column3.Header1.Caption = "Street"
				.Column3.Header1.FontBold = .T.
				.Column3.Width = 150

				.Column4.Header1.Caption = "City"
				.Column4.Header1.FontBold = .T.
				.Column4.Width = 150

				.Column5.Header1.Caption = "ZipCode"
				.Column5.Header1.FontBold = .T.
				.Column5.Width = 150

			ENDWITH 
		ENDPROC

	PROCEDURE Load()	

		IF FILE("ATMOS.DBC")
			OPEN DATABASE ATMOS
			
		ELSE 
			CREATE DATABASE "ATMOS"
			
			CREATE TABLE "tblNames" (iAutoInc i AUTOINC NEXTVALUE 100, cName C(20), cStreet C(20), cCity C(20), cZipCode C(20))

				INSERT INTO tblNames (cName, cStreet, cCity, cZipCode) VALUES ("Winston Smith","123 Anywhere St","Chicago","60601")
				INSERT INTO tblNames (cName, cStreet, cCity, cZipCode) VALUES ("Andrea Lopez","235 N Ave","Lawrence","08648")
				INSERT INTO tblNames (cName, cStreet, cCity, cZipCode) VALUES ("Sandra Johnson","2500 8th St","Los Angeles","90061")
				INSERT INTO tblNames (cName, cStreet, cCity, cZipCode) VALUES ("Kate Wilson","987 M Blvd","Tarzana","91356")
				INSERT INTO tblNames (cName, cStreet, cCity, cZipCode) VALUES ("Kate Smith","87 Santa M Blvd","Santa Barbara","91358")
				INSERT INTO tblNames (cName, cStreet, cCity, cZipCode) VALUES ("Jim Monterrey","8715 Santa Monica","Santa Barbara","91358")
				INSERT INTO tblNames (cName, cStreet, cCity, cZipCode) VALUES ("Winston Smith","123 Anywhere St","Eau Claire","20601")
				INSERT INTO tblNames (cName, cStreet, cCity, cZipCode) VALUES ("Andrea Lopez","235 N Ave","Toronto","28648")
				INSERT INTO tblNames (cName, cStreet, cCity, cZipCode) VALUES ("Sandra Johnson","2500 8th St","Los Alamos","20061")
				INSERT INTO tblNames (cName, cStreet, cCity, cZipCode) VALUES ("Kate Wilson","987 M Blvd","Carlson","21356")
				INSERT INTO tblNames (cName, cStreet, cCity, cZipCode) VALUES ("Kate Smith","87 Santa M Blvd","Santa Ines","21358")
				INSERT INTO tblNames (cName, cStreet, cCity, cZipCode) VALUES ("Jim Monterrey","8715 Santa Monica","Santa Ines","21358")
				
				LOCATE 
				
			CREATE SQL VIEW vueNames AS ;
				Select * from tblNames where cName = ?ALLTRIM(ThisForm.txtName.Value) 
				

				DBSETPROP('vueNames', 'View', 'Tables', 'tblNames')

*!* Set the tables to be updated.

				DBSETPROP('vueNames.iAutoInc', 'Field', 'UpdateName', 'tblNames.iAutoInc')
				DBSETPROP('vueNames.cName', 'Field', 'UpdateName', 'tblNames.cName')
				DBSETPROP('vueNames.cStreet', 'Field', 'UpdateName', 'tblNames.cStreet')
				DBSETPROP('vueNames.cCity', 'Field', 'UpdateName', 'tblNames.cCity')
				DBSETPROP('vueNames.cZipCode', 'Field', 'UpdateName', 'tblNames.cZipCode')

** Set update names.

				DBSETPROP('vueNames.iAutoInc', 'Field', 'KeyField', .T.)

*!* Set a single-field unique key for the tblNames table.


				DBSETPROP('vueNames.cName', 'Field', 'Updatable', .T.)
				DBSETPROP('vueNames.cStreet', 'Field', 'Updatable', .T.)
				DBSETPROP('vueNames.cCity', 'Field', 'Updatable', .T.)
				DBSETPROP('vueNames.cZipCode', 'Field', 'Updatable', .T.)

*!* Set the updatable fields. Typically, key fields are not updatable.

				DBSETPROP('vueNames', 'View', 'SendUpdates', .T.)

*!* Activate the update functionality.

				
		ENDIF

		IF USED("tblNames")
			SELECT tblNames
		ELSE 
			USE tblNames IN 0
		ENDIF 

		IF USED("vueNames")
			SELECT vueNames
		ELSE 
			USE vueNames IN 0
		ENDIF 

	ENDPROC 

	PROCEDURE Destroy()
		CLEAR Events
		ThisForm.Release
	ENDPROC
ENDDEFINE

*****

hth

MarK

 

[Chris Miller]

The code the view designer shows is pseudocode. It doesn't work. And it's not even worth explaining what's wrong with it and how to make it work outside of the view designer stand alone.

If you want to see how to build the views with code use HOME()+"Tools\Gendbc\Gendbc.prg". For example run it on the northwind DBC, which also contains some views.

Chriss