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: Mike S
  Where is Mike S?
 Staffordshire
 United Kingdom
 Mike S
 Tags
Subject: RE: VFP 9 and SQL Server 2000
Thread ID: 112331 Message ID: 113047 # Views: 1 # Ratings: 1
Version: Visual FoxPro 9 Category: Databases, Tables and SQL Server
Date: Wednesday, November 15, 2006 4:31:51 AM         
   


> > Asif and Ken
> >
> > When using CA's I use the following code segment if ever I need to update > 1 table in SQLServer.
> >
> >
> > * first for VFP - start the transaction
> > BEGIN TRANSACTION
> > 
> > * now SQLServer - start the transaction
> > SQLSETPROP(oConn,"Transactions",2)
> > 
> > IF NOT ThisForm.SaveData()
> > 	* roll back here
> > 	* first SQLServer
> > 	SQLROLLBACK(oConnection)
> > 	* Next VFP local cursors
> > 	ROLLBACK
> > ELSE
> > 	* for SQLServer
> > 	IF SQLCOMMIT(oConn) < 0
> > 		* store and log the error
> > 		AERROR(aer)
> > 		ThisForm.logsqlerror()
> > 		MESSAGEBOX(aer[2])
> > 		* rollback SQLServer
> > 		SQLROLLBACK(oConn)
> > 		* rollback local vfp cursors
> > 		ROLLBACK
> > 	ELSE
> > 		* This is for VFP local cursors
> > 		END TRANSACTION 
> > 		Messagebox("Data has been saved. .",64,"Data Saved",0)
> > 		lSave = .T.
> > 	ENDIF
> > ENDIF
> > * set transactions off in SQLServer
> > SQLSETPROP(oConn,"Transactions",1)

> >
> >
> > and we are done here.

>
> Im just starting to look at Sql and Foxpro. I found this post interesting but cant seem to get my head around it. The code below should replace my alb_desc with "Members Photos"+ CHR(13) + "Test Version". The cursor updates but not on SQL, have i done something wrong?
>
>
> gnConnect = SQLCONNECT("SQLDEV", "user", "pass")
> IF gnConnect <= 0
> 	=MESSAGEBOX("Could not establish a database connection.",0, "Connection Error")
> 	RETURN
> ENDIF
> 
> WAIT WINDOW NOWAIT "Establishing Connection with Server"
> SQLEXEC(gnConnect, "select * from album", "CurResult")
> 
> SELECT CurResult
> GOTO 2
> replace alb_desc WITH "Members Photos"+ CHR(13) + "Test Version"
> 
> * first for VFP - start the transaction
> BEGIN TRANSACTION
> 
> * now SQLServer - start the transaction
> SQLSETPROP(gnConnect,"Transactions",2)
> 
> IF SQLCOMMIT(gnConnect) < 0
> 	* store and log the error
> 	AERROR(aer)
> 	MESSAGEBOX(aer[2])
> 	* rollback SQLServer
> 	SQLROLLBACK(gnConnect)
> 	* rollback local vfp cursors
> 	ROLLBACK
> ELSE
> 	* This is for VFP local cursors
> 	END TRANSACTION 
> 	Messagebox("Data has been saved. .",64,"Data Saved",0)
> 	lSave = .T.
> ENDIF
> * set transactions off in SQLServer
> SQLSETPROP(gnConnect,"Transactions",1)
> 



Mike

As Ken says you have not updated the SQL side with the data from your VFP Cursor. Since you are using SPT you have to cobnstruct the update statement manually and send it to the backendmanually as well.

A few points to note:

1. You do NOT need transactions unless you are updating > 1 table and need to ensure that all updates are done or none at all. In the sample you posted there is no need for a transaction since only 1 table is being updated.

2. As ken mentioned, don't assume that your SQL functions went OK. You MUST check the return value and act on it. I have a keyboard macro on my computer that inserts this code for every SQLEXEC I issue:

nRet = SQLEXEC(.......)

the macro code is:

IF nRet = 0
AERROR(aer)
ThisForm.LogSqlError(aer) && custom log routine
RETURN .F.
ENDIF

Have a look at the AERROR() function and what is stiored in the elements for SQLServer.

This way if a command fails it is logged and I return an .F. from the method.

After you are used to SPT take a look at CursorAdapters, new to VFP from V8. They will sinmplify your development a lot.

Look in the article section of this site for some articles on using CA's.

ENTIRE THREAD

VFP 9 and SQL Server 2000 Posted by Asif Hussain @ 11/7/2006 12:41:55 PM
RE: VFP 9 and SQL Server 2000 Posted by Ken Murphy @ 11/7/2006 3:05:53 PM
RE: VFP 9 and SQL Server 2000 Posted by Asif Hussain @ 11/8/2006 7:43:20 AM
RE: VFP 9 and SQL Server 2000 Posted by Ken Murphy @ 11/8/2006 1:03:55 PM
RE: VFP 9 and SQL Server 2000 Posted by Bernard Bout @ 11/9/2006 7:03:53 AM
RE: VFP 9 and SQL Server 2000 Posted by Asif Hussain @ 11/10/2006 1:36:57 PM
RE: VFP 9 and SQL Server 2000 Posted by Bernard Bout @ 11/10/2006 10:18:39 PM
RE: VFP 9 and SQL Server 2000 Posted by Asif Hussain @ 11/11/2006 5:22:32 AM
RE: VFP 9 and SQL Server 2000 Posted by Ken Murphy @ 11/11/2006 3:55:42 PM
RE: VFP 9 and SQL Server 2000 Posted by Ken Murphy @ 11/10/2006 8:05:26 PM
RE: VFP 9 and SQL Server 2000 Posted by Bernard Bout @ 11/10/2006 10:11:40 PM
RE: VFP 9 and SQL Server 2000 Posted by Ken Murphy @ 11/11/2006 1:50:48 AM
RE: VFP 9 and SQL Server 2000 Posted by Bernard Bout @ 11/12/2006 3:25:06 AM
RE: VFP 9 and SQL Server 2000 Posted by Mike S @ 11/14/2006 12:04:52 PM
RE: VFP 9 and SQL Server 2000 Posted by Ken Murphy @ 11/14/2006 4:14:54 PM
RE: VFP 9 and SQL Server 2000 Posted by Bernard Bout @ 11/15/2006 4:31:51 AM
RE: VFP 9 and SQL Server 2000 Posted by Mike S @ 11/15/2006 9:33:27 AM
RE: VFP 9 and SQL Server 2000 Posted by Ken Murphy @ 11/15/2006 1:16:07 PM
RE: VFP 9 and SQL Server 2000 Posted by David Jo @ 11/11/2006 4:54:56 PM