VFP update from another table

[Guest]

Not sure if this is the right location for this question.
I Saw several previous FP questions in this Group and did not see one for
VFP....

If this is the wrong group, please direct me appropriately.

I have two tables and I would like to update a field in table1 based on data
in table2. Here is an example of what I thought I could do based on SQL .

update employee
set salary = e.salary + b.amount
from employee e, bonus b
where e.empid = b.empid and e.company = b.company.

I have tried several updates from a google search including joins, and sub
selects to no avail.

Any assistance would be greatly appreciated.

 

[Marc Miller]

Chris,

If you aren't using Visual Studio.NET, then I would suggest the next time
you visit the newsgroup 'microsoft.public.fox.programmer.exchange'.

In answer to your question, you can't do that in VFP, yet. The method in
FoxPro would be command code.
Create an index of str(employee.id ) + employee.company on the employee
table.
Set the order in employee to your index tag.
Select the bonus table.
Then create a relationship between the tables:

SET RELATION TO str(Bonus.id) + bonus.company INTO Employee ADDITIVE
SET SKIP TO Employee

REPLACE ALL employee.salary with employee.salary + bonus.amount


That should do it for you.

M. Miller

 

[Cindy Winegarden]

Hi Chris,

You've left out a lot of details. Are you working in Visual FoxPro (if so,
you'll be better off posting to a Fox newsgroup), or sending SQL
Pass-through from a .NET app? Are you using ODBC or OLE DB? What's the
version of the driver/data provider you're using? There have been recent
improvements in the FoxPro and Visual FoxPro OLE DB data provider, which is
downloadable from
http://msdn.microsoft.com/vfoxpro/downloads/updates/default.aspx.

 

[Cindy Winegarden]

Hi Chris,

Sorry I'm late to this thread so I hope you see this. Here's VB code that
works, and needless to say the same SQL works in VFP9, which has recently
been released.

Option Explicit On
Option Strict On

Imports System
Imports System.Data
Imports System.Data.OleDb

Module Module1
Sub Main()
Try

'-- Download and install the latest VFP OLE DB data provider
'-- from
http://msdn.microsoft.com/vfoxpro/downloads/updates/default.aspx
'-- This example assumes you already have a c:\Temp directory
'-- For free tables use "Data Source = C:\MyFolder"
'-- For a DBC use "Data Source = C:\MyFolder\MyDBC"
'-- When there are spaces in the path use "" around it.
'-- Note also that you can surround a quoted string with "", '',
or [] in VFP.
Dim conn As OleDbConnection
conn = New OleDbConnection("Provider=VFPOLEDB.1;Data
Source=C:\Temp;")
conn.Open()

'-- Lets create some data to work with
Dim cmd1 As New OleDbCommand("Create Table Employee (EmpID I,
Company I, Salary I)", conn)
Dim cmd2 As New OleDbCommand("Insert Into Employee Values (1, 1,
2000)", conn)
Dim cmd3 As New OleDbCommand("Create Table Bonus (EmpID I,
Company I, Amount I)", conn)
Dim cmd4 As New OleDbCommand("Insert Into Bonus Values (1, 1,
100)", conn)
cmd1.ExecuteNonQuery()
cmd2.ExecuteNonQuery()
cmd3.ExecuteNonQuery()
cmd4.ExecuteNonQuery()

'-- Now let's update our data
Dim cmd5 As New OleDbCommand( _
"Update Employee Set Salary = e.Salary + b.Amount " & _
"From Employee e, Bonus b " & _
"Where ((e.EmpID = b.EmpID) And (e.Company = b.Company))",
conn)

''-- Now let's update our data
'Dim cmd5 As New OleDbCommand( _
' "Update Employee Set Salary = e.Salary + 100 " & _
' "From Employee e " & _
' "Where ((e.EmpID = 1) And (e.Company = 1))", conn)

cmd5.ExecuteNonQuery()

'-- Now let's see what the data looks like
Dim da As New OleDbDataAdapter("Select * From Employee", conn)
Dim ds As New DataSet
da.Fill(ds)

MsgBox("Salary = " & ds.Tables(0).Rows(0).Item(2).ToString())

Catch ex As Exception
MsgBox(ex.ToString())
End Try

End Sub
End Module