Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Bernard Bout
  Where is Bernard Bout?
 Brisbane
 Australia
 Bernard Bout
 To: Ronan Masangcay
  Where is Ronan Masangcay?
 Manila
 Philippines
 Ronan Masangcay
 Tags
Subject: RE: SQLEXEC("UPDATE") destination table = SQL Server (dbo) from VFP cursor -- MULTIPLE RECORDS IN ON
Thread ID: 102737 Message ID: 113175 # Views: 3 # Ratings: 0
Version: Visual FoxPro 6 Category: Databases, Tables and SQL Server
Date: Thursday, November 16, 2006 4:46:22 AM         
   


> > > Dear Bernard,
> > >
> > > Do u mean that while working with other damn backend not native i only require to connect to the backend either thru OLEDB or ODBC, then make the cursor updatable and use tableupdate and it will update the table in the backend.
> > >
> > > Since i have been using the sqlexec(). Pls. help.
> > >
> > > Regards,
> > > Hozefa Bandukwala,
> > > Udyog Software (India) Ltd.,
> > > Project Leader,
> > > Mummbai, India.
> > >

> >
> >
> > Yes that is almost exactly right. CA's can be used in fact to connect to any backend, even to VFP tables itself. Why you may ask since VFP can directly access a VFP table. The theory is that while testing, you can use CA's and test off VFP tables. Once everything is working sweetly, you "UPSIZE" your VFP tables to Oracle or SQLServer or... and point the CA's to the new data source. You can even switch partway between different data sources.
> >
> > The steps in brief are:
> >
> > You set up the CA and make it updatable. You set buffering to 5 (for buffering = 3 see below)
> > You add it to your DE and depending on certain properties it will get data or will get just the structure with no data. Now in VFP you can use APPEND BLANK or INSERT INTO to add records or you can use a grid etc. to allow edits for any number of fields.
> >
> > When you are ready to save, you issue a TABLEUPDATE() (see help for options, syntax) and the CA will then take over, and for each record that has data changed it will autogenerate the appropriate INSERT, UPDATE OR DELETE statement and send it back to SQLServer. Other than checking that all went well there is nothing you have to do. That is the beauty of CA's. Views work similarly but are not as flexible and "hookable" as CA's.
> >
> > With SPT you have to do all the work yourself, but people (Like Andy Kramek) have written robust frameworks that work in a similar fashion using SPT that have built in hooks and are equally easy to use. CA's is just the latest "built in" approach for updating data.
> >
> > For more info please read the Help file. Also I have written some articles on CA's published on this site. Check out the Articles section.
> >
> > What I use is CA's for updatable tables, and a mixture of SPT and CA's for lookups.
> > Basically if I need a lookup more than once, then I just create a CA and for all other casual selects I use SPT.
> >
> >
> > By the way your name sounds Parsi. Kem cho :)

>
> ******************************************************************
>
> Dear Bernard,
>
> CA is really good. I create CAs and pratice off VFP tables, upsize, then update CA on the forms' DE, then I'm done. Thank you for the direction :)
>
> however, i need help in understanding why CA is not able to convert or execute the REPLACE ALL command. This goes the same using UPDATE SET WHERE command (not SQLPT since i am using CA).
>
> The difference between REPLACE ALL and UPDATE is that REPL ALL does not update anything and UPDATE SET WHERE updates only the last record.
>
> Thank you again Bernard!
>
> Regards,
> Ronan
>
> Something that's worth doing is worth doing right....



Hi Ronan

Without knowing the buffering status of your tables, it is difficult to say.

Anyway from experience, I never use REPLACE ALL in a multi user environment. I did once long ago and used to get huge locking issues. I advise against using this.

What backend are you using?
If using a VFP backend why are you using CA's when you can access the data directly?
The actual difference between REPLACE ALL and UPDATE SET WHERE is that REPLACE ALL only works in VFP with VFP tables and will replace all the records for the selected table - unless the CURRENTLY SELECTED TABLE is at EOF(). Then no replace will take place. Documented in Help. Easy to fix.

Just select the table before doing a replace.

UPDATE SET WHERE on the other hand is a filtered update and will only affect those records that meet the where clause, not all the records. Maybe only 1 record meets the criterion.

I need more info.

ENTIRE THREAD

SQLEXEC("UPDATE") destination table = SQL Server (dbo) from VFP cursor -- MULTIPLE RECORDS IN ONE GO Posted by Ronan Masangcay @ 7/28/2006 4:58:21 AM
RE: SQLEXEC("UPDATE") destination table = SQL Server (dbo) from VFP cursor -- MULTIPLE RECORDS IN ON Posted by Bernard Bout @ 7/28/2006 5:44:26 AM