Hi Jim,

In VFP9, SPT has been enhanced to make amount of affected records available:

  CLOSE DATABASES all  
  SQLDISCONNECT(0)  
  CLEAR  
  SET MULTILOCKS ON  
    
 * Connect to any available Sql Server   
  nConn=SQLCONNECT("TestServer")  
  IF (nConn <1)  
   ? "Failed to connect!!!"  
   AERROR(aerrs)  
   DISPLAY MEMORY LIKE aerrs  
   return  
  ENDIF  
    
  IF SQLEXEC(nConn ,"CREATE TABLE #test (f1 int, f2 varchar(10) NULL )")!=1  
   ? "Failed to create demo table!!!"  
   AERROR(aerrs)  
   DISPLAY MEMORY LIKE aerrs  
  ENDIF  
    
  FOR I=1 TO 100  
  	SQLEXEC(nConn ,"insert into #test (f1) values (?I)")  
  NEXT   
    
  SQLEXEC(nConn,"UPDATE #test SET f2=convert(varchar(10),f1) where f1 BETWEEN 32 and 79",NULL,aRowCount)  
  ? "Updated records:", aRowCount(1,2)  
  DISPLAY MEMORY LIKE aRowCount  
    
  SQLDISCONNECT(nConn)  

Thanks,
Aleksey.


>Hi Fabio,
>
>It's good to see that there is a way with SQL Server.
>
>I am using VFP ODBC driver though and I *assume* that it does not support @@rowcount. _Tally also does not seem to apply.
>
>I've gotta redesign the application for this part. At least I have the list (Listbox) of updated records so I can use that to pre-verify.
>
>Thanks
>
>>Hi Jim,
>>
>>SQLEXEC() supports batchs of commands, so you can send a sequence of T-SQL statements in a single request and SQL Server will process all of them. That said, you could send your update statement and right after query the @@rowcount SQL Server global variable, which will contain the number of rows affected by the last SQL statement issued. Remember that this will happen in one single round-trip.
>>
>>This dirty code shows an example against the Northwind database:
>>
>>

>>LOCAL lcStrConn  as String
>>LOCAL lnHandle   as Integer
>>LOCAL lcSQLBatch as String
>>
>>lcStrConn = "Driver={SQL Server}; Server=(local); Database=Northwind; UID=sa; PWD=;"
>>lnHandle  = SQLSTRINGCONNECT(lcStrConn)
>>
>>IF lnHandle < 1
>>  MESSAGEBOX("Could not connect to the database")
>>  RETURN .F.
>>ENDIF
>>
>>*-- The following statement will cause no updates
>>TEXT TO lcSQLBatch TEXTMERGE NOSHOW
>>  UPDATE Customers SET CompanyName = 'Will not update' WHERE CustomerID = 'NONEXISTENT';
>>  SELECT @@rowcount as RowsUpdated
>>ENDTEXT
>>
>>SQLEXEC(lnHandle, lcSQLBatch, "curResult")
>>
>>MESSAGEBOX("Rows Updated: " + TRANSFORM(curResult.RowsUpdated))
>>
>>SQLDISCONNECT(lnHandle)
>>