Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Cursor adapter with 2 tables
Message
From
13/09/2008 04:20:43
 
 
To
12/09/2008 19:59:34
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:
01347320
Views:
21
>Can a cursoradapter work with 2 related tables at the same time? If yes, how would I modify the code below to add the second table? The second table will be TEST_PHASES, the common field will be TIP_ID. The table structure of TEST_PHASES is:
>keyID I
>tip_id N(10,2)
>phase C(10)
>_year I
>
>We want tip_id and _year to match those from TEST_FINANCING.
>
>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
>            FROM \\tpdfiles\data\dfreeman\tip\data\TEST_FINANCING.DBF
>            WHERE  Test_financing._year = ( ?m.lnYear )
>    		ORDER BY Test_financing.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_financing] 	&&
>this.oCa.fetchsize      = -1				&& primarily for remote views
>this.oCa.keyfieldlist   = [keyid]			&& 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" 						
>							
>this.oCa.updatablefieldlist = "keyid, tip_id, amount, source, _year "
>this.oCa.alias              = this.sourcetable
>
>m.lnYear = this._year
>this.oCa.cursorfill()
>
>Thanks

Change only SELECTCMD and JOIN second table.
i.e.
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.Phase
        FROM  TEST_FINANCING
        [LEFT | INNER] JOIN TEST_PHASES ON TEST_FINANCING.Tip_Id = TEST_PHASES.Tip_Id AND 
                                           TEST_FINANCING._year  = TEST_PHASES._year
       WHERE  Test_financing._year = ( ?m.lnYear )
      ORDER BY Test_financing.tip_id
ENDTEXT
Against Stupidity the Gods themselves Contend in Vain - Johann Christoph Friedrich von Schiller
The only thing normal about database guys is their tables.
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform