FoxPro 2.6 transaction managment... -- dotnet datatools

Cindy Winegarden
3/22/2005 1:10:05 PM

Hi Stéphane,

Please see
http://groups-beta.google.com/group/microsoft.public.fox.vfp.queries-sql/msg/5327ecd1863dca80 .

In Visual FoxPro Transactions are only supported for tables that are part of
a DatabaseContainer (a DBC file is present) but FoxPro "free" tables like
yours do not support this natively. However, by using the code as VFP MVP
Anders Altberg outlined, you can support Transactions via the OLE DB
provider:

[quoted text, click to view]
This code worked in VFP. The corresponding VB code should work as well I
hope
********
LPARAMETERS lCommit
LOCAL oConn AS ADODB.Connection, ors AS ADODB.RecordSet
oConn=CREATEOBJECT('ADODB.Connection')
ors= CREATEOBJECT('ADODB.RecordSet')
oConn.Open('Provider=VFPOLEDB.1;Data Source=D:\projects\data')
ors.Open('Receiver', oConn)
oConn.Execute("maketransactable('receiver')")
oConn.BeginTrans
oConn.Execute("insert into Receiver values (123, 'xxx')")
IF lCommit
oConn.CommitTrans
ELSE
oConn.RollBackTrans
ENDIF
ors.close
oConn.close
*********

Opening a RecordSet with the table as Source seemed to be necessary to make
it work for me.
<<

The latest FoxPro and Visual FoxPro OLE DB data provider is downloadable
from http://msdn.microsoft.com/vfoxpro/downloads/updates .

I haven't tried this yet in VB.NET.

--
Cindy Winegarden MCSD, Microsoft Visual Foxpro MVP
cindy_winegarden@msn.com www.cindywinegarden.com
Blog: http://spaces.msn.com/members/cindywinegarden

[quoted text, click to view]

StÃ©phane
3/23/2005 6:01:03 AM

Thanks Cindy,

I convert the sample code to Visual Basic 6 and it work.

But I was unable to make it work with ado.net :(

You put me back on the road with the "maketransactable()" function.

Thanks for your help.

[quoted text, click to view]

Cindy Winegarden
3/23/2005 7:35:53 PM

Hi Stéphane,

I had time to play with this today:

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

Module Module1

Sub Main()

Dim conn As New OleDbConnection( _
"Provider=VFPOLEDB.1;Data Source = C:\Temp;")
conn.Open()

'-- Let's create some data to work with
Dim cmd1 As New OleDbCommand("Create Table TestTrans (Field1 C(10))",
conn)
Dim cmd2 As New OleDbCommand("Insert Into TestTrans Values ('Hello')",
conn)
cmd1.ExecuteNonQuery()
cmd2.ExecuteNonQuery()

'-- Now let's see what the data looks like
Dim da As New OleDbDataAdapter("Select * From TestTrans", conn)
Dim ds1 As New DataSet
da.Fill(ds1)
MsgBox("Say hello: " & ds1.Tables(0).Rows(0).Item(0).ToString())
MsgBox("Count of rows: " & ds1.Tables(0).Rows().Count.ToString)

'-- Make it transactable
Dim cmd3 As New OleDbCommand("MakeTransactable('TestTrans')", conn)
cmd3.ExecuteNonQuery()

'-- Use a Transaction and Commit it
Dim trans1 As OleDbTransaction = conn.BeginTransaction()

Dim cmd4 As New OleDbCommand("Insert Into TestTrans Values ('World')",
conn, trans1)
cmd4.ExecuteNonQuery()
trans1.Commit()

Dim ds2 As New DataSet
da.Fill(ds2)
MsgBox("Say hello: " & ds2.Tables(0).Rows(0).Item(0).ToString())
MsgBox("Count of rows: " & ds2.Tables(0).Rows().Count.ToString)

Dim trans2 As OleDbTransaction = conn.BeginTransaction()
Dim cmd5 As New OleDbCommand("Insert Into TestTrans Values ('Not!!!')",
conn, trans2)
cmd5.ExecuteNonQuery()
trans2.Rollback()

Dim ds3 As New DataSet
da.Fill(ds3)
MsgBox("Count of rows: " & ds3.Tables(0).Rows().Count.ToString)

End Sub

End Module

--
Cindy Winegarden MCSD, Microsoft Visual Foxpro MVP
cindy_winegarden@msn.com www.cindywinegarden.com
Blog: http://spaces.msn.com/members/cindywinegarden

[quoted text, click to view]

StÃ©phane
3/24/2005 12:31:02 PM

Hi Cindy,

This is exactly the kind of test I did... I try your code...and when we are
trying to execute the command 3 (MakeTransactable('TestTrans')) It does not
work.

OleDbException is raise

ErrorCode : -2147217865
Message : File 'maketransactable.prg' does not exist.

Maybe I don't have the latest version of the provider...

Version : 8.00.0000.3006

Thanks

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about dotnet datatools

dotnet datatools : FoxPro 2.6 transaction managment...