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/do...s/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
--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
ci**************@msn.com www.cindywinegarden.com
"Chris Morton" <Chris
Mo****@discussions.microsoft.com> wrote in message
news:DE**********************************@microsof t.com...
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.
