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: Bernard Bout
  Where is Bernard Bout?
 Brisbane
 Australia
 Bernard Bout
 Tags
Subject: RE: VFP 9 and SQL Server 2000
Thread ID: 112331 Message ID: 112668 # Views: 2 # Ratings: 0
Version: Visual FoxPro 9 Category: Databases, Tables and SQL Server
Date: Friday, November 10, 2006 8:05:26 PM         
   


> > > Thanks Sir,
> > > for your cooprate. I am success to get data through CA but how can I use Buffering,Tableupdate(),Tableuprevert() and refresh data please help me
> > >
> > > thanks
> > >
> > >
> > > > > Hello Experts,
> > > > > I never use SQL Server with VFP before. Please help me how i connect to the data of SQL Server. And also something about cursoradaptor
> > > > >
> > > > > Thank in Advance

> > > >
> > > > Asif,
> > > >
> > > > When connecting to a SQL backend, you have three basic flavours: SPT, Remote Views and CA's
> > > >
> > > > SPT - SQL PassThrough
> > > > You begin by creating a connection to the SQL database using SQLCONNECT() or SQLSTRINGCONNECT(). Once the connection has been created, you pass SQL command strings to the server using SQLEXEC(). The first thing you need is a connection string (you can use a named connection but this is not recommended.) Go to www.ConnectionStrings.Com to find the connection string you need. The SQLSTRINGCONNECT() returns an integer handle that you can then use in your SQLEXEC() commands. You pass the SELECT, UPDATE and INSERT INTO commands to the server using the following format:
SQLEXEC(MyConnectionHandle,[SELECT ... FROM ... WHERE ... ORDER BY ...],[csrResultCursor])
In this case the result of the SELECT command (fill in your own particulars) will be sent to a local cursor called csrResultCursor.
> > > >
> > > > I recommend that you look at the help file on all commands beginning with SQL.
> > > >
> > > > Remote Views
> > > > A remote view also uses a connection that can be generated using SQLCONNECT() or SQLSTRINGCONNECT(). When you create a view in the view designer, go to the VFP menu Query->View SQL. You will see that the view is simply a SELECT command followed by a series of commands to set cursor properties.
> > > >
> > > > CA'S
> > > > A CA is simply a class based version of a view. It again uses a connection created with SQLCONNECT() or SQLSTRINGCONNECT() and it uses a SELECT command.
> > > >
> > > > My recommendation would be to play with all three options. To begin with, start with Bernard's very good series of articles on "A Basic CA Tutorial" found in the artlicles page of this site. I am just beginning an exploration of the CA and found Bernard's articles quite usefull. Next, I recommend that you go to Andy's website and look at his article on using SPT in "Class Based Data Management" here: www.tightlineComputers.Com What I normally use is based largely on that article.
> > > >
> > > > Hope this helps.
> > > >
> > > > Ken
> > > > You shall know the truth - and the truth shall set you free. (John 8:33)

> > >
> > >
> > >
> > > i accept all the condition of forum

> >
> > Asif,
> >
> > See my reply to your other post.
> >
> > I typically just put a "save" button on the form, and in the .Click() call a custom form method .SaveData(). In the .SaveData method, I simply call a series of TABLEUPDATES() (one for each table or CA cursor) and if they all succeed, issue an END TRANSACTION. If any of the TABLEUPDATEs fails I tell the user and issue a ROLLBACK.
> >
> > Ken
> > You shall know the truth - and the truth shall set you free. (John 8:33)

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


Thanks Bernard,

This is essentially what I do but I use a DO CASE structure. IF/ELSE/ENDIF works great when you have 2 tables, but when you have 3+ tables to update, you can end up with a lot of nested IFs. A CASE structure just seems like neater code to me.
BEGIN TRANSACTION
SQLSETPROP(oConn,"Transactions",2)
DO CASE
   CASE NOT SaveTable1()
      ROLLBACK
      SQLROLLBACK(oConn)
   CASE NOT SaveTable2()
      ROLLBACK
      SQLROLLBACK(oConn)
   CASE NOT SaveTable3()
      ROLLBACK
      SQLROLLBACK(oConn)
   ...
   OTHERWISE
      END TRANSACTION
      SQLSETPROP(oConn,"Transactions",1)
ENDCASE

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