Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Ken Murphy
  Where is Ken Murphy?
 Springhill
 Canada
 Ken Murphy
 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: 113099 # Views: 1 # Ratings: 0
Version: Visual FoxPro 9 Category: Databases, Tables and SQL Server
Date: Wednesday, November 15, 2006 1:16:07 PM         
   


> > > > 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.

>
> I see now, i thought that if you updated the cursor for some reason it would update sql table just my mix up. I will look into CursorAdapters but if it is as you said new to VFP 8 i wont be able to use it until we upgrade to VFP 9 (still on VFP 7).

Mike,

If you are still on VFP 7, you should probably look at the remote view. A remote view, like the CA handles updating the backend table for you. Don't give up on SPT though. Learn how to use SPT fisrt and when you get into CA's and remote views, you will understand exactly what is happening "behind the scenes." I recommend that you read Andy's excellent article "Class Based Data Management" at http://www.tightlinecomputers.com/Downloads.htm

When you do upgrade, Bernards excellent series of articles on "A Simple CA Tutorial" is a very good place to start.

Ken
You shall know the truth - and the truth shall set you free. (John 8:33)

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