Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Borislav Borissov
  Where is Borislav Borissov?
 Sofia
 Bulgaria
 Borislav Borissov
 To: Widi Wijanarko
  Where is Widi Wijanarko?
 
 Indonesia
 Widi Wijanarko
 Tags
Subject: RE: Joint Data free table with Sql function
Thread ID: 78741 Message ID: 79249 # Views: 3 # Ratings: 1
Version: Visual FoxPro 9 Category: Databases, Tables and SQL Server
Date: Friday, October 14, 2005 12:03:04 PM         
   


> > > Dear All
> > > How can I Update data from free table vfp to data MSSql with SQL Command
> > > Thank

> >
> >
> >
> > Can you say what exactly you want to do?
> > Join VFP free table to MSSql table and Update field(s) in SQL Server or something else?

>
>
> Yes, I want join vfp free table to MSSQL table, or update field(s) without sql remote view.




You can use SPT for this, something like:
I assumed sqlH is connection handler to SQL Server
SELECT YourFreeTRable
SCAN
   SQLEXEC(sqlH,"UPDATE SqlTable SET SqlTableField1 = ?YourFreeTable.Field1 WHERE SqlTable.FieldX = ?YourFreeTable.Fieldx")
ENDSCAN


otder way is to use OPENROWSET() function in T-SQL, but this way is more complicated and you must be SURE that you will get right resuilts.

** This is SQL Script and must be EXECUYED on SQL Server side
UPDATE YourSqlTable SET SqlTableField1 = MyVFPTable.Field1 
      INNER JOIN  OPENROWSET('VFPOLEDB.1', 
   'c:\MyFreeTable.DBF';'';'', '') AS MyVFPTable
   ON YourSqlTable.CustomerID = MyVFPTable.FieldForJoin

This is not tested at all, you must set the correct OLE DB string here and TEST this example. Check BOL for more help about OPENROWSET() function



The best way (my beloved :o)) Use CursorAdapter, not only for updating but for retreive data. Becuase I don't know your current taks this way maybe is not applicable. So Choose

ENTIRE THREAD

Joint Data free table with Sql function Posted by Widi Wijanarko @ 10/7/2005 1:03:13 PM
RE: Joint Data free table with Sql function Posted by Borislav Borissov @ 10/7/2005 3:11:15 PM
RE: Joint Data free table with Sql function Posted by Widi Wijanarko @ 10/14/2005 11:45:45 AM
RE: Joint Data free table with Sql function Posted by Borislav Borissov @ 10/14/2005 12:03:04 PM
RE: Joint Data free table with Sql function Posted by Widi Wijanarko @ 10/17/2005 9:44:16 AM
RE: Joint Data free table with Sql function Posted by William Sanders @ 10/19/2005 9:46:00 AM