Add data to a source table using a view

[sigcon]

I have a contact table and a company table in the database. Each table has a primary index of type integer. The contact table has a foreign key to the company table. I've created a view that inner joins both tables. I've created a data maintenance form that contains both company and contact data from the view. When the user types a new company name I want to add a new row to the company source table. I can't figure out how to set the view properties or how to update the view so the source table gets updated correctly.

Any suggestions are appreciated.

 

[TomasDill]

Hi!

I will try to explain the solution step by step.

1. Get your view to be updatable for changes. You require to define key fields for both tables.

2. Get your views to be updatable for new records. After update you should have a way to refresh key fields by new auto-generated values in view.

3. Replace the foreighn key and update again.

For 2. - its depended on how you generate your uniquie IDs for tables. If you used the way described here in my FAQ article, you will require to use APPEND BLANK command to insert new record into view correctly. Than use replace command for foreign key and update again.

If you do not generate new key value in the view, but in table only, you require another approach, more complex.

The approach is much similar to which is used with SQL Server. With SQL Server you can get value of the last ID key generated by last INSERT command. Here you can do the same: In the routine that is used to generate new ID key value store ID key in the public variable. (When you use view with 2 joined tables - store values of new ID keys in two separate veriables, each for appropriate table).

Than, updating of new records in view will look like following:

* check if recorn in view is new
llNewRec = '3' $ GetFLDState(-1) OR '4' $ GetFLDState(-1)
tableupdate(...) && first update will
&&insert new records in table calling key generation routine(s)
&& let assume new keys placed into gnTable1ID and gnTable2ID variables
if llNewRec
replace Table1ID with gnTable1ID, Table2ID with gnTable2ID, Table2ForeignKey with gnTable1ID
tableupdate(...)
&& above command do not generate any update for key fields when they
&& are not marked as updatable. These keys required just to mark
&& record as it is not modified and provide information about
&& ID keys to match them with ID keys generated in table
&& to perform second update.
endif



Vlad Grynchyshyn
vgryn@softserve.lviv.ua

http://www.softserve.lviv.ua

The professional level of programmer could be determined by level of stupidity of his/her bugs

 

[sigcon]

Thanks for the reply. I'm not sure what you mean by "Get your views to be updatable for new records". I have been trying to resolve this problem for days. I found a way to make it work but I don't think it's the best way. I also don't think it will perform well on large tables since I have to create a separate view for each table I want to add rows to, use that view to add a row, requery() the main table after the tableupdate(), change the foriegn key in the main view, and finally requery() the main view again to get the add row data.

Does anyone know a better way?

Does anyone know why I get the "No updatable tables found..." error message (see code below)?

Here's the sample program I created. You can cut an paste it into a .prg and run it.

* Sample.Prg
* A sample application to discuss adding records to tables
* using views. Developed in VFP 6.0

* Create a database with two tables
* Add data to the tables
* Create two views
* Open the database and use the view
* Change the company for one of the contacts to a new company
* Update the base tables

* Cleanup from last run
Close DATABASES ALL
Erase SAMPLE.bak
Erase SAMPLE.DBC
Erase SAMPLE.dct
Erase SAMPLE.dcx
Erase Company.DBF
Erase Company.CDX
Erase Contact.DBF
Erase Contact.CDX

* Create company
Create TABLE Company ;
(CompId i, ;
Company c(30))
Insert INTO Company ;
(CompId, Company) ;
VALUES (1, "Company1&quot
Insert INTO Company ;
(CompId, Company) ;
VALUES (2, "Company2&quot
Insert INTO Company ;
(CompId, Company) ;
VALUES (3, "Company3&quot
Index ON CompId TAG CompId CANDIDATE

* Create contact
Create TABLE Contact ;
(ContId i, ;
CompId i, ;
Contname c(30))
Insert INTO Contact ;
(ContId, CompId, Contname) ;
VALUES (1, 1, "Joe Contact1&quot
Insert INTO Contact ;
(ContId, CompId, Contname) ;
VALUES (2, 1, "Afred Contact2&quot
Insert INTO Contact ;
(ContId, CompId, Contname) ;
VALUES (3, 2, "Tom Contact3&quot
Insert INTO Contact ;
(ContId, CompId, Contname) ;
VALUES (4, 2, "Michael Contact4&quot
Insert INTO Contact ;
(ContId, CompId, Contname) ;
VALUES (5, 3, "Sherman Contact5&quot
Insert INTO Contact ;
(ContId, CompId, Contname) ;
VALUES (6, 3, "John Contact6&quot
Index ON ContId TAG ContId CANDIDATE

* Create sample database
Close DATABASE ALL
Create DATABASE SAMPLE
Add TABLE Company
Add TABLE Contact

* Create sample view
Create VIEW lvSample AS ;
SELECT * ;
FROM SAMPLE!Contact ;
INNER JOIN SAMPLE!Company ;
ON Contact.CompId = Company.CompId ;
ORDER BY Contact.Contname
DBSetProp("lvSample","View","Tables", ;
"Contact,Company&quot
DBSetProp("lvSample.ContId","Field", ;
"KeyField",.T.)
DBSetProp("lvSample.ContId","Field", ;
"Updatable",.T.)
DBSetProp("lvSample.CompId_B","Field", ;
"KeyField",.T.)
DBSetProp("lvSample.CompId_B","Field", ;
"Updatable",.T.)
DBSetProp("lvSample","View", ;
"SendUpdates",.T.)

* Create company view
Create VIEW lvCompany AS ;
SELECT * ;
FROM SAMPLE!Company ;
ORDER BY Company.Company
DBSetProp("lvCompany","View","Tables", ;
"Company&quot
DBSetProp("lvCompany.CompId","Field", ;
"KeyField",.T.)
DBSetProp("lvCompany.CompId","Field", ;
"Updatable",.T.)
DBSetProp("lvCompany","View", ;
"SendUpdates",.T.)

* Suspend here and set the database update properties
* in the view designer. It don't work when I set them
* in code. I get error "No updatable tables were found..."
* Anybody know why?
Modify view lvCompany
Modify view lvSample

* Add a new company by changing the company ID
* and company name of contact3
Close DATABASE ALL
Open DATABASE SAMPLE
Use lvSample
Use lvCompany in 0
Select lvCompany
Insert into lvCompany ;
(CompId,Company) values (4,"Company4&quot
Tableupdate()
Select lvSample
Requery() && the source table has changed
Locate FOR ContId = 3
Replace ;
compid_A with 4
Tableupdate()
Requery() && get the new company info
Browse nowait

Return
* End SampApp

 

[mbru]

Don't know if you ever resolved this, but I did a quick read through and noted what I think would be a problem. In the code creating the lvSample view:

Create VIEW lvSample AS ;
SELECT * ;
FROM SAMPLE!Contact ;
INNER JOIN SAMPLE!Company ;
ON Contact.CompId = Company.CompId ;
ORDER BY Contact.Contname

You have selected all fields from both tables (*), Contact and Company. Each of those tables contains the field CompId, which is the join condition and also the key field for your table updates. When you do a join like this, the duplicate field names will appear as CompId_a and CompId_b. There will be no field named CompId in your resulting view. When you set a keyfield with DBSETPROP to CompId, that field is not found in the view.

I don't know if there are other problems with the code, but this kind of jumped out at me. Hope it helps.