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: Ken Murphy
  Where is Ken Murphy?
 Springhill
 Canada
 Ken Murphy
 Tags
Subject: RE: VFP 9 and SQL Server 2000
Thread ID: 112331 Message ID: 112506 # Views: 2 # Ratings: 0
Version: Visual FoxPro 9 Category: Databases, Tables and SQL Server
Date: Thursday, November 09, 2006 7:03:53 AM         
   


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

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