How To Determine Number of Records Affected by an ADO UPDATE

Article translations Article translations
Article ID: 195048 - View products that this article applies to.
This article was previously published under Q195048
Expand all | Collapse all

On This Page

SUMMARY

The Execute method of the ActiveX Data Objects (ADO) Command object passes by reference an integer value that you can use to retrieve the number of records affected by a SQL UPDATE command.

MORE INFORMATION

The following example establishes a DSN-less connection to the SQL Server sample table ROYSCHED in the PUBS database, executes a SQL UPDATE command, displays the number of records affected by the command, then executes another UPDATE command to restore the ROYSCHED table to its previous state.

NOTE: Modify the SERVER component of the lcConnString variable, the lcUID and lcPWD variables as appropriate for your SQL Server installation.

In order to use this example, you must have Microsoft Data Access Components (MDAC) version 2.x or later installed, which is included in the data components of Visual Studio 6.0 or can be downloaded from the following Web address:
http://msdn.microsoft.com/en-us/data/aa937729.aspx

Sample Code

   #DEFINE adModeReadWrite 3
   #DEFINE adCmdText 1

   oConnection = CREATEOBJECT("ADODB.Connection")
   oCommand = CREATEOBJECT("ADODB.Command")

   lcConnString = "DRIVER={SQL Server};" + ;
      "SERVER=YourServerName;" + ;
      "DATABASE=pubs"

   lcUID = "YourUserID"
   lcPWD = "YourPassword"

   oConnection.ATTRIBUTES = adModeReadWrite
   oConnection.OPEN(lcConnString, lcUID, lcPWD )

   * Use the command object to perform an UPDATE
   * and return the count of affected records.
   strSQL = "UPDATE roysched SET royalty = royalty * 1.5"
   liRecordsAffected = 0
   WITH oCommand
      .CommandType = adCmdText
      .ActiveConnection = oConnection
      .CommandText = strSQL
      .Execute(@liRecordsAffected)
   ENDWITH
   =MESSAGEBOX("Records affected: " + LTRIM(STR(liRecordsAffected)))

   * Set the royalty column back to its previous value.
   strSQL = "UPDATE roysched SET royalty = royalty / 1.5"
   liRecordsAffected = 0
   WITH oCommand
      .CommandType = adCmdText
      .ActiveConnection = oConnection
      .CommandText = strSQL
      .Execute(@liRecordsAffected)
   ENDWITH

   =MESSAGEBOX("Records affected: " + LTRIM(STR(liRecordsAffected)))
				

Properties

Article ID: 195048 - Last Review: July 1, 2004 - Revision: 4.3
APPLIES TO
  • Microsoft Visual FoxPro 6.0 Professional Edition
  • Microsoft Data Access Components 2.1 Service Pack 2
  • Microsoft Data Access Components 2.5
  • Microsoft Data Access Components 2.6
Keywords: 
kbdatabase kbhowto kbsqlprog KB195048

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com