Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Cursor adapter with 2 tables
Message
From
15/09/2008 12:41:35
 
General information
Forum:
Visual FoxPro
Category:
Databases,Tables, Views, Indexing and SQL syntax
Environment versions
Visual FoxPro:
VFP 9 SP2
OS:
Vista
Network:
Windows 2008 Server
Database:
MS SQL Server
Miscellaneous
Thread ID:
01347281
Message ID:
01347687
Views:
16
OK, with Naomi's suggestion I realized I had the wrong field name in the select. So I fixed that and turned the relation around so that test_phases drives the result. This produces a 1 to many rather than a many to 1 result. I also made some changes to the oCA tables, the oCA.fieldlist, and the oCA.updatenamelist so that the field in test_phases would be updateable. Here is my code now and it seems to work at least to the point of reading and updateing both tables.
this.oCa = createobject([CursorAdapter])

text TO this.oCa.SelectCmd PRETEXT 15 NOSHOW
     SELECT test_financing.keyID, Test_financing.tip_id, Test_financing.amount,
            Test_financing.source, Test_financing._year, test_phases.phasename,
            test_phases.keyID as key2
            FROM \\tpdfiles\data\dfreeman\tip\data\TEST_phases.DBF
            left join test_financing on test_phases.tip_id = test_financing.tip_id and test_phases._year = test_financing._year
            WHERE  Test_phases._year = ( ?m.lnYear )
		    ORDER BY Test_phases.tip_id
endtext

this.oCa.allowinsert		= .t.
this.oCa.insertcmd			= ""
this.oCa.buffermodeoverride = 3				&& 3 = row buffering, 5 = table buffering
this.oCa.datasourcetype = [NATIVE]			&& native fox datasource
this.oCa.sendupdates    = .t.				&& unnecessary except with ODBC or ADO
this.oCa.wheretype      = 1					&&
this.oCa.tables         = [test_phases,test_financing] 	&&
this.oCa.fetchsize      = -1				&& primarily for remote views
this.oCa.keyfieldlist   = [keyid, key2]			&& the primary key for the table
this.oCa.updatenamelist = 	"keyid test_financing.keyid," + ;
							"tip_id test_financing.tip_id," + ;
							"amount test_financing.Amount," + ;
							"source test_financing.Source," + ;
							"_year  test_financing._year," + ;
							"key2 test_phases.keyID," + ;
							"phasename test_phases.phasename"						
this.oCa.updatablefieldlist = "keyid, tip_id, amount, source, _year, phasename, key2 "
this.oCa.alias              = this.sourcetable

m.lnYear = this._year
this.oCa.cursorfill()
My next step will be to write code to provide INSERT and DELETE capabilities. What I am not sure of is this:
1. If the only fields that need to be updatable (except for INSERT and DELETE) are amount, source, and phasename, do the other fields need to be included in the updatenamelist?

2. Do I need to include the keys from both tables in the keyfieldlist?

3. Do I need to include both tables in the tables list?

4. What about the SELECT. Do I need to include more than the 2 keyfields and the fields that will be displayed?

Thanks
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform