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: 112984 # Views: 1 # Ratings: 1
Version: Visual FoxPro 9 Category: Databases, Tables and SQL Server
Date: Tuesday, November 14, 2006 4:14:54 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,

You are using SPT to create the cursor with your:
SQLEXEC(gnConnect, "select * from album", "CurResult")

You also need to use SPT to save the data. Add this command under your REPLACE:
lnRetVal = SQLEXEC(gnConnect, [UPDATE Album SET alb_desc = "Members Photos"+ CHR(13) + "Test Version"])
I note that you do not check the result of your SQLEXEC() command. I recommend that you always check SQLEXEC()'s return value to see if it was successful.

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