Introducing the CursorAdapter Class
Wiki
Cursor Adapter Or Not

Original

If you have another unique field in the record being inserted and you already know the value of that field (it is assigned in the cursor before the Tableupdate), you can use that field as the lookup key to find the record just inserted. For example, if you added another field like Extrakey c(10) and inserted SYS(2015) into it, you could have VFP9 CA automatically refresh the cursor record by setting two properties:
oCA = CreateObject("MyCAObj")
oCA.InsertCmdRefreshKeyFieldList = "Extrakey"
oCA.InsertCmdRefreshFieldList = "MyID"
oCA.CursorFill()
Insert Into crs_Table (Extrakey, Name, LastName) ;
  Values (SYS(2015), "Rodolfo","Duarte")
TableUpdate()
Because the value of Extrakey is known (it's in the cursor after the insert statement), CA can automatically use that value to retreive the record just inserted to refresh the cursor with the new autoinc value.

A different approach you might want to experiment with is to make use of the InsertCmdRefreshCmd property to specify a query that matches the Identity field to the last key added (this might work only if you are inserting just one record).

This idea comes from the "New in Nine" Hentzenwerke book, which shows an example of using the @@IDENTITY feature in SQL Server to get the newly-added value):
oCA.InsertCmdRefreshCmd = ;
  "Select MyID from tablename where MyID = @@IDENTITY"
oCA.InsertCmdRefreshFieldList = "MyID"
So, with VFP9's new GETAUTOINCVALUE() function, you MIGHT be able to mimic the same thing in VFP9 against Fox tables (I have not tested this and have no idea if it will work, so I leave it to you as just an idea to explore):
*** THIS CODE NOT TESTED -- JUST AN IDEA TO EXPLORE
oCA.InsertCmdRefreshCmd = ;
  "Select MyID from tablename where MyID = GetAutoIncValue()"
oCA.InsertCmdRefreshFieldList = "MyID"

ADO Jumpstart for Microsoft Visual FoxPro Programmers

Source

Clear All
csql = "SELECT EmployeeID, LastName, FirstName," + ;
   "Title, TitleOfCourtesy, BirthDate," + ;
   "Hiredate, Address, City, Region," + ;
   "PostalCode, Country, HomePhone," + ;
   "Extension, Photo, Notes," + ;
   "ReportsTo, PhotoPath FROM dbo.Employees"

lnHandle=Sqlstringconnect('DRIVER=SQL Server;'+;
   'SERVER=server;DATABASE=Northwind;Trusted_Connection=Yes')
SQLExec(lnHandle,csql,'v_emp')

CursorSetProp('KeyFieldList','Employeeid','v_emp')
CursorSetProp('WhereType',1,'v_emp')
CursorSetProp('Tables','employees','v_emp')

TEXT to m.lcUpdateNameList noshow
employeeID  employees.employeeid,
Lastname employees.Lastname,
Firstname employees.FirstName,
Title employees.Title,
TitleOfCourtesy employees.TitleOfCourtesy,
BirthDate employees.BirthDate,
Hiredate employees.Hiredate,
Address employees.Address,
City employees.City,
Region employees.Region,
PostalCode employees.PostalCode,
Extension employees.Extension,
Notes employees.Notes,
ReportsTo employees.ReportsTo,
PhotoPath employees.PhotoPath
ENDTEXT
CursorSetProp("UpdateNameList", ;
   Chrtran(m.lcUpdateNameList,Chr(13)+Chr(10),''),'V_emp')

CursorSetProp('UpdatableFieldList',;
   "LastName, FirstName," + ;
   "Title, TitleOfCourtesy, BirthDate," + ;
   "Hiredate, Address, City, Region," + ;
   "PostalCode, Country, HomePhone," + ;
   "Extension, Notes," + ;
   "ReportsTo, PhotoPath",'v_emp')
CursorSetProp('SendUpdates',.T.,'v_emp')
CursorSetProp('Buffering',5,'v_emp')
Browse Title 'Editable fields are all except Id and Photo'
If !Tableupdate(2,.T.,'v_emp')
   Aerror(arrWhy)
   Display Memo Like arrWhy
Endif
SQLExec(lnHandle,'select * from dbo.employees','afterupdate')
SQLDisconnect(lnHandle)
Select afterupdate
Browse

Source

All of these things can be accomplished with SQLEXEC() and other SQL...() functions from Visual FoxPro.

To make a connection, you can use SQLSTRINGCONNECT():

cConnString = "Driver={SQL Server};server=YourServerName;uid=sa;pwd=YourPassWord;database=YourDataBase"
nSQLHandle = SQLSTRINGCONNECT(cConnString)
Parameterized procedures and calling SQL Server functions and/or stored procedures:

cSQL = [EXEC InsertRecord ?thisform.MainID,?cNewPhone,?@thisform.TNID]
nStat = SQLEXEC(nSQLHandle,cSQL)
Note the use of "?@" for the return value from the procedure "InsertRecord" or whatever your names are.

If you need to just retrieve records from SQL Server into a Visual FoxPro cursor, it's:

cSQL = [SELECT * FROM YourTable WHERE SomeField='some value']
nStat = SQLEXEC(nSQLHandle,cSQL,"CursorAlias")
You can do UPDATE statements via SQLEXEC also.

cSQL = [UPDATE YourTable SET SomeField='some value' WHERE KeyField=?YourKey]
nStat = SQLEXEC(nSQLHandle,cSQL)
If you check the return values of nStat from a SQLEXEC call and it's not a value of 1, then you'll need to use AERROR() to see what happened on the SQL side.

And to disconnect:

SQLDISCONNECT(nSQLHandle)

Click
T-SQL doesn't support variables in place of table names. You can use dynamic SQL instead.
DECLARE @sql varchar(4000)
SET @sql = 'update @chvTableName SET ' +
'ClientID = ClientID + ' + CAST(@insClientOffset AS varchar(16)) +
', QuoteID        = QuoteID  + ' + CAST(@insQuoteOffset AS varchar(16)) +
', MasterClientID = MasterClientID + ' + CAST(@insMasterClientOffset AS varchar(16))

EXECUTE (@sql)

Temporary table on SQL Server

IF object_id('tempdb..##Temptable') IS NOT NULL
   DROP TABLE ##Temptable
DECLARE @sql varchar(4000)
SET @sql = 'SELECT * INTO ##Temptable FROM pubs.dbo.Authors'
EXECUTE(@sql)
SELECT * FROM ##Temptable
IF object_id('tempdb..##Temptable') IS NOT NULL
   DROP TABLE ##Temptable



Frequently Asked Questions
Title

How to return a value from dynamicaly executed T-SQL code
  
Summary

You can return value from a dynamicaly executed T-SQL into a local variable using undocumented in SQL Server Books Online (it's documented in mskb #262499) feature of system extended stored procedure sp_executesql. This method is shorter and simplier than the common practice of using a temporary table.
  
Description

If you try to return value from dynamicaly executed T-SQL you'll quickly find out that the calling procedure local variables are not within scope of dynamic SQL because it is treated as another batch. The common practice to overcome that limitation involves writing the value to a temporary table and then pulling it out in the calling procedure. Listing #1 illustrates this


-- Listing #1
DECLARE @ret_ext_max nvarchar(100), @SQLstr nvarchar(100)
CREATE TABLE #returnvalue (retval nvarchar(100))
SET @SQLstr = N'SELECT MAX(au_id) FROM pubs..authors'
INSERT INTO #returnvalue EXECUTE (@SQLstr)
SET @ret_ext_max = ( SELECT retval FROM #returnvalue)
PRINT @ret_ext_max
The sp_executesql system stored procedure allows output parameters to be specified so that any output generated from the T-SQL statements can be stored (although this is not documented in SQL Server Books Online). Based on that code above could be rewriten as shown in the Listing #2.


-- Listing #2
DECLARE @ret_ext_max nvarchar(100), @SQLstr nvarchar(100)
SET @SQLstr = N'SELECT @ret_ext_max = MAX(au_id) FROM pubs..authors'
EXEC sp_executesql @SQLSTR, N'@ret_ext_max nvarchar(100) OUTPUT',
            @ret_ext_max OUTPUT
PRINT @ret_ext_max
As you can see this code is much shorter and works without temporay table.
The Listing #3 shows how to return more than one value and at the same time pass parameter to the dynamic sql. Notice, that ouput parameters listed first because you have to use only named parameters after the first named parameter has been used.


-- Listing #3
DECLARE @ret_ext_max nvarchar(100), @ret_ext_min nvarchar(100)
DECLARE @SQLstr nvarchar(100)

SET @SQLstr = N'SELECT @ret_ext_min = MIN(au_id), @ret_ext_max = MAX(au_id)' +
      ' FROM pubs..authors Where State = @State'
EXEC sp_executesql @SQLSTR,
  N'@ret_ext_min nvarchar(100) OUTPUT, @ret_ext_max nvarchar(100) OUTPUT, @State char(2)',
  @ret_ext_min OUTPUT, @ret_ext_max OUTPUT, @State = 'CA'
PRINT @ret_ext_min
PRINT @ret_ext_max
If for same reason you want to use only named parameter than see Listing #4

-- Listing #4
DECLARE @ret_ext_max nvarchar(100), @ret_ext_min nvarchar(100)
DECLARE @SQLstr nvarchar(100)

SET @SQLstr = N'SELECT @ret_int_min = MIN(au_id), @ret_int_max = MAX(au_id)' +
      ' FROM pubs..authors Where State = @State'
EXEC sp_executesql @SQLSTR,
  N'@State char(2), @ret_int_min nvarchar(100) OUTPUT, @ret_int_max nvarchar(100) OUTPUT',
  @ret_int_min=@ret_ext_min OUTPUT, @ret_int_max=@ret_ext_max OUTPUT, @State = 'CA'
PRINT @ret_ext_min
PRINT @ret_ext_max

*Sample form
#Define SQLCONNECTION   "Provider=SQLOLEDB;Integrated Security=SSPI;"+;
  "Persist Security Info=False;"+;
  "Initial Catalog=Northwind; Data Source=servername"

oForm = Createobject('myForm',SQLCONNECTION)
oForm.Show
Read Events

Define Class myForm As Form
  DataSession = 2
  Height = 300
  Width = 800
  Add Object Grd1 As myGrid With Height = 300, Width = 400
  Add Object Grd2 As Grid With Height = 300, Width = 400, Left = 400

  Procedure Init
  Lparameters tcConn
  This.AddProperty('oConn', Newobject("ADODB.Connection"))
  With This.oConn
    .ConnectionString = tcConn
    .Mode = 16
    .Open()
  Endwith
  This.AddProperty('Dataenvironment',Createobject('myDataEnvironment', This.oConn))
  This.Grd1.RecordSource = 'ca_customers'
  This.Grd2.RecordSource = 'ca_orders'
  Endproc

  Procedure QueryUnload
  Clear Events
  Endproc
  Procedure Destroy
  Endproc

  Procedure DispError
  Local Array errors(1)
  Aerror(errors)
  ? 'Error--------------'
  For ix=1 To 7
    ? errors[ix]
  Endfor
  ? 'Error--------------'
  Endproc

Enddefine

Define Class myDataEnvironment As DataEnvironment
  Procedure Init
  Lparameters toConn
  With This
    .AddObject('caCustomers','myADOAdapter',toConn,'ca_customers')
    With .caCustomers
      .Tables = "Customers"
      .KeyFieldList = "CustomerID"
      .UpdatableFieldList = "CustomerID, CompanyName, ContactName, ContactTitle"
      .UpdateNameList = ;
        "CustomerID customers.CustomerID, "+;
        "CompanyName customers.CompanyName,"+;
        "ContactName customers.ContactName,"+;
        "ContactTitle customers.ContactTitle"
      .SelectCmd = "select * from customers"
      .CursorFill()
    Endwith
    .AddObject('caOrders','myADOAdapter',toConn,'ca_orders')
    With .caOrders
      .Tables = "Orders"
      .KeyFieldList = "ORDERID"
      .UpdateNameList = "CUSTOMERID Orders.CUSTOMERID, "+;
        "EMPLOYEEID Orders.EMPLOYEEID,"+;
        "FREIGHT Orders.FREIGHT,"+;
        "ORDERDATE Orders.ORDERDATE,"+;
        "ORDERID Orders.ORDERID"
      .UpdatableFieldList = "CUSTOMERID, EMPLOYEEID, FREIGHT, ORDERDATE, ORDERID"
      .CursorSchema = "CUSTOMERID C(5), EMPLOYEEID I, FREIGHT Y, ORDERDATE D, ORDERID I"

      .AddProperty('oCommand', Newobject('ADODB.Command') )
      With .oCommand
        .CommandText = "select Orders.CustomerID, Orders.EmployeeID, "+;
          "Orders.Freight, Orders.OrderDate, Orders.OrderID"+;
          " from Orders"+;
          " where customerID = ?"+;
          " order by employeeID, orderdate desc"
        .Parameters.Append( .CreateParameter("CustomerID", 129,1,10,ca_customers.customerID) )
      Endwith
      .oCommand.ActiveConnection = .Datasource.ActiveConnection
      .AddProperty( 'oRS', .oCommand.Execute() )
      .CursorFill(.T., .F., 0, .oRS)
    Endwith
  Endwith
  Endproc
Enddefine

Define Class myGrid As Grid
  Procedure AfterRowColChange
  Lparameters nColIndex
  With Thisform.DataEnvironment.caOrders
    .oRS.ActiveCommand.Parameters("CustomerID") = ca_customers.customerID
    .CursorRefresh()
  Endwith
  Thisform.Refresh()
  Endproc
Enddefine

Define Class myADOAdapter As CursorAdapter
  AllowUpdate = .T.
  AllowInsert = .T.
  AllowDelete = .T.
  WhereType = 1
  UpdateType= 1
  SendUpdates = .T.
  DataSourceType = "ADO"

  Procedure Init
  Lparameters toConn, tcAlias
  With This
    .Alias = tcAlias
    .Datasource = Newobject("ADODB.Recordset")
    With .Datasource
      .ActiveConnection = toConn
      .LockType=4
      .CursorLocation = 3
      .CursorType = 2
    Endwith
  Endwith
  Endproc

  Procedure UpdateTable
  This.Datasource.Updatebatch()
  Endproc
Enddefine
SOurce

Click

nAutoRefreshConn=SQLSTRINGCONNECT("DRIVER=SQL Server;SERVER=FABIO-NX7000\DEV;Trusted_Connection=Yes;database=tempdb")
IF nAutoRefreshConn<1
   RETURN
ENDIF

Local oCA As CursorAdapter
oCA=CREATEOBJECT("myCA")

TEXT TO CREATE NOSHOW
USE tempdb
IF OBJECT_ID('T1') IS NOT NULL DROP TABLE T1
IF OBJECT_ID('T2') IS NOT NULL DROP TABLE T2
CREATE TABLE T1 (
   Z_id  int IDENTITY(1,1)PRIMARY KEY,
   Z_name varchar(20) NOT NULL)
CREATE TABLE T2 (
   Y_id  int IDENTITY(100,5)PRIMARY KEY,
   Y_name varchar(20) NULL)

ENDTEXT

TEXT TO TRIGGER NOSHOW
CREATE TRIGGER T1_trig
ON T1
FOR INSERT AS
   BEGIN
   INSERT T2 VALUES ('')
   END
ENDTEXT

IF SQLEXEC(nAutoRefreshConn,m.create)=1 AND SQLEXEC(nAutoRefreshConn,m.TRIGGER)=1 AND oCA.CursorFill()

   SET NULL OFF
   * oCA.InsertCmdRefreshCmd="SELECT @@IDENTITY" && CursorAdapter returns a wrong number
   * oCA.InsertCmdRefreshCmd="SELECT z_id From t1 Where z_id = @@IDENTITY" && CursorAdapter returns an error: The refresh key defined for table is not found
   * oCA.InsertCmdRefreshCmd="SELECT z_id From t1 Where z_id = SCOPE_IDENTITY()" && CursorAdapter returns an error: The refresh key defined for table is not found

   INSERT INTO CATest (z_name) VALUES ('Rodolfo')
   INSERT INTO CATest (z_name) VALUES ('Denise')
   INSERT INTO CATest (z_name) VALUES ('Carol')

   IF !TABLEUPDATE(.T.)
       ? "TABLEUPDATE is failed!!!"  
      AERROR(aerrs)  
      DISPLAY MEMORY LIKE aerrs  
   ELSE
      LIST
   ENDIF
   oCA.CursorDetach
ENDIF
TEXT TO DESTROY NOSHOW
IF OBJECT_ID('T1') IS NOT NULL DROP TABLE T1
IF OBJECT_ID('T2') IS NOT NULL DROP TABLE T2
ENDTEXT
SQLEXEC(nAutoRefreshConn,m.DESTROY)
SQLDisconnect(nAutoRefreshConn)
BROWSE

DEFINE CLASS myCA AS CursorAdapter
   DataSourceType="ODBC"  
   DataSource= nAutoRefreshConn  
   Alias = "CATest"  
   BufferModeOverride= 5
   SelectCmd="select * from t1"  
   CursorSchema="z_id I, z_name C(10)"    
   UseCursorSchema= .T.
   Tables="t1"
   UpdatableFieldList="z_name"
   UpdateNameList="z_id t1.z_id, z_name t1.z_name"
   InsertCmdRefreshFieldList="z_id"
   InsertCmdRefreshCmd="SELECT SCOPE_IDENTITY()"

PROCEDURE beforeInsert
LPARAMETERS cFldState, lForce, cInsertCmd
   cInsertCmd = STRTRAN(m.cInsertCmd,"?catest.z_name","'"+catest.z_name+"'",1)
ENDDEFINE