You are currently offline, waiting for your internet to reconnect

How to retrieve information from SQL Server about affected rows in Visual FoxPro 7.0 and later versions

This article was previously published under Q156034
This article has been archived. It is offered "as is" and will no longer be updated.
Microsoft SQL Server and other server data sources frequently have statistical information about how many rows were affected by a particular SQL statement when it was executed. Microsoft Visual FoxPro 7.0 and later versions do not retrieve this information from the server for you. This article describes how to integrate using stored procedures on the server to retrieve this information.
When you are working with data on a server, you must know how many rowsin the table were affected by a certain SQL command. This information is similar to theinformation that is provided by the _TALLY variable in Visual FoxPro.

This information is not available to you directly in Visual FoxPro, butthere are ways to obtain that information if you want.

Visual FoxPro 7.0 and later versions support output parameters from storedprocedures. This means that you can call a procedure that is located on the SQLServer-based computer and then that procedure returns information to the clientapplication.

The following code example uses an existing table in the SQL Server Pubs database and creates the stored procedure on the SQL Server-based server. Create a program in Visual FoxPro, copy the code into the program, and then run the program from the Visual FoxPro Command window:
ClearlcServername = "(local)"  && change the SQL Server name if you have to.lcDatabase = "pubs"TEXT to lcText noshow textmerge CREATE PROCEDURE upd_pubsauthors @state char(2), @zip char(5), @retcount int OUTPUT AS  UPDATE authors SET state = @state          WHERE  zip = @zip     SELECT @retcount = @@ROWCOUNTENDTEXT Mvar = 0nhandle=Sqlstringconnect("Driver=SQL Server;SERVER="+lcServername+";DATABASE="+lcDatabase) =SQLExec(nhandle,'drop procedure upd_pubsauthors') = -1 If SQLExec(nhandle,lcText) = -1 Aerror(atest) List Memory Like atest ReturnEndif If SQLExec(nhandle,"{CALL upd_pubsauthors ('CA', '94609', ?@mvar)}") = -1 Aerror(atest) List Memory Like atestElse ? MvarEndifReturn
After the code runs, view the value that is echoed to the Visual FoxPro desktop. This value indicates the number of rows in the SQL Server table that are affected.
For more information about SQL Server stored procedures, see Microsoft SQL Server manuals.

Article ID: 156034 - Last Review: 01/16/2015 18:25:22 - Revision: 3.4

Microsoft Visual FoxPro 7.0 Professional Edition, Microsoft Visual FoxPro 8.0 Professional Edition, Microsoft Visual FoxPro 9.0 Professional Edition

  • kbnosurvey kbarchive kbautomation kbhowto kbinterop KB156034