Clear All
csql = "SELECT EmployeeID, LastName, FirstName," + ;
"Title, TitleOfCourtesy, BirthDate," + ;
"Hiredate, Address, City, Region," + ;
"PostalCode, Country, HomePhone," + ;
"Extension, Photo, Notes," + ;
"ReportsTo, PhotoPath FROM dbo.Employees"
lnHandle=Sqlstringconnect('DRIVER=SQL Server;'+;
'SERVER=(local);DATABASE=Northwind;Trusted_Connection=Yes')
SQLExec(lnHandle,csql,'v_emp') && v_emp has data now but not updatable
* You can browse and change data but it wouldn't update
* at backend unless you prepare it to be updatable
* Make v_emp updatable
CursorSetProp('KeyFieldList','Employeeid','v_emp')
CursorSetProp('WhereType',1,'v_emp')
CursorSetProp('Tables','employees','v_emp')
* Specify fields' mapping
* Mapping is in pairs
* On left there is local field name and on right
* tablename.Fieldname on backend.
* employeeID employees.employeeid
* employeeID is local field name mapping to
* employees.employeeid on backend
* In other words if set to be updatable
* v_emp.employeeID would update employees.employeeID at backend
* Note: Include key field in this list even though you might not
* want it to be updatable
TEXT to m.lcUpdateNameList noshow
employeeID employees.employeeid,
Lastname employees.Lastname,
Firstname employees.FirstName,
Title employees.Title,
TitleOfCourtesy employees.TitleOfCourtesy,
BirthDate employees.BirthDate,
Hiredate employees.Hiredate,
Address employees.Address,
City employees.City,
Region employees.Region,
PostalCode employees.PostalCode,
Extension employees.Extension,
Notes employees.Notes,
ReportsTo employees.ReportsTo,
PhotoPath employees.PhotoPath
ENDTEXT
CursorSetProp("UpdateNameList", ;
Chrtran(m.lcUpdateNameList,Chr(13)+Chr(10),''),'V_emp')
* Specify fields that could be updated
CursorSetProp('UpdatableFieldList',;
"LastName, FirstName," + ;
"Title, TitleOfCourtesy, BirthDate," + ;
"Hiredate, Address, City, Region," + ;
"PostalCode, Country, HomePhone," + ;
"Extension, Notes," + ;
"ReportsTo, PhotoPath",'v_emp')
CursorSetProp('SendUpdates',.T.,'v_emp')
* We want all updates to occur in a batch
* so setting to table level buffering
CursorSetProp('Buffering',5,'v_emp')
Browse Title 'Editable fields are all except Id and Photo'
* Update backend with changes
If !Tableupdate(2,.T.,'v_emp')
Aerror(arrWhy)
Display Memo Like arrWhy
EndIf
* Requery data from backend to see if it has been really updated
SQLExec(lnHandle,'select * from dbo.employees','afterupdate')
SQLDisconnect(lnHandle)
Select afterupdate
Browse