Updating SQL Table Via SqlExec

[SGLong]

In VFP the use of an UPDATE command to a fox table returns the number of rows that were updated to _TALLY. I'm building an UPDATE command to SQL and using SQLEXEC to run it. Is there a way to get back from SQL the number of rows that were updated also?

Steve

 

[Zografski]

YES
Check in VFP HELP SQLEXEC( ) Function
aCountInfo
Specifies the name of the array to populate with row count information. If the array doesn’t exist, it is created. The array has two columns: 1 – Alias, 2 –Count.

Count
Number of affected or fetched records.
Integer
Indicates the number of affected records as returned by the ODBC SQLRowCount function. Returns -1 if the number of records is unavailable

Example

SQLEXEC(sqlhandler ,lcSelect , [crsTest],aCountInfo)
MESSAGEBOX([Number of affected or fetched records ]+TRANSFORM(aCountInfo[2]))

 

[Mike Lewis]

Steve,

There's no generic way of doing this. It all depends on the back end. For example, in SQL Server, @@ROWCOUNT returns the required value, but that doesn't necessarily work with other servers.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[bborissov]

I Prefer that construction:

TEXT TO lcSQL NOSHOW TEXTMERGE
     UPDATE MyTable ........
            WHERE .....
     SELECT @@ROWCOUNT
ENDTEXT

SQLEXEC(..., lcSQL, [crsTest])
SELECT crsTest
BROWSE NORMAL

If you use different BackEnd just change SELECT @@ROWCOUNT to its appropriate syntax.



Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[Mike Lewis]

Steve,

Actually, Zografski's solution does have the advantage of being independent of the back end. The reason I suggested an alternative is that the fourth param to SQLEXEC() was new in VFP 9.0.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[SGLong]

All,

I just got back to this project, so I'll be testing things this week. Mike, your suggestion is probably the way that I will go because management hasn't given us to green light to migrate from VFP 6 yet (groan).

Steve